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 on the table:

   EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_CUSTOMERS',
     estimate_percent => 100,
     method_opt => 'for all columns size 1');
   

5. Execute a query on the table, using the first two fields of the CUSTOMERS_IXMULTI index in the predicate...