Exploring index lookup

In the previous recipe, we have seen some issues related to FTS operations and when it's better to avoid them.

One of the methods to avoid FTS is indexing. In this recipe, several issues related to index lookup and index scan will be presented along with an illustration of the counterpart for indexes of FTS operation—Index Full Scan.

Getting ready

The examples given are based on a copy of the CUSTOMERS table in the SH schema; we will use SQL*Plus to execute our tests.

How to do it...

The following steps will demonstrate index lookup:

1. Connect to SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create the MY_CUSTOMERS table as a copy of CUSTOMERS:

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

3. Update the CUST_VALID field to obtain a skewed distribution of values:

   UPDATE sh.MY_CUSTOMERS SET
     CUST_VALID = 'I'
     WHERE CUST_VALID = 'A' AND MOD(CUST_ID,100) <> 0;
   SELECT CUST_VALID, COUNT(*)
     FROM sh.MY_CUSTOMERS
     GROUP BY CUST_VALID;
   

4. Create an index on the MY_CUSTOMERS...