Monday, February 10, 2014

SQL Performance Optimization Checklist (Part -1)

A few points that I came across and implemented for a better SQL query execution and results;

1. Always in a JOIN condition the precedence comes from right. So RIGHT JOIN the table which has less number of rows or is of less size.

2.WHERE clause will always be executed from bottom to top. So tune the filter accordingly.

WHERE c1=1
AND c2 = 2
AND c3= 3 //First Executed

3. Implement any function in the WHERE clause as the last condition.

4. Use indexed column in the WHERE clause. Indexing implies faster search results.
Note: Indexing will create indexed trees for faster search / retrieval.

5. Do not use unwanted indexes in the table.
Note: We can always remove unwanted index in a table.

Database Used: Oracle