Oracle 9i SQL Query Tuning Tips

The following are not de-facto rules.But quite helpful when tuning SQL query

Rule - 1
Avoid data type mismatch for index columns.

Rule - 2
Avoid functions on indexed columns

Rule - 3
Move conditions from having clause to where clause wherever possible.

Rule - 4
Use joins instead of nested selects. (Very Very Imp)

Rule - 5
When joining multiple tables the smallest table should be specified last.

Rule - 6
Replace NOT IN with NOT EXISTS.

Rule - 7
Replace != by UNION of “<“and “>” .

Rule - 9
Use UNION ALL instead of UNION wherever possible.

Rule - 10
Do not use columns on both sides of operator
When an indexed column appears on both sides of an operator, the index for that column is disabled.

Rule-11

Use Count(1) instead of count(*)

The COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

Useful Links

============
http://people.aapt.net.au/roxsco/tuning/
http://download.oracle.com/docs/html/A86647_01/vmqtune.htm
http://www.dba-oracle.com/art_sql_tune.htm

Oracle 9i Modifying settings in favour of Index scans

In a normal setting CBO in Oracle 9i gives equal preference to index scans and full table scans.But it is possible for the administrator to tweak the settings so as to make the optimiser favour index scan to full table scan.

optimizer_index_cost_adj is the parameter used for the purpose.By default its value is 100.Here it gives equal weightage to index scan as well as full table scan.By bringing that value down the CBO prefers for a index scan to full table scan.The value can range between 1-10000.