Databases Reference
In-Depth Information
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, CUST_FIRST_NAME);
6.
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;
7.
Execute a
SELECT
DISTINCT
query (the same query as in step 3):
SET AUTOT TRACE EXP STAT
SELECT DISTINCT CUST_CITY FROM CUSTOMERS;
