Who won’t love an SQL query which is finely tuned, well written and which gives higher performance too? I have compiled some of the nice steps we need follow to tune the SQL query and increase the performance of the execution.
- Use of the appropriate Table Aliases:
Assigning a common and standard approach for the table aliases can be very helpful in tuning the SQL query. For example, if you assign different table alias to the same table in the similar SQL statements, then SQL statements becomes different and will not be shared.
- Write Identical SQL statement:
Always keep the habit of writing the SQL query in the similar case, white space and underlying schema objects so that it will be shared within the Oracle’s memory.
| sql> SELECT FIRST_NAME FROM EMP WHERE ID = 100; statement to match sql> SELECT FIRST_NAME FROM emp WHERE ID = 100; lower case sql> SELECT FIRST_NAME FROM EMP WHERE ID=100; white space |
- Tuning the views:
Tuning the Views is also the another best approach for tuning the SQL statement ultimately increasing the query performance. Views can be tuned like a regular SQL Select statements.
- Standard SQL statement:
SQL statements should be parallel with each other and should be correctly written. For example, don’t use two spaces between the two words in the statement. Write all SQL verbs on a new line. Always align the SQL verbs right or left (not both). Similarly, use similar case for all SQL statements. Most people prefer writing in the UPPER case.
- Not using HAVING Clause in select statement if possible:
In the SELECT statements, HAVING clause should be avoided to improve the query performance. HAVING clause filters selected rows only, after all, the other rows has been yielded. Use of WHERE clause helps to reduce the overheads in sorting, summing and so on. HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.
| Using HAVING Clause | Not Using HAVING Clause |
| SELECT region, AVG (loc_size) FROM location GROUP BY region HAVING region != ‘ WASHINGTON DC ‘ AND region != ‘ TEXAS ‘; |
SELECT region, AVG (loc_size) FROM location WHERE region != ‘ WASHINGTON DC ‘ AND region != ‘ TEXAS ‘; GROUP BY region; |
- Always prefix column names by Aliases if the statement involves multiple tables:
If the SQL statement involves more than one table, then prefix all the column name with their respective aliases. This helps in decreasing the parse time and also prevents possible syntax error which might occur in the future when someone adds a column to one of the tables used in the statement with the same column name as a column in another table.
- Don’t use any operation in the DB object referenced in WHERE Clause :
If possible, do not perform any operation on the database objects referenced in the WHERE clause. Here are some of the examples of bad practices in which operations are performed after the WHERE clause:
| SELECT account_name, trans_date, amount WHERE SUBSTR (account_name,1,7) = ‘CHASE’; |
| SELECT account_name, trans_date, amount WHERE account_name || account_type= ‘CHASE’; |
| SELECT customer_id, trans_date, amount WHERE TRUNC (trans_date) = TRUNC ( SYSDATE ); |
| SELECT account_name, customer_id, amount WHERE account_name = NVL ( :acct_name, account_name); |
| SELECT account_name, customer_id, amount WHERE amount + 3000 < 5000; |
| SELECT account_name, customer_id, amount WHERE amount < 2000; |
| SELECT account_name, customer_id, amount WHERE amount NOT = 0; |
- Use of fewer Sub-queries in the SQL statement:
The number of table lookups should be used as little as possible especially when the SQL statement contains the multiple subqueries (SELECT statement or Multicolumn UPDATEs).
| Separate Subqueries | Combined Subqueries |
| SELECT emp_name FROM sal_info WHERE sal = ( SELECT MAX (sal) FROM sal_info) AND sal_range = ( SELECT MAX (sal_range) FROM sal_info) AND emp_dept = 10; |
SELECT emp_name FROM sal_info WHERE (sal, sal_range) = ( SELECT MAX (sal), MAX (sal_range) FROM sal_info) AND emp_dept = 10; |
- Use of UNION ALL instead of UNION if possible:
If you are confident enough that your table doesn’t contain any duplicates or if duplicates result doesn’t affect your business requirement then consider using UNION ALL instead of UNION in the SQL statement. Union clause needs all rows returned by each portion of the UNION to be sorted and merged and duplicates to be filtered before returning the result. However, UNION ALL simply results in all the rows including the duplicates it doesn’t perform any sort, merge or filter.
| UNION | UNION ALL |
| SELECT acct_num, balance_amt FROM account_payable WHERE tran_date = ’31-DEC-15′ UNION SELECT acct_num, balance_amt FROM account_receivable WHERE tran_date = ’31-DEC-15′; |
SELECT acct_num, balance_amt FROM account_payable WHERE tran_date = ’31-DEC-15′ UNION ALL SELECT acct_num, balance_amt FROM account_receivable WHERE tran_date = ’31-DEC-15′; |
- Don’t use the keyword DISTINCT if the desired value can be fetched with other methods. DISTINCT adds an extra sort operation and, therefore, slows down your query performance.
- The SQL query should not result in any Cartesian product unless and until there is a business requirement to do so.
- While writing sub-queries make use of the EXISTS operator whenever possible as Oracle knows that once a match to the query has been found it can stop and avoid a full table scan
- If the desired object is in the subquery, then use IN.
- If the desired object is in the parent query, then use EXISTS.
- If the response time is important to you, then avoid doing an ORDER BY on a large data set
- Use CASE statements in the place of DECODE (especially where nested DECODEs are involved) because they increase the performance of the query significantly.
- To get the count, use COUNT(1) instead of COUNT(*) for the higher performance.
–Do not use:
SELECT COUNT(*)
FROM EMP;
–Use:
SELECT COUNT(1)
FROM EMP;
- Never compare NULL with NULL. You should know that NULL is not like an empty string or as the number 0.
SELECT COUNT(1)
FROM all_tables
WHERE 1 = 1;
Result: With condition 1 = 1 you will get result. But in this case, you can also skip condition 1 = 1.
SELECT COUNT(1)
FROM all_tables
WHERE NULL = NULL;
Result: With condition NULL = NULL you will get result 0.
- OR vs. IN.
Using IN in WHERE condition is faster than using OR.
–Do not use:
SELECT *
FROM EMP
WHERE FIRST_NAME = ‘JOHN’
OR FIRST_NAME = ‘ROBERT’
OR FIRST_NAME = ‘MIKE’
–Use:
SELECT *
FROM EMP
WHERE FIRST_NAME IN (‘JOHN’, ‘ROBERT’, ‘MIKE’)
- Use EQUALS TO (=) sign instead of “LIKE” operator
