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:
Connect to the
SH
schema:CONNECT sh@TESTDB/sh
SET AUTOT TRACE EXP STAT SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_CITY FROM CUSTOMERS ORDER BY CUST_CITY;
Execute a
SELECT DISTINCT
query: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;
Add an index on the
CUSTOMERS
table:CREATE INDEX IX_CUST_CITY ON CUSTOMERS( CUST_CITY, CUST_LAST_NAME, CUST_FIRST_NAME);
Execute an
ORDER BY
query (the same query as in step 2):SET AUTOT TRACE EXP STAT SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_CITY FROM CUSTOMERS ORDER BY CUST_CITY;
Execute a
SELECT DISTINCT
query (the same...