Efficient SQL Statements

1. Don't "select *"

2. Use "DECODE" to avoid scan same records

3. Delete data by ROWID (best efficient)

4. "TRUNCATE", not "DELETE"

5. Calculate rows by calculating index column, like COUNT（EMPNO）

6. Where instead of "HAVING"
(X) SELECT REGION， AVG （LOG_SIZE） FROM LOCATION GROUP BY REGION HAVING REGION REGION ！= ‘SYDNEY’ AND REGION ！= ‘PERTH’
(O) SELECT REGION， AVG （LOG_SIZE） FROM LOCATION WHERE REGION REGION ！= ‘SYDNEY’ ND REGION ！= ‘PERTH’ GROUP BY REGION

7. EXISTS instead of "IN"
(X) SELECT * FROM EMP WHERE EMPNO 》 0 AND DEPTNO IN （ SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’）
(O) SELECT * FROM EMP WHERE EMPNO 》 0 AND EXISTS （ SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’）

8. "NOT EXISTS" instead of "NOT IN"
(X) SELECT …FROM EMP WHERE DEPT_NO NOT IN （SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’）；
(O) SELECT … FROM EMP A，DEPT B WHERE A.DEPT_NO = B.DEPT（+） AND B.DEPT_NO IS NULL AND B.DEPT_CAT（+） = ‘A’
*(O) SELECT … FROM EMP E WHERE NOT EXISTS （ SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’）；

9. "EXISTS" instead of "DISTINCT"
(X) SELECT DISTINCT DEPT_NO，DEPT_NAME FROM DEPT D，EMP E WHERE D.DEPT_NO = E.DEPT_NO
(O) SELECT DEPT_NO，DEPT_NAME FROM DEPT D WHERE EXISTS （ SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO）；

10. Execution by index. * Restructure index

11. Don't perform a function on the indexed colum
(X) SELECT … FROM DEPT WHERE SAL * 12 》 25000;
(O) SELECT … FROM DEPT WHERE SAL 》 25000/12;

12. Don't perform mathematical operations on the indexed column
(X) SELECT ... FROM SALARY WHERE salary + 1 = 10001
(O) SELECT ... FROM SALARY WHERE salary = 10000

13. Don't concatenate a column
(X) SELECT ... FROM EMPLOYEE WHERE firstname || ' ' || lastname = 'JOHN JONES'
(O) SELECT ... FROM EMPLOYEE WHERE firstname = 'JOHN' and lastname = 'JONES'

*14. Include the first column of a concatenated index in the WHERE clause of your statement

15. 》= instead of 》
Suggested DEPTNO is a index column.
(X) SELECT * FROM EMP WHERE DEPTNO 》=4
(O) SELECT * FROM EMP WHERE DEPTNO 》3

16. Don't use 'OR' statements to confuse the Cost Based Optimizer (CBO). It will even ignore optimizer hints in this situation. The only way of guaranteeing the use of indexes in these situations is to use an INDEX hint.