Databases Reference
In-Depth Information
How to do it...
The following steps will demonstrate index skip-scan and index range-scan:
1.
Connect to
SH
schema:
CONNECT sh@TESTDB/sh
2.
Create
MY_CUSTOMERS
table as a copy of
CUSTOMERS
:
CREATE TABLE sh.MY_CUSTOMERS AS
SELECT * FROM sh.CUSTOMERS NOLOGGING;
3.
Create an index on the
MY_CUSTOMERS
table based on multiple fields:
CREATE INDEX sh.CUSTOMERS_IXMULTI ON sh.MY_CUSTOMERS
(CUST_GENDER, CUST_YEAR_OF_BIRTH, CUST_FIRST_NAME);
4.
Compute statistics on the table:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'MY_CUSTOMERS',
estimate_percent => 100,
method_opt => 'for all columns size 1');
5. Execute a query on the table, using the first two fields of the
CUSTOMERS_IXMULTI
index in the predicate:
SET AUTOT TRACE EXP
SELECT CUST_ID FROM sh.MY_CUSTOMERS
WHERE CUST_GENDER = 'M' AND CUST_YEAR_OF_BIRTH = 1945;
6. Execute a query using the first and the last fields of the index in the predicate:
SELECT CUST_ID FROM sh.MY_CUSTOMERS
WHERE CUST_GENDER = 'F' AND CUST_FIRST_NAME = 'Yvette';
7. Execute a query using the second and the last fields of the index in the predicate:
SELECT * FROM sh.MY_CUSTOMERS
WHERE CUST_YEAR_OF_BIRTH = 1951
AND CUST_FIRST_NAME = 'Yvette';
8.
Drop the table used for testing:
SET AUTOT OFF
DROP TABLE sh.MY_CUSTOMERS;
