Databases Reference
In-Depth Information
6.
Test the same query from the previous step but with the less common value:
SET AUTOT TRACE EXP STAT
SELECT * FROM sh.MY_CUSTOMERS WHERE CUST_VALID = 'A';
7.
Execute a slightly different query, and replace the previous predicate with a not
equal condition (resulting in the same data):
SET AUTOT TRACE EXP STAT
SELECT * FROM sh.MY_CUSTOMERS WHERE CUST_VALID <> 'I';
8.
Finally, drop the table created for testing:
DROP TABLE sh.MY_CUSTOMERS;
How it works...
After following step 1 to step 3, we will reach the following situation:
We have a table where the values in
CUST_VALID
column are largely skewed—we define a
column as skewed where the values in the column are not equi-distributed, resulting in less
than 0.2 percent of rows with a value of "A" in this field.
In the same column, create an index and execute a query with the same column in
the predicate.
In the first query (step 5), it will ask for the rows with the most common value for the
CUST_VALID
field, resulting in the execution plan illustrated in the following screenshot:
