Skip Scan Index Access
Jeff Hunter, www.idevelopment.info

In Oracle, releases before Oracle9i, the Cost Based Optimizer (CBO) would only use a composite index (sometimes referred to as a concatenated index) if you referenced the index prefix column in the WHERE clause of the SQL statement. If the SQL statement omitted the index prefix column of the composite index, the CBO would not use the index.

One solution would be to simply create another index on the non-prefixed index columns in order for Oracle to use an index for faster access to the data. This method, however, may add considerable overhead when Oracle had to update several indexes during DML operations. A new feature included with Oracle9i called Skip Scan Index Access allows the Oracle CBO to use a composite index even when the index prefix column in a SQL statement has been omitted. Using this new feature, you will not have to create an additional index to provide faster access to your data. This article provides a brief introduction to Skip Scan Index Access and how it can improve the response time of your queries.

Oracle9i Support for Skip Scan Index Access

Oracle will attempt to use the new Skip Scan Index Access feature and scan a composite index when the SQL does not contain the index prefix column. This new feature can be used for the default B-tree index, however, you cannot use the skip scan index access technology for bitmap indexes, function-bases indexes, domain indexes, and for reverse key keys. Oracle9i does, however, supporting the new skip scan index access feature on clustered indexes, descending indexes, and using the CONNECT BY clause of the SQL statement.

How Skip Scan Index Access Works

Now let's see how the skip scan technology works. Oracle will use the skip scan algorithm to determine the domain of distinct values for the index prefix column and then iterate through each distinct value in this domain. For each distinct value, Oracle will perform a regular index scan on that portion of the index. To put it another way, Oracle will treat the composite index search as a number of small sub-indexes to retrieve the ROWIDs - there will be one sub-index search performed for each distinct value within the index prefix column. The new skip scan index access feature will function as if you issued a sequence of SQL statements having each statement specifying a single value for the index prefix column.

Skip scan index access is not a silver bullet. While it is faster than performing a full table scan (FTS), it is not faster than a full index scan (FIS). The real benefits and efficiencies for using scan index access increase when the domain of distinct values for the prefix index column decreases. Basically, when the domain of distinct values for the prefix index column, there are fewer sub-indexes Oracle will have to utilize. When determining whether to use the skip scan index access algorithm over a full table scan (or other possible data access paths), the Oracle CBO will use the data distribution statistics of the prefix index column. Keep in mind that the skip scan index access feature can only be used with the Oracle cost based optimizer.

Example

The following simple example should provide an insight to how the skip scan index access feature works. In this example, we will create a composite index. When the skip scan index access algorithm is going to be used, it will split the composite index into logically smaller indexes. The number of logical sub-indexes will depend on the cardinality of the prefix index column. Let's take a look at an example.

SQL> create table test1(a number, b number);

Table created.


SQL> begin
  2  for i in 1 .. 1000
  3  loop
  4  insert into test1 values(mod(i, 10), 100);
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.


SQL> create index test1_i on test1(a, b);

Index created.


SQL> exec dbms_stats.gather_table_stats(ownname => 'SYSTEM', tabname => 'test1', cascade => true);

PL/SQL procedure successfully completed.


SQL> explain plan for select /*+ index_ss(test1 test1_i) */ a, b from test1 where b = 101;

Explained.


SQL> start ?\rdbms\admin\utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     5 |    11 |
|*  1 |  INDEX SKIP SCAN     | TEST1_I     |     1 |     5 |    11 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - access("TEST1"."B"=101)
       filter("TEST1"."B"=101)

Note: cpu costing is off

15 rows selected.