Query Performance By Khurram

Tip #	Query Optimization Tip
1.	If you know that there will not be any duplication of rows created as a result of using the UNION statement, then use the UNION ALL statement instead.
2.	Carefully evaluate whether your query needs the DISTINCT clause or not. The DISTINCT clause slows down virtually every query it is in.
3.	In your queries, don't return column data you don't need. In addition, using SELECT * prevents the use of covered indexes, further potentially hurting query performance.
4.	Try to avoid WHERE clauses using  "IS NULL", "OR", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE",  and "LIKE '%500'" can often (but not always) prevent the query optimizer from using an index to perform a search.
5.	Use EXISTS or NOT EXISTS as a substitute for NOT IN. Ex. Avoid using this: select col1 from tab1 where col1 not in (select col2 from tab2) Use this: select col1 from tab1 where not exists (select col2 from tab2 where tab2.col1 = tab1.col2)
6.	Use EXISTS instead of IN. It is more efficient and performs faster. But when EXISTS is used, make sure that there is a matching condition to filter out unnecessary records, otherwise there will be higher overhead and hence performance is slower. Ex. Avoid using this: select col1 from tab1 where col1 in (select col2 from tab2) Use this: select col1 from tab1 where exists (select col2 from tab2 where tab2.col1 = tab1.col2)
7.	Using IN or OR in WHERE clause may cause server to use TABLE SCAN instead of INDEX SEEK even though those columns are covered by an index. In this case, you may need to add INDEX hint for that table. Ex. Avoid using this: select col1, col2 from tab1 where col1 = 1 and col2 in (10,20) Use this: select /*+ INDEX (t1 index_name) */ col1 from tab1 t1 where col1 = 1 and col2 in (10,20)
8.	Avoid using leading character (%) in a LIKE clause as a wildcard (ex. LIKE ‘%a’) - the Query Optimizer will not be able to use an index, and a table scan must be run, reducing performance.
9.	Use untransformed column values. For example, use: WHERE a.order_no = b.order_no rather than: WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, ’.’) - 1)) = TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, ’.’) - 1))
10.	Do not use SQL functions in predicate clauses or WHERE clauses. Any expression using a column, such as a function having the column as its argument, causes the optimizer to ignore the possibility of using an index on that column, even a unique index, unless there is a function-based index defined that can be used.   Ex. (A) Avoid using this: select col1, col2 from tab1 where to_char(datecol3, ‘dd/mm/yyyy’) = to_char(sysdate, ‘dd/mm/yyyy’) Use this: select col1, col2 from tab1 where datecol3 >= to_date( ‘17/07/2002’, ‘dd/mm/yyyy hh24:mi:ss’) and datecol3 < to_date( ‘17/07/2002’, ‘dd/mm/yyyy hh24:mi:ss’) + 1 Avoid the following kinds of complex expressions: n col1 = NVL (:b1,col1) n NVL (col1,-999) = …. n TO_DATE(), TO_NUMBER(), and so on When you need to use SQL functions on filters or join predicates, do not use them on the columns on which you want to have an index; rather, use them on the opposite side of the predicate, as in the following statement: TO_CHAR(numcol) = varcol rather than varcol = TO_CHAR(numcol)