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