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:
Connect to
SHschema:CONNECT sh@TESTDB/shCreate
MY_CUSTOMERStable as a copy ofCUSTOMERS:CREATE TABLE sh.MY_CUSTOMERS AS SELECT * FROM sh.CUSTOMERS NOLOGGING;
Create an index on the
MY_CUSTOMERStable based on multiple fields:CREATE INDEX sh.CUSTOMERS_IXMULTI ON sh.MY_CUSTOMERS (CUST_GENDER, CUST_YEAR_OF_BIRTH, CUST_FIRST_NAME);
Compute statistics on the table:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_CUSTOMERS', estimate_percent => 100, method_opt => 'for all columns size 1');
Execute a query on the table, using the first two fields of the
CUSTOMERS_IXMULTIindex in the predicate...