Oracle multi-Table query optimization solution

The following articles mainly describe the optimization solution for Oracle multi-table queries. We all know that Oracle uses a bottom-up sequence to parse WHERE clauses. Based on this principle, the join between tables must be written before other WHERE conditions.

The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

For example:

 

  
(Inefficient, execution time: 156.3 seconds)
  
SELECT...
  
FROM EMP E
  
Where sal>; 50000
  
And job = 'manager'
  
AND 25 <(select count (*) FROM EMP
  
Where mgr = E. EMPNO );
  
(Efficient, execution time: 10.6 seconds)
  
SELECT...
  
FROM EMP E
  
WHERE 25 <(select count (*) FROM EMP
  
Where mgr = E. EMPNO)
  
And sal>; 50000
  
And job = 'manager ';
 

Focus on 3: Avoid using '*' in the SELECT clause. Focus on

When you want to list all columns in the SELECT clause, using dynamic SQL COLUMN reference '*' is a convenient method. unfortunately, this is a very inefficient method. in fact, Oracle multi-table queries convert '*' into all column names in sequence during parsing. This task is completed by querying the data dictionary, this means that it will take more time.

7. Reduce the number of database accesses

When each SQL statement is executed, Oracle multi-table queries perform a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks. it can be seen that reducing the number of visits to the database can actually reduce the workload of Oracle.

For example,

There are three ways to retrieve employees with employee numbers equal to 0342 or 0291.

Method 1 (most inefficient)

 

  
SELECT EMP_NAME , SALARY , GRADE  
  
FROM EMP  
  
WHERE EMP_NO = 342;  
  
SELECT EMP_NAME , SALARY , GRADE  
  
FROM EMP  
  
WHERE EMP_NO = 291;  
 

Method 2 (low efficiency)

 

  
DECLARE  
  
CURSOR C1 (E_NO NUMBER) IS  
  
SELECT EMP_NAME,SALARY,GRADE  
  
FROM EMP  
  
WHERE EMP_NO = E_NO;  
  
BEGIN  
  
OPEN C1(342);  
  
FETCH C1 INTO …,..,.. ;  
  
OPEN C1(291);  
  
FETCH C1 INTO …,..,.. ;  
  
CLOSE C1;  
  
END;   
 

The above content is an introduction to Oracle multi-Table query optimization. I hope you will find some gains.