Oracle SQL Performance Tuning

1. SQL Performance Tuning recommends to use COUNT(1) instead COUNT(*) for performance optimization.

Example:

2. Never compare NULL with NULL. Consider that the NULL is not like empty string or like number 0 and a NULL can not be not equal to a NULL

Example:

3. If you are using more than one table use table aliases.

Example:

4. Use name of columns in a query. The SQL statement will be more readable but that is not the main reason. If you will use in INSERT statement SELECT * FROM x and you will later add new column in x table SQL will return error. The third reason is that you can reduce network traffic.

5. In WHERE statement compare string with string or number with number.

Example:

Note: Column id is NUMBER Data Type.

6. If is possible use untransformed column values, recommends SQL Performance Tuning.

Example:

7. Avoid of using complex expressions.

Examples:

8. If you need to use SQL functions on join predicates, do not use them on indexed columns.

9. EXISTS versus IN for subqueries Note: If the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

10. RETURNING clause Use INSERT, UPDATE or DELETE RETURNING when appropriate. On that way is reduced the number of calls to the Database.

11. SQL Performance Tuning recommends to use CASE statements It is more efficient to run single statement if possible than two separate statements for the same result.

Example:

12. Use UNION ALL instead UNION if possible

Example:

13. SQL Performance Tuning recommends to use minimal number of subqueries if possible.

Example:

14. SQL Performance Tuning recommends to store intermediate results Always consider about the benefits of intermediate tables. If the information in that tables is reused some times then you should use them.

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

Example: