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;
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.
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;
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;
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
7. Avoid 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 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
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;
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
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 )
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')
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;