Oracle performance SQL Tuning

We recommend that you use the following methods and Suggestions after testing!

17.Use the table alias (alias)

When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each column. in this way, the parsing time can be reduced and the syntax errors caused by column ambiguity can be reduced.

 

(Note: ColumnAmbiguity refers to the SQLDifferent tables have the same columnName,When SQLThe column appears in the statement.SQLThe parser cannot determine this column)

 

18.Use existsReplace in

In many basic table-based queries, to meet one condition, you often need to join another table. in this case, using exists (or not exists) usually improves the query efficiency.

 

Inefficiency:

Select *

From EMP (basic table)

Where empno> 0

And deptno in (select deptno

From Dept

Where loc = 'melb ')

Efficient:

Select *

From EMP (basic table)

Where empno> 0

And exists (select 'x'

From Dept

Where Dept. deptno = EMP. deptno

And loc = 'melb ')

 

 

 (Press:Relatively speaking,Use not existsReplace not inIt will significantly improve efficiency,As described in the next section)

 

 

19.Use not existsReplace not in

In a subquery, the not in Clause executes an internal sorting and merging. in either case, not in is the most inefficient (because it executes a full table traversal for the table in the subquery ). to avoid the use of not in, we can rewrite it into 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 it:

 

(Method 1: 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 2: most efficient) --- available

Select ....

From EMP E

Where not exists (select 'x'

From dept d

Where D. dept_no = E. dept_no

And dept_cat = 'A ');