Oracle SQL Tuning Points

 

Here

NOT IN= 

lets consider dept10 will see each and every records

in table whether match found or not found.

It doesn't matter.

If match found on second record but still all records

will be accessed in NOT IN case.

 

NOT EXISTS:

If dept number 10 found a single match oracle will not

loop the next record.

It means execution will exit on that record.

From 2nd fig oracle will stop execution and execution for

other record time will be saved.

 

NOT EXISTS much faster than NOT IN.

 

More indexes on table i.e. burden on table

Arthematic operation has been done with database column.

Oracle Optimizer ignore the index here.

Lets I have table one column varchar2

where id=123,

so here number value ..

developer trying to 123 into varchar2 and make join so don't use.

If you use SQL function in WHERE clause oracle optimizer will not use index.

Equi Join is much faster than other join so always use Equi join (outer join)

Union all ( delicacy ) for better performance and faster

Union - avoid/remove delicacy

Co-related sub query  are slow as compare to inline views. Inline views are much faster

When Oracle optimizer has to make decision Oracle optimizer first find out the driving table

 

It is recommended that your driving table should be in last in WHERE clause.

Here

EMP_ACCOUNT -- Driven table

EMP table -- Driven table

DEPT table -- Driving table