O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required