Queries Using BETWEEN, LESS THAN, GREATER THAN, or LIKE Operators
Assume that a query involves BETWEEN, LESS THAN, GREATER THAN, or LIKE operations on a single NUSI as in the following examples.
CREATE INDEX (hire_date) ON employee;
SELECT last_name, first_name, hire_date
FROM employee
WHERE hire_date BETWEEN 880101 AND 881231;
SELECT last_name, first_name, hire_date
FROM employee
WHERE hire_date < 880101;
SELECT last_name, first_name, hire_date
FROM employee
WHERE hire_date > 881231;
The following process outlines the approach taken to respond to these requests. This technique can be very efficient for a selective ANDed predicate when there is a composite NUSI that contains all the predicate columns. Optimizer chooses to use the NUSI if the savings in the number of data blocks to read is greater than the overhead of scanning the NUSI and writing and reading a rowID spool.
1 The Optimizer determines whether it is more efficient to do a full‑table scan of the base table rows or to scan the secondary index subtable to get the rowIDs of the qualifying base table rows. Note that ordering the NUSI values can reduce the index scan time.
For purposes of this example, assume that the NUSI access method is the more efficient technique to pursue.
2 The access plan specifies to place those rowIDs into a spool.
3 The access plan specifies to use the resulting rowIDs to access the base table rows.