Databases Reference
In-Depth Information
Exploring index lookup
In the previous recipe, we have seen some issues related to FTS operations and when it's
better to avoid them.
One of the methods to avoid FTS is indexing. In this recipe, several issues related to index
lookup and index scan will be presented along with an illustration of the counterpart for
indexes of FTS operation—Index Full Scan.
Getting ready
The examples given are based on a copy of the
CUSTOMERS
table in the
SH
schema; we
will use SQL*Plus to execute our tests.
How to do it...
The following steps will demonstrate index lookup:
1.
Connect to
SH
schema:
CONNECT sh@TESTDB/sh
2.
Create the
MY_CUSTOMERS
table as a copy of
CUSTOMERS
:
CREATE TABLE sh.MY_CUSTOMERS AS
SELECT * FROM sh.CUSTOMERS NOLOGGING;
3.
Update the
CUST_VALID
field to obtain a skewed distribution of values:
UPDATE sh.MY_CUSTOMERS SET
CUST_VALID = 'I'
WHERE CUST_VALID = 'A' AND MOD(CUST_ID,100) <> 0;
SELECT CUST_VALID, COUNT(*)
FROM sh.MY_CUSTOMERS
GROUP BY CUST_VALID;
4.
Create an index on the
MY_CUSTOMERS
table to test different execution plans:
CREATE INDEX sh.MY_CUSTOMERS_IXVALID
ON sh.MY_CUSTOMERS (CUST_VALID);
5.
Test a query on the table, looking for the most common value:
SET AUTOT TRACE EXP STAT
SELECT * FROM sh.MY_CUSTOMERS WHERE CUST_VALID = 'I';
