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

During daily application development, filter-type local range scanning often encounters a need to determine whether a set that meets certain query conditions exists. In fact, this operation is essentially a special filtering operation, you need to use one set as the source and the other set as the judgment standard, and use this judgment standard as the filter to go to the filter source set. Www.2cto.com is the most ideal method for executing this operation. When the first qualified record is met, the whole execution process will be stopped and the results will be returned, this is because the existence of the set that meets the conditions has been confirmed. If the batch array has been filled up, it can be returned as a result. In fact, local range scanning is used in the process of performing this filtering operation. However, in daily development, most operations cannot be executed in this way due to the arbitrary compilation of statements to implement such filtering operations, but to determine the existence of such operations, the global data scan is not performed properly, resulting in poor query performance. Let's look at the following statement example: Select count (*) from item_tab where dept = '000000' and seq> 101; if the query result returned by this statement is greater than 0, the set that meets the query condition dept = '20160301' and seq> 101 exists. You can use this method to determine the existence of a set. However, in this way, you cannot scan all the data that meets the conditions. In this way, when the data volume is large, low query performance. However, if we replace the preceding statement with the following statement: Select 1 into: cnt from dual www.2cto.com where exists (select 'x' from item_tab where dept = '2016' and seq> 101 ); you can use this method to rewrite the preceding statement to obtain the query results in a short time. This is because the subquery is executed in the form of a local range scan during execution of statements in this way, thanks to the role of the exists predicate, exists is a Boolean function that checks whether the subquery results exist. If yes, true is returned. Otherwise, false is returned. Therefore, the subquery ends immediately when the first condition record is met, and the final result is returned through the primary query. The FILTER execution plan will appear in the execution plan generated by the preceding statement, which also shows that this query is a special filtering operation (different from the traditional filtering operation, if traditional filtering operations become "Conditional filtering", such filtering operations can be called "set filtering "). I think many people have heard that using the exists operation to execute SQL can achieve good performance, especially by replacing in or not in with exists or not exists, it will achieve very good results. In fact, in this case, local range scanning is playing a role, rather than exists or not exists. Therefore, you cannot use exists or not exists without thinking about it. Let's take a look at an example to judge that a set does not belong to another set, as shown in the following statement: Select ord_dept, ord_date, custno From order where ord_date like '000000' Minus Select ord_dept, ord_date, '000000' From sales where custno = '000000'; when executing the preceding statement, each row of data is scanned, and a wide range of data needs to be scanned to find and confirm the corresponding data rows. Therefore, we need to consider how to avoid this situation through local range scanning. We can rewrite the statement as follows: Select ord_dept, ord_date, custno From order x www.2cto.com Where ord_date like '2013' And not exists (select * from sales y Where y. ord_dept = x. ord_dept and y. ord_date = x. ord_date And y. cusno = '000000'); During execution, this query uses a local range scan and has a large data range that meets the driver query condition ord_date like '000000, at the same time, the data range of the subquery that meets the filtering condition is very small, so the data range that meets the opposite not exists will be very large. Therefore, according to the local range scanning performance policy matrix, we can see that, the final query performance is very high. From javacoffe's column