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
SHschema:CONNECT sh@TESTDB/shSET AUTOT TRACE EXP STAT SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_CITY FROM CUSTOMERS ORDER BY CUST_CITY;
Execute a
SELECT DISTINCTquery: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
CUSTOMERStable:CREATE INDEX IX_CUST_CITY ON CUSTOMERS( CUST_CITY, CUST_LAST_NAME, CUST_FIRST_NAME);
Execute an
ORDER BYquery (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 DISTINCTquery (the same...