In this recipe, we will see how to use composite indexes and also the difference between index skip-scan and index range-scan operations.
For this recipe, we will use a copy of the
CUSTOMERS table in the
SH schema and SQL*Plus to execute our tests.
The following steps will demonstrate index skip-scan and index range-scan:
MY_CUSTOMERStable as a copy of
CREATE TABLE sh.MY_CUSTOMERS AS SELECT * FROM sh.CUSTOMERS NOLOGGING;
MY_CUSTOMERStable based on multiple fields:
CREATE INDEX sh.CUSTOMERS_IXMULTI ON sh.MY_CUSTOMERS (CUST_GENDER, CUST_YEAR_OF_BIRTH, CUST_FIRST_NAME);