Oracle Database 11gR2 Performance Tuning Cookbook

Exploring index skip-scan and index range-scan

In this recipe, we will see how to use composite indexes and also the difference between index skip-scan and index range-scan operations.

Getting ready

For this recipe, we will use a copy of the CUSTOMERS table in the SH schema and SQL*Plus to execute our tests.

How to do it...

The following steps will demonstrate index skip-scan and index range-scan:

1. Connect to SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create MY_CUSTOMERS table as a copy of CUSTOMERS:

   CREATE TABLE sh.MY_CUSTOMERS AS
     SELECT * FROM sh.CUSTOMERS NOLOGGING;
   

3. Create an index on the MY_CUSTOMERS table based on multiple fields:

   CREATE INDEX sh.CUSTOMERS_IXMULTI ON sh.MY_CUSTOMERS
     (CUST_GENDER, CUST_YEAR_OF_BIRTH, CUST_FIRST_NAME);
   

4. Compute statistics ...