Partial Range Scan 3

The principle to improve the execution speed of Partial Range Scan

Take a look at an example first,

SELECT * FROM order;

Generally, the SQL statement above will get the results returned (first set of data) quickly. But the SQL statement below will get the returned much more slowly.

SELECT * FROM order ORDER BY item;

The reason is not merely because there is one SORT operation in the second SQL statement. The more important reason is that the SORT operation causes the SQL engine need to perform the FULL RANGE (table) SCAN operation before the first set of data can be returned.
If there is one index of which the leading column is “item”, the SQL statement above can be rewritten as follows,

SELECT * FROM order WHERE item > ' ';

This way, the optimizer will take advantage of the index to perform the data scan and the partial range scan is possible. We can also uses the hint INDEX to impose the use the index, like…

SELECT /*+ index (order item_index) */ * FROM order WHERE item > ' ';

The principle of Partial Range Scan

If the data volume that meets the “driving” query condition is small, the execution operation cost will be less. If the data volume that meets the “filtering” query condition is big, the execution operation cost will be low.
In order to make the query condition that will lead to small data volume be the “driving” condition, we can take advantage of some hints (index, etc) or other methods.
For example, suppose there are indexes created on the column “ordno” and “custno”, but the query condition on the column “custo” will lead to smaller data volume which is appropriate for the driving condition, we can instruct the optimizer to follow our intent…

SELECT * FROM order WHERE RTRIM(ordno) between 1 and 1000 AND custno like 'DN%';
SELECT /*+ INDEX(order custno_index)*/ * FROM order WHERE ordno BETWEEN 1 and 1000 AND cusno like 'DN%';