Oracle Database 11gR2 Performance Tuning Cookbook

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