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

Filter-type local range scan

During daily application development, you often encounter the need to determine whether a set that meets certain query conditions exists. In fact, this operation is essentially a special filtering operation, which requires a set as the source, another set is the judgment standard, which is used as the filter to go to the filter source set.

During this operation, the most ideal execution method is to stop the entire execution process and return results when the first matching record is met, 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 = '2013' and SEQ> 101;

If the query result returned by this statement is greater than 0, it indicates that a set that meets the query condition dept = '20160901' 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

Where exists (select 'x' from item_tab where dept = '2013' 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 look at an example to judge whether 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 '123'

Minus

Select ord_dept, ord_date, '123'

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

Where ord_date like '123'

And not exists (select * from sales y

Where Y. ord_dept = x. ord_dept and Y. ord_date = x. ord_date

And y. cusno = '20140901 ');

During the execution of this query, local range scanning is used, and the data range meeting the driver query condition ord_date like '123' is large, 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.