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

Sorting and indexing

We have seen various aspects of indexing in Chapter 3, Optimizing Storage Structures. In this recipe, we will focus on how to use indexes to avoid sort operations.

How to do it...

The following steps will demonstrate how to use indexes and avoid sorts:

  1. Connect to the SH schema:
    CONNECT sh@TESTDB/sh
    
  2. Execute an ORDER BY query:
    SET AUTOT TRACE EXP STAT
    SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_CITY
    FROM CUSTOMERS
    ORDER BY CUST_CITY;
    
  3. Execute a SELECT DISTINCT query:
    SET AUTOT TRACE EXP STAT
    SELECT DISTINCT CUST_CITY FROM CUSTOMERS;
    
  4. Execute a GROUP BY query:
    SET AUTOT TRACE EXP STAT
    SELECT CUST_CITY, COUNT(*)
    FROM CUSTOMERS
    GROUP BY CUST_CITY;
    
  5. Add an index on the CUSTOMERS table:
    CREATE INDEX IX_CUST_CITY ON CUSTOMERS( CUST_CITY, CUST_LAST_NAME, ...

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