ORACLE SQL Performance Optimization Series (v)

oracle| Performance | optimization
17. Use table alias (alias)

When you connect multiple tables in an SQL statement, use the alias of the table and prefix the alias with each column. This allows you to reduce parsing time and reduce syntax errors caused by column ambiguity.



(Translator Note: column ambiguity means that because different tables in SQL have the same column name, the SQL parser cannot determine the attribution of this column when the column appears in the SQL statement)



18. Replace in with exists

In many queries based on the underlying table, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) usually increases the efficiency of the query.



Low efficiency:

SELECT *

From EMP (base table)

WHERE EMPNO > 0

and DEPTNO in (SELECT DEPTNO

From DEPT

WHERE LOC = ' Melb ')



Efficient:

SELECT *

From EMP (base table)

WHERE EMPNO > 0

and EXISTS (SELECT ' X '

From DEPT

WHERE DEPT. DEPTNO = EMP. DEPTNO

and LOC = ' Melb ')





(Translator: Relatively speaking, replacing not in with not exists will significantly increase efficiency, as noted in the next section)





19. Use NOT exists instead of in

In a subquery, the NOT IN clause performs an internal sort and merge.  In either case, not in is the least efficient (because it performs a full table traversal of the table in the subquery). In order to avoid using not in, we can rewrite it as an outer join (Outer joins) or not EXISTS.



For example:

SELECT ...

From EMP

WHERE dept_no not in (SELECT dept_no

From DEPT

WHERE dept_cat= ' A ');



To improve efficiency. Rewrite as:



(Method one: efficient)

SELECT ....

From EMP a,dept B

WHERE A.dept_no = b.dept (+)

and B.dept_no is NULL

and B.dept_cat (+) = ' A '





(Method two: most efficient)

SELECT ....

From EMP E

WHERE not EXISTS (SELECT ' X '

From DEPT D

WHERE d.dept_no = E.dept_no

and Dept_cat = ' A ');



adjourned