Parsing Oracle data scanning Oracle SQL Optimization-Guided Local scanning (6)

Related link parsing Oracle data scanning Oracle SQL optimization guide local scanning method (1) http://www.bkjia.com/database/201205/130424.html Parsing Oracle data scanning Oracle SQL optimization guide local scanning method (2) http://www.bkjia.com/database/201205/130428.html Parsing Oracle data scanning Oracle SQL optimization guide local scanning method (3) http://www.bkjia.com/database/201205/130432.html Parsing Oracle data scanning Oracle SQL optimization guide local scanning method (4) http://www.bkjia.com/database/201205/130439.html Parsing Oracle data scanning Oracle SQL optimization guide local scanning method (5) http://www.bkjia.com/database/201205/130593.html By separating SQL statements to scan data in a local range, it is usually more effective to make all components of SQL statements that implement a function run 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 www.2cto.com Where s. deptno like '200' And s. sal_date between '000000' and '000000' Group by empno) x, employee y Where y. empno = x. empno; this statement queries 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: Explain scans the employee table separately according to the driver query conditions: Select deptno into: v_deptno from employee like '123456 '; 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. Filters 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 www.2cto.com 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 '000000' and '000000' 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, so the composite index inx_dept_sal can be used to improve the performance. Note the following three points for separating SQL statements: First, you must accurately identify the application scenarios. The common application scenario is: when two tables in the M relationship are queried, the data used to filter the condition for the M side comes from the "1" side, and the data volume of the M side is large, in addition, the statement needs to perform time-consuming operations (such as grouping statistics or sorting) on "M", and the data volume of "1" side is relatively small; second, we need to build an appropriate index on important filter fields to maximize the local query performance. Third, we need to control the data scanning that needs to be performed first after the split, batch return of important field information used for filtering (if the data range is large) to achieve local range scanning; we can see that this method requires more consideration in design, at the same time, more additional steps and more control behaviors are also required for implementation (for example, the means to return the scanning results of the first small data table in batches ), however, these costs are also worth comparing the performance improvements we can achieve. 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: www.2cto.com 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 '201312') And s. sal_date between '000000' and '000000' Group by empno) x, employee y Where y. empno = x. empno; execute the SQL statement according to the index rules by using the rule prompt. First, execute Select deptno from employee like' 12% 'to obtain the information required for index filtering through the s. deptno field. You can also rewrite the statement 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 www.2cto.com Where s. deptno in (Select deptno from employee like '201312') And s. sal_date between '000000' and '000000' Group by empno) x, employee y Where y. empno = x. empno; the two rewriting methods aim to ensure that the scanning of small data volume is performed first, and then the data of 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. From javacoffe's column