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.
The following steps will demonstrate how to use indexes and avoid sorts:
SET AUTOT TRACE EXP STAT SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_CITY FROM CUSTOMERS ORDER BY CUST_CITY;
SET AUTOT TRACE EXP STAT SELECT DISTINCT CUST_CITY FROM CUSTOMERS;
SET AUTOT TRACE EXP STAT SELECT CUST_CITY, COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY;
CREATE INDEX IX_CUST_CITY ON CUSTOMERS( CUST_CITY, CUST_LAST_NAME, ...