SQL Performance

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

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: 

SELECT COUNT(1)
FROM all_users
WHERE 1 = 1;

SELECT COUNT(1)
FROM all_users
WHERE NULL = NULL;

SELECT COUNT(1)
FROM all_users
WHERE NULL != NULL;

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

Example: 

SELECT COUNT(1)
FROM master m, detail d
WHERE m.id = d.master_id;

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.

Do not use:
SELECT id, apn, charging_class
FROM master
WHERE id = '4343';

Use:
SELECT id, apn, charging_class
FROM master
WHERE id = 4343;

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

Example:

Do not use:
WHERE SUBSTR(a.serial_id, INSTR(b.serial_id, ',') - 1) = SUBSTR(b.serial_id, INSTR(b.serial_id, ',') - 1)

Use:
WHERE a.serial_id = b.serial_id

7. Avoid of using complex expressions.

Examples:

Avoid:
WHERE serial_id = NVL(:a1, serial_id)

WHERE NVL(serial_id,-1) = ( 1, etc...)

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

9. EXSISTS 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: 

Do not use:
SELECT COUNT (1)
  FROM emp
 WHERE salary < 1000;

SELECT COUNT (1)
  FROM emp
 WHERE salary BETWEEN 1000 AND 2000;

Use:
SELECT COUNT (CASE WHEN salary < 1000
                   THEN 1 ELSE null END) count_1,
       COUNT (CASE WHEN salary BETWEEN 1001 AND 2000
                   THEN 1 ELSE null END) count_2
  FROM emp;

12. Use UNION ALL instead UNION if possible

Example: 

Do not use:
SELECT id, name
  FROM emp_bmw
 UNION
SELECT id, name
  FROM emp_bmw_welt

Use:
SELECT id, name
  FROM emp_bmw
 UNION ALL
SELECT id, name
  FROM emp_bmw_welt

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

Example: 

Do not use:
SELECT id, manufacturer, model
  FROM cars
 WHERE price = ( SELECT MAX(price)
                   FROM cars_bmw
               )
   AND year =  ( SELECT MAX(year)
                   FROM cars_bmw
               )

Use:
SELECT id, manufacturer, model
  FROM cars
 WHERE (price, year) = ( SELECT MAX(price), MAX(year)
                           FROM cars_bmw
                       )

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