SQL Tunning or Query Optimization Tips

Optimized SQL writing Technique

1. WHERE EXISTS vs IN
If the sub-query return more records than the main query then use WHERE EXISTS. If the sub-query return less records than the main query then use IN. EXISTS return a Boolean value where as IN returns real value. 

2. NOT IN, NOT=
Avoid NOT IN or NOT=  on indexed columns. They prevent the optimizer from using indexes.
For example - you can write WHERE amount>0 instead of WHERE amount!=0.

3. NOT NULL
Avoid writing NULL on indexed columns.  It can prevent the optimizer from using Indexes.

For example - avoid writing like WHERE amount is NOT NULL.

4. OR and Full table scan
Try to use IN or UNION in place of OR on indexed columns. ORs on indexed columns causes the optimizer to perform a full table scan.

5. Calculation on Indexed column
Try to avoid calculation on indexed column. 
Example - write WHERE amount >1800/3 instead of WHERE amount/3 > 1800 

6. using Hints
Sometimes using hints can bring benefit.

For example -
SELECT /*+ index (t1 t1_abc) index (t2 t2_abc)*/
COUNT (*)
FROM t1,t2
WHERE t1.col1 = t2.col2;

Sometimes when you need to show first rows in a list -

SELECT /*+ FIRST_ROW(10)*/ column_nm1

FROM table_nm1
WHERE column_nm1 LIKE 'abc%';

If you want to actually understand what you are doing with you SQL, start playing with following things -

Execution plan
Trace - It will tell you like how much time it spends on each query.
Baseline - plan
Profile - guideline 


Indexing Tips:

1. Consider adding small frequently accessed columns (not frequently updated) to an existing index. This will enable some queries to work only with index, not the table.

2. Regularly perform Index Monitoring. Check if the indexes are using by the application queries or not.

3. After adding a new index, analyze the index and compute statistics.