How to analyze ORACLE data scanning Oracle SQL query optimization guide local range data scanning (6)

By separatingSQL statement for local range data scanning

Generally, it is more effective to put all components of an SQL statement that implements a function together. However, in a few cases, the SQL separation method is more effective. This situation mainly exists in that when you query two tables with a 1: M relationship, the data used to filter the condition for the "M" side comes from the "1" side. Generally, the data on the "M" side is more likely to be greater. If the table on the "1" side is assigned a query condition with a large data range, in the whole data processing process, a large cost is required. The following statement example:

Select Y. deptno, Y. ename, Y. empno, Y. Job, X. sal_tot, X. comm_tot

From (select empno, sum (sal_amt) sal_tot, sum (Comm) comm_tot

From salary s

Where S. deptno like '20140901'

And S. sal_date between '20160301' and '20160301'

Group by empno) x, employee y

Where Y. empno = x. empno;

This statement is used to query the total annual salary of each employee in a department whose department number starts with "12. To improve query performance, deptno fields are redundant in the salary table, and a composite index (inx_dept_sal) based on the deptno and sal_date fields is constructed in the salary table ), in addition, we can know that the employee table and salary table have a typical 1: M relationship.

The driver query condition of this statement is S. deptno like '000000'. In this case, local range scanning cannot be used, and salary tables with a large amount of data can be scanned using this condition. The salary information of all employees in many departments needs to be read, the cost is very high. In addition, although the compound index inx_dept_sal of the salary table is used for queries, because the first field of the composite index does not use equivalent conditions, according to the basic principle of the composite index, we can know that the data size of the index scan will also be very large.

By analyzing the execution logic of the preceding statement, we can see that the driver query condition of the statement is actually from the deptno field of the employee table, while the data volume of the employee table is relatively small compared to the salary table. Therefore, if you separate the original SQL statement, first scan the employee table according to the driver conditions to obtain the required deptno information, the deptno is used as the filter condition to filter the salary table and obtain the final query result, which may achieve better query performance. First, we can use the deptno obtained from the employee table to use the equivalent (=) condition to filter the salary table. Second, we can control the range of data returned by the employee table in each search condition, in this way, local range scanning can be implemented in disguise. Therefore, we can separate the original SQL statement as follows:

Worker scans the employee table separately according to the driver query conditions:

Select deptno: V_deptnoFrom employee like '20140901 ';

In this case, if the deptno field of the employee table has an appropriate index, the scanning performance will be better, and the overall query performance will be higher.

Filter the final result of the salary table by obtaining the deptno information:

Select Y. deptno, Y. ename, Y. empno, Y. Job, X. sal_tot, X. comm_tot

From (select empno, sum (sal_amt) sal_tot, sum (Comm) comm_tot

From salary s

Where S. deptno =: V_deptno

And S. sal_date between '20160301' and '20160301'

Group by empno) x, employee y

Where Y. empno = x. empno;

In this case, the drive query condition for the salary table is changed to the equivalent condition. Therefore, the composite index inx_dept_sal can be used to improve the performance.

Note the following three points when separating SQL statements:

First, it is necessary to accurately identify application scenarios. Generally, when querying two tables with a 1: M relationship, the data used for filtering conditions on the "M" side comes from the "1" side. At the same time, the "M" side has a large amount of data and the statement needs to perform time-consuming operations on the "M" side (for example: group statistics or sorting). At the same time, the data volume of the "1" side is relatively small;

Second, appropriate indexes should be built on important filter fields to maximize local query performance;

Third, we need to control the data scanning that needs to be performed first, so that important field information that is used for filtering is returned in batches (if the data range is large), so as to implement local range scanning;

It can be seen that this method requires us to think more about the design, at the same time, we need more additional steps and more control behavior (For example, the method of returning the scanning results of the first small data table in batches), But compared to what we can achieve performance improvement, these costs are also worth it. However, in any case, This method requires cost evaluation. Therefore, its applicability is limited. When using this method, you must take the preceding three points of attention for a reasonable design.

If you do not want to separate the original SQL statement, the method to improve the performance in this case is very limited, but there are still methods, in this case, you need to use the old rule-based optimizer and have a reasonable and efficient index on key filter fields. You can use the following method to implement the original statement:

Select/* +Rule*/Y. deptno, Y. ename, Y. empno, Y. Job, X. sal_tot, X. comm_tot

From (select empno, sum (sal_amt) sal_tot, sum (Comm) comm_tot

From salary s

Where S. deptno in (select deptno from employee like '2013 ')

And S. sal_date between '20160301' and '20160301'

Group by empno) x, employee y

Where Y. empno = x. empno;

Run the rule prompt to execute the SQL statement according to the index rules. First, run select deptno from employee like '000000' to obtain the information required for index filtering through the S. deptno field. You can also rewrite it as follows:

Select Y. deptno, Y. ename, Y. empno, Y. Job, X. sal_tot, X. comm_tot

From (select/* +Index (s inx_dept_sal)*/

Empno, sum (sal_amt) sal_tot, sum (Comm) comm_tot

From salary s

Where S. deptno in (select deptno from employee like '2013 ')

And S. sal_date between '20160301' and '20160301'

Group by empno) x, employee y

Where Y. empno = x. empno;

The purpose of the two rewriting methods is to ensure that the scanning of small data volume is performed first, and then the data of the big data table is filtered by using indexes. However, whether the last two kinds of rewriting will take effect during the execution process depends on different versions of Oracle to execute the optimizer. Therefore, you need to perform a validation test, at the same time, the latter two types of rewriting do not necessarily ensure local range data scanning. However, the first method is undoubtedly effective.