Skip Scanning of Indexes Advantages

What is skip scanning?

  In prior releases, a composite index would be used to retrieve the records, if the index prefix (leading) column was included in the predicate of the statement. With Oracle9i, the optimizer can use a composite index even if the prefix column value is not known. The optimizer uses an algorithm called skip scanning to retrieve ROWIDs for values that do not use the prefix column.       How does a skip scan work?   During a skip scan, the B*-tree is probed for each distinct value in the prefix column. Under each prefix column value, the normal search algorithm takes over. The result is a series of searches through subsets of the index, each of which appears to result from a query using a specific value of the prefix column. However, with the skip scan, the value of the prefix column in each subset is obtained from the initial index probe rather than from the command predicate. The optimizer uses the statistics to determine whether a skip scan retrieval would be more efficient than a full table scan, or other possible retrieval paths, when parsing SQL statements.   Suppose a composite index exists on the two columns, LANGUAGE andTERRITORY, with LANGUAGE as the prefix column. The data values stored in the underlying table result in the combinations of values in the table, as shown below. Each combination can occur multiple times in the table and the resulting index. In previous releases without the skip scan algorithm, a query on a value in theTERRITORY column was forced to execute a full table scan or a fast full index scan. If the query were more common, then you might have needed to add a new index on the TERRITORY column alone. This new index, could negatively impact the performance of DML on the table. The skip scan solution provides an improvement without the need for the second index. While not as fast as a direct index look up, the skip scan algorithm is faster than a full table scan in cases where the number of distinct values in the prefix column is relatively low.       Note: The skip scan feature cannot be turned on and off by the user. This feature is always on and is transparent to the users.   What are the advantages of skip scanning?

• Skip scans reduce the need to add an index to support occasional queries that do not reference the prefix column of an existing index. This can be useful when high levels of DML activity is expected to degrade because of the existence of too many indexes used to support infrequent queries.
• The algorithm is also valuable in the cases where there are no clear advantages as to which column to use as the prefix column in a composite index.
• The prefix column should be the most discriminating, but also the most frequently referenced in queries. Sometimes, these two requirements are met by two different columns in a composite index, forcing a compromise or the use of multiple indexes. Skip scanning helps to overcome these problems.