Writing a better SQL

Many of us often faced performance issues though we have very good design and index organization. Any research would turn out mostly to report that only minor things have caused this. Here are the SQL tips to have a better Performance.

• Use Table alias and always qualify column names by table alias. This will increase the efficiency of SQL parsing by Oracle.

• UNION will eliminate duplicates and needs sorting to be done internally. Hence it will be wise to use UNION ALL instead of UNION. Unless you need to eliminate duplicates, do not use UNION. This will give performance merits some time.

• Use count(*) only when you needed total records.

• Select only required columns instead of "SELECT * FROM". It matters in performance especially when you SELECT tables with huge set of columns (denormalized MIS tables in datawarehousing is a best example).

• Revisit the use of DISTINCT, if the results are already using GROUP BY or UNION clauses, since GROUP BY and UNON were eliminating duplicates.

• Review your WHERE clause and make sure indexes are properly utilized. For example, if you have a index for column DEPT_ID on EMP table, below list of queries will not utilize indexes.

    SELECT emp_id 

      FROM emp 

     WHERE TRIM(dept_id) = 'ACCOUNTS'; 

    SELECT emp_id 

      FROM emp 

     WHERE UPPER(dept_id) = 'ACCOUNTS'; 

• Make sure expressions are not affecting the use of indexes. The expression " ADD_MONTHS(Summary_Date, 1) = '30-Jun-2008' ", will eliminate the use of any index in SUMMARY_DATE column. Instead you can use "Summary_Date = ADD_MONTHS('30-Jun-2008', -1)".

• Similarly use type conversions carefully to ensure index utilization.

    Refer below expression which would cause ORACLE not to use your index:

       WHERE TO_CHAR(price) = v_price 

    You can use below form of expression instead, 

       WHERE price = TO_NUMBER(v_price) 

• Always have a EXPLAIN PLAN of your queries and ensure there no unwanted full table SCANs. It would be better more than two versions of your query and SELECT the better one which is better in COST and execution time.

• Most importantly make sure your INDEXEs and TABLEs are analyzed after table changes. This really matters when you have huge record set change and your table/index statistics are too old for the optimizer to use.

Also read my hub pages on below topics.

• Oracle Interview Tips and Questions
• Good resume Format
• Writing a better SQL
• Oracle 10g New Features in SQL
• Over come the 255 Character Limit of DBMS_OUTPUT
• Oracle Exceptions
• Mastering Oracle Date Arithmetic

Feel free to leave your comments.