SQL Query Optimization Rules

 Query Optimization Rules

Rules for SQL query optimization:- Below are the some basic rules for SQL query optimization. This is not a complete set but can help to improve to create a optimize Query.
  
1) Use COUNT(1) instead of COUNT(*) for SQL query performance optimization.
2) Never compare NULL with NULL. NULL can not be not equal to NULL (NULL != NULL).
   Use "IS" or "IS NOT" operator for access of NULL value records
3) If you are using more than one table, make sure to use table aliases.
4) Select only those columns which are required. Extra columns which are not actually used, increase more I/O on the database and increase network traffic.
5) Do not use function in left side with column name in WHERE statements if possible.
6) Avoid using complex expressions.
7) Use EXISTS in place of IN as EXISTS work on boolean test while IN on record test
8) Use UNION ALL instead of UNION, if possible
9) Use Oracle Analytic Functions where you can use.
10) Use minimum number of Sub-Query if possible or use "With" clause for same.
11) Use MINUS set operator in place of NOT EXISTS/NOT IN
12) Do not use DISTINCT if the objective can be achieved. DISTINCT incurs an extra sort operation therefore queries processing will slow down.
13) Wherever multiple tables are used, always refer to a column by either using an alias or using the fully qualified table name. Do not leave to check for Oracle.
14) While querying on a partitioned table then try to use the partition key in the “WHERE” clause if possible. This will ensure partition pruning.
15) Use PARALLEL hint while accessing large data sets
16) Avoid  ORDER BY on a large data set especially if the response time is important.
17) Do not use HINTS unless required for the performance gains . 
18) Use CASE instead of DECODE because CASE increase the readability of the query immensely.
19) Check the Table and Index stats are up to date otherwise gather the stats through DBMS_STATS package or by Analyze command.
20) SQL Tuning Advisor and SQL Access Advisor can be used for system advice on tuning of specific SQL  
21) Hash Joins are preferable when 2 large tables need to be joined. Nested loops work better when a large table is joined with a small table. so read explain plan and use hints accordingly .
22) Use AWR and ADDM reports for better understanding of change in execution plan and throughput of top queries over a period of time.