Few Oracle SQL Performance Improvement Tips

Some of the performance improvement criteria for Oracle SQLS are below :

1. IN and NOT IN Operators -
These operators are slower than the operators EXISTS and NOT EXISTS.
Use EXISTS and NOT EXISTS with the target select statement.
This also gives chance for getting the index (if any) in the targetted sql utilised.
e.g. -
SELECT
BK.fm_book_id IN (select fm_book_id from fm_book_group_link where fm_group_id=128)
FROM BOOK BK

can be replace by

SELECT
BK.fm_book_id EXISTS (select fm_book_id from fm_book_group_link where fm_group_id=128)
FROM BOOK BK

2. LIKE operator -
Use leading character for pattern matching like 'A%' instead of '%A'.
Also, use TEXT field INDEX created and use CONTAINS in place of LIKE.
The entire story is at - http://www.orafaq.com/node/1918

3. Take care of system functions -
Try to avoid using system function as they can add up to the performance of the processing.

4. Ordering of the conditions in WHERE clause -
Ordering of the conditional statements in WHERE clause affects the table scan effort and impacts performance.
e.g. -
Use the statements having number (constant) comparison first, as this will filter the number of records and reduce the table scan.

Hope this will benefit somewhere :) Good Luck !

Avaneesh S. Tiwari
12-07-2013