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 skip-scan and index range-scan

In this recipe, we will see how to use composite indexes and also the difference between index skip-scan and index range-scan operations.

Getting ready

For this recipe, we will use a copy of the CUSTOMERS table in the SH schema and SQL*Plus to execute our tests.

How to do it...

The following steps will demonstrate index skip-scan and index range-scan:

  1. Connect to SH schema:
    CONNECT sh@TESTDB/sh
    
  2. Create MY_CUSTOMERS table as a copy of CUSTOMERS:
    CREATE TABLE sh.MY_CUSTOMERS AS
    SELECT * FROM sh.CUSTOMERS NOLOGGING;
    
  3. Create an index on the MY_CUSTOMERS table based on multiple fields:
    CREATE INDEX sh.CUSTOMERS_IXMULTI ON sh.MY_CUSTOMERS
    (CUST_GENDER, CUST_YEAR_OF_BIRTH, CUST_FIRST_NAME);
    
  4. Compute statistics on ...

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