The optimizer can
also use a ???skip-scan??? approach in which a concatenated index is used even if its leading column
is not in the query??™s where clause.
Indexes must be tailored to the access path needed. Consider the case of a three-column,
concatenated index. As shown in the following listing, this index is created on the City, State,
and Zip columns of the EMPLOYEE table:
create index CITY_ST_ZIP_NDX
on EMPLOYEE(City, State, Zip)
tablespace INDEXES;
If a query of the form
select * from EMPLOYEE
where State='NJ';
is executed, then the leading column of the index (City) is not in the where clause. Oracle can
use two types of index-based accesses to retrieve the rows??”a skip-scan of the index or a full scan
of the index. The optimizer will select an execution path based on the index??™s statistics??”its size,
the size of the table, and the selectivity of the index. If users will frequently run this type of query,
the index??™s columns may need to be reordered with State first in order to reflect the actual usage
pattern.
An index range scan is another index-based optimization that Oracle can use to efficiently
retrieve selective data. Oracle uses an index range scan when the variable in the where clause is
equal to, less than, or greater than the specified constant and the variable is the leading column if
248 Oracle Database 11g DBA Handbook
the index is a multi-part index. No order by clause is required if you want the rows returned in the
index order, as in this example where you are looking for employees hired before August 1st, 2007:
select * from EMPLOYEE where hire_date < '1-AUG-2007';
It is critical that the table??™s data be as ordered as possible.
Pages:
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416