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.

Feel free to leave your comments.

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working