For Oracle SQL Query Tuning you are welcome to use our free SQL Query Tuning Tool.

Rules for SQL query optimization:

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

Example:

--Do not use:
SELECT COUNT(*)
FROM master;

--Use:
SELECT COUNT(1)
FROM master;

--Do not use:

SELECT COUNT(*)

FROM master;

--Use:

SELECT COUNT(1)

FROM master;

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:

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

--Result: With condition 1 = 1 you will get result. But in this case you can also omit condition 1 = 1.

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

--Result: With condition NULL = NULL you will get result 0.

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

--Result: With condition NULL != NULL you will also get result 0.

SELECT COUNT(1)

FROM all_users

WHERE 1 = 1;

--Result: With condition 1 = 1 you will get result. But in this case you can also omit condition 1 = 1.

SELECT COUNT(1)

FROM all_users

WHERE NULL = NULL;

--Result: With condition NULL = NULL you will get result 0.

SELECT COUNT(1)

FROM all_users

WHERE NULL != NULL;

--Result: With condition NULL != NULL you will also get result 0.

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

Example:

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

SELECT COUNT(1)

FROM master m, detail d

WHERE m.id = d.master_id;

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.

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

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

--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. Do not change column values in WHERE statements if possible, recommended by 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

--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 using complex expressions.

Examples:

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

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

--Avoid:

WHERE serial_id = NVL(:a1, serial_id)

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

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. NOT IN vs. NOT EXISTS vs. LEFT JOIN and NULL

Example:

--Try:
SELECT COUNT (1)
  FROM emp
 WHERE id NOT IN ( SELECT emp_id
                     FROM salary
                 );
--Try:
SELECT COUNT (1)
  FROM emp e
 WHERE NOT EXISTS ( SELECT emp_id
                     FROM salary s
                    WHERE s.emp_id = e.id
                  );
--Use:
SELECT COUNT (e.id)
  FROM emp e, salary s 
 WHERE e.id = s.emp_id(+)
   AND s.emp_id IS NULL

--Try:

SELECT COUNT (1)

FROM emp

WHERE id NOT IN ( SELECT emp_id

FROM salary

);

--Try:

SELECT COUNT (1)

FROM emp e

WHERE NOT EXISTS ( SELECT emp_id

FROM salary s

WHERE s.emp_id = e.id

);

--Use:

SELECT COUNT (e.id)

FROM emp e, salary s

WHERE e.id = s.emp_id(+)

AND s.emp_id IS NULL

11. 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.

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

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;

--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;

13. Use UNION ALL instead of 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

--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

14. SQL Performance Tuning recommends to use minimal number of sub queries, 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
                       )

--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

)

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

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

Example:

--Do not use:
SELECT *
  FROM CDRS_NR
 WHERE RECORD_TYPE = 'MTC'
    OR  RECORD_TYPE = 'MOC'
    OR  RECORD_TYPE = 'SMSO'

--Use:
SELECT *
 FROM CDRS_NR
WHERE RECORD_TYPE IN ('MTC', 'MOC', 'SMSO')

--Do not use:

SELECT *

FROM CDRS_NR

WHERE RECORD_TYPE = 'MTC'

OR RECORD_TYPE = 'MOC'

OR RECORD_TYPE = 'SMSO'

--Use:

SELECT *

FROM CDRS_NR

WHERE RECORD_TYPE IN ('MTC', 'MOC', 'SMSO')

17. Use a Power of Oracle Analytic Functions

On first look both SQL queries deliver the same result, but if we have in table RECORDS_NOT_CHARGED around 5.000.000 records the execution time of first SQL would be a few hours. Second SQL which is using Analytic Functions is executed in 2,5 minutes. This is a great performance improvement!

Example:

