Oracle SQL Performance Tuning

1. SQL Performance Tuning team recommends using COUNT(1) instead COUNT(*) for SQL query performance optimization.

Example:

2. Never compare NULL with NULL. Consider that NULL is not like an empty string or like the number 0. Also NULL can not be not equal to NULL (NULL != NULL).

Example:

3. If you are using more than one table, make sure to use table aliases.

Example:

4. It is good practice to use table column names in an SQL query. This way the SQL statements will be more readable, but that is not the main reason. Example: If in INSERT statement you use SELECT * FROM x and at some point you add a new column in table x, SQL will return an error. The third reason why it is better to use table column names is to reduce network traffic.

5. In WHERE statements make sure to compare string with string and number with number, for optimal SQL query performance.

Example:

Note: Column id is NUMBER Data Type.

6. Do not change column values in WHERE statements if possible, recommended by SQL Performance Tuning.

Example:

7. Avoid using complex expressions.

Examples:

8. If you need to use SQL functions on join predicates that is okay, but do not use them with indexed table columns.

9. EXISTS vs. IN for sub queries
If the selective predicate is in the sub query, then use IN. If the selective predicate is in the parent query, then use EXISTS.

10. RETURNING clause
INSERT, UPDATE or DELETE can be used with RETURNING clause when appropriate. This way the number of calls to the database are reduced.

11. SQL Performance Tuning recommends using CASE statements. It is more efficient to run a single SQL statement, rather than two separate SQL statements.

Example:

12. Use UNION ALL instead of UNION, if possible

Example:

13. SQL Performance Tuning recommends to use minimal number of sub queries, if possible.

Example:

14. SQL Performance Tuning recommends when cretin data are used frequently, it is a good idea to store them into intermediate tables.

15. SQL Performance Tuning OR vs. IN.
Our tests showed that using IN in WHERE condition is a little faster then using OR.

Example: