Search code examples
sqloracle-databasesql-viewsqlperformance

Extract data from view ORACLE performance

Hello I created a view to make a subquery ( select from two tables) this the sql order :

CREATE OR REPLACE VIEW EMPLOYEER_VIEW
AS
 SELECT A.ID,A.FIRST_NAME||' '||A.LAST_NAME AS NAME,B.COMPANY_NAME
 FROM EMPLOY A, COMPANY B 
 WHERE A.COMPANY_ID=B.COMPANY_ID
 AND  A.DEPARTEMENT !='DEP_004'
 ORDER BY A.ID;

If I select data from EMPLOYEER_VIEW the average execution time is 135,953 s

Table EMPLOY contiens 124600329 rows Table COMPANY contiens 609 rows.

My question is : How can i make the execution faster ?

I created two indexes:

emply_index (ID,COMPANY_ID,DEPARTEMENT) and company_index(COMPANY_ID)

Can you help me to make selections run faster ? ( creating another index or change join )

PS: I Can't create a materialized view in this database.

In advance thanks for help.

Solution

  • You have a lot of things to do. If you must work with a view, and can not create a scheduled job to insert data in a table, I will remove my answer. VIEWs does not have the scope to support hundred of million data. Is for few million.

    • INDEXes Must be cleaned when data are inserting. If you insert data with an index the process is 100 times slower. (You can drop and create or update them).

    • In table company CREATE PARTITION. If you have a lot of IDs, use RANGE. If you have around 100 IDs LIST PARTITION.

    You do not need Index, because the clause to JOIN does not optimize, INDEXes is specified to strict WHERE Clause.

    We had a project with 433.000.000 data, and the only way to works was playing with partitions.