--Do not use:
   SELECT nc.session_id, nc.msisdn, nc.served_imsi, MIN(nc.datum) datum
     FROM RECORDS_NOT_CHARGED nc
    WHERE nc.charged = 0                      
     AND (EXISTS ( SELECT 1
                     FROM RECORDS_NOT_CHARGED m
                    WHERE m.session_id = nc.session_id
                      AND m.msisdn = nc.msisdn
                      AND m.datum >= nc.datum
                      AND (m.couse_for_cdr_closing ='Normal release' 
                           OR m.couse_for_cdr_closing ='Abnormal release'))
      OR (SYSDATE - nc.datum) >= 2)
     AND NOT EXISTS ( SELECT 1
                        FROM RECORDS_NOT_CHARGED nca
                       WHERE nca.session_id = nc.session_id
                         AND nca.msisdn = nc.msisdn 
                         AND (nca.record_sequence_number = '1' OR nca.record_sequence_number IS NULL))              
    GROUP BY nc.session_id, nc.msisdn, nc.served_imsi;

--Use:
   SELECT DISTINCT session_id, msisdn, served_imsi, min_datum AS datum
     FROM (   SELECT nc.session_id, 
                     nc.msisdn, 
                     nc.served_imsi, 
                     nc.datum,
                     nc.couse_for_cdr_closing,
                     MIN(nc.datum) OVER (PARTITION BY nc.session_id, nc.msisdn, nc.served_imsi) AS min_datum,
                     MIN(nc.record_sequence_number) OVER (PARTITION BY nc.session_id, nc.msisdn, nc.served_imsi ORDER BY nc.record_sequence_number NULLS FIRST) AS min_record_sequence_number,
                     SUM( CASE
                               WHEN (nc.record_sequence_number = '0' OR nc.record_sequence_number = '1' OR nc.record_sequence_number IS NULL) THEN
                                   1
                               ELSE
                                   0
                          END
                         ) OVER (PARTITION BY nc.session_id, nc.msisdn, nc.served_imsi) AS count_rsn_1_or_null
                FROM RECORDS_NOT_CHARGED nc
               WHERE nc.charged = 0 ) a
    WHERE (a.couse_for_cdr_closing IN ('Normal release', 'Abnormal release')
           OR (sysdate - a.min_datum) >= 2)
      AND count_rsn_1_or_null = 0;

--Do not use:

SELECT nc.session_id, nc.msisdn, nc.served_imsi, MIN(nc.datum) datum

FROM RECORDS_NOT_CHARGED nc

WHERE nc.charged = 0

AND (EXISTS ( SELECT 1

FROM RECORDS_NOT_CHARGED m

WHERE m.session_id = nc.session_id

AND m.msisdn = nc.msisdn

AND m.datum >= nc.datum

AND (m.couse_for_cdr_closing ='Normal release'

OR m.couse_for_cdr_closing ='Abnormal release'))

OR (SYSDATE - nc.datum) >= 2)

AND NOT EXISTS ( SELECT 1

FROM RECORDS_NOT_CHARGED nca

WHERE nca.session_id = nc.session_id

AND nca.msisdn = nc.msisdn

AND (nca.record_sequence_number = '1' OR nca.record_sequence_number IS NULL))

GROUP BY nc.session_id, nc.msisdn, nc.served_imsi;

--Use:

SELECT DISTINCT session_id, msisdn, served_imsi, min_datum AS datum

FROM ( SELECT nc.session_id,

nc.msisdn,

nc.served_imsi,

nc.datum,

nc.couse_for_cdr_closing,

MIN(nc.datum) OVER (PARTITION BY nc.session_id, nc.msisdn, nc.served_imsi) AS min_datum,

MIN(nc.record_sequence_number) OVER (PARTITION BY nc.session_id, nc.msisdn, nc.served_imsi ORDER BY nc.record_sequence_number NULLS FIRST) AS min_record_sequence_number,

SUM( CASE

WHEN (nc.record_sequence_number = '0' OR nc.record_sequence_number = '1' OR nc.record_sequence_number IS NULL) THEN

ELSE

END

) OVER (PARTITION BY nc.session_id, nc.msisdn, nc.served_imsi) AS count_rsn_1_or_null

FROM RECORDS_NOT_CHARGED nc

WHERE nc.charged = 0 ) a

WHERE (a.couse_for_cdr_closing IN ('Normal release', 'Abnormal release')

OR (sysdate - a.min_datum) >= 2)

AND count_rsn_1_or_null = 0;

Oracle SQL Performance Tuning

Rules for SQL query optimization:

TOOLS

POSTS