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

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

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.