Code example of Oracle multi-Table query optimization

If you are a beginner in Oracle multi-Table query optimization and do not know much about the actual application of Oracle multi-Table query optimization, the following articles will provide you with more comprehensive knowledge. The following is a detailed description of the article.

Oracle uses the bottom-up sequence to parse the WHERE clause. According to 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 converts '*' into all column names in sequence during parsing. This task is completed by querying the data dictionary in Oracle Multiple tables, this means that it will take more time.

7. Reduce the number of database accesses

When each SQL statement is executed, Oracle performs 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.