Index Skip Scan

An index skip scan uses logical subindexes of a composite index. The database “skips” through a single index as if it were searching separate indexes.

oracle siebel crm training courses malaysia

Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index. The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate.

oracle peoplesoft training courses malaysia

Example 3-3 Skip Scan of a Composite Index

Assume that you run the following query for a customer in the sh.customers table:

CopySELECT * FROM sh.customers WHERE cust_email = 'Abbey@company.example.com';

The customers table has a column cust_gender whose values are either M or F. Assume that a composite index exists on the columns (cust_gender, cust_email). The following example shows a portion of the index entries:

CopyF,Wolf@company.example.com,rowid
F,Wolsey@company.example.com,rowid
F,Wood@company.example.com,rowid
F,Woodman@company.example.com,rowid
F,Yang@company.example.com,rowid
F,Zimmerman@company.example.com,rowid
M,Abbassi@company.example.com,rowid
M,Abbey@company.example.com,rowid

The database can use a skip scan of this index even though cust_gender is not specified in the WHERE clause.

In a skip scan, the number of logical subindexes is determined by the number of distinct values in the leading column. In the preceding example, the leading column has two possible values. The database logically splits the index into one subindex with the key F and a second subindex with the key M.

oracle linux administration training courses malaysia

When searching for the record for the customer whose email is Abbey@company.example.com, the database searches the subindex with the value F first and then searches the subindex with the value M. Conceptually, the database processes the query as follows:

CopySELECT * FROM sh.customers WHERE cust_gender = 'F' 
  AND cust_email = 'Abbey@company.example.com'
UNION ALL
SELECT * FROM sh.customers WHERE cust_gender = 'M'
  AND cust_email = 'Abbey@company.example.com';

oracle e business suite training courses malaysia