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 ...

Get Oracle Database 11gR2 Performance Tuning Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial