 |
|
Oracle Tips by Burleson |
Using Oracle 10g Index Skip
Scans
The index skip scan is a new execution plan in
Oracle 10g whereby an Oracle query can bypass the leading-edge of a
concatenated index and access the inside keys of a multi-values index.
For example, consider the following concatenated index on region and
employee ID:
create index
region_emp_id
on
emp (region, emp_id)
;
Prior to Oracle 10g, this index could only be
used with both region and emp_id were present in the SQL query, or
when the region column was specified. For example, the following
query would not be able to use the concatenated index:
select
emp_id
from
emp
where
emp_id = 123;
The Oracle 10g skip scan execution plan allows
for the concatenated index to be used, even though sex is not
specified in the SQL query. This feature promises that there is
no need to provide a second index on the emp_id column. Oracle
acknowledges that the index skip scan is not as fast as a direct index
lookup, bit states that the index skip scan is faster than a
full-table scan.
What Oracles does not mention is that the
cardinality of the leading column has a direct impact on the speed of
the index skip scan. In our example, the first column, region,
has four distinct values (Figure 1).
While Oracle does not publish the internals of
the index skip scan, we can infer from the execution plans that Oracle
is internally generating multiple queries, thereby satisfying the
query with multiple sub-queries:
SELECT
STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)
0 SORT (AGGREGATE)
1 INDEX (SKIP SCAN) OF 'REGION_EMP_ID'
(NON-UNIQUE)
Internally, Oracle 10g is probably generating
two queries and joining the resulting Row ID lists:
select
emp_name from emp_where region = ‘NORTH’ and emp_id = 123
UNION
select emp_name from emp_where region = ‘SOUTH’ and emp_id = 123
UNION
select emp_name from emp_where region = ‘EAST’ and emp_id = 123
UNION
select emp_name from emp_where region = ‘WEST’ and emp_id = 123;
The implications of using the index skip scan
are clearly related to the number of distinct values in the leading
edge column.
Oracle skip scan execution plan performance
will decrease according to the number of unique values in the high
order key. If the leading column were “state” with 50
values, Oracle would be issuing 50 index probes to retrieve the result
set.
The index skip scan is only useful in shops
where disk space savings are critical. Shops that can afford the
disk space to build a second index will always get faster performance.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
