Evaluating Index Usage for
Skewed Column Values
Oracle Tips by Burleson Consulting
Even within a specific column value, the
value of the column can impact the execution plan. For example,
assume that a student table has 1000 rows, representing 900
undergraduate students and 100 graduate students. A nonunique index
has been built on the student_level field that indicates
UNDERGRAD or GRAD. The same query will benefit from different access
methods depending upon the value of the literal in the where
clause. The following query will retrieve 90 percent of the rows in
the table, and it will run faster with a full-table scan than it
will if the SQL optimizer chooses to use an index:
student_level = 'UNDERGRAD';
This next query will access only 10 percent
of the table rows, and it will run faster by using the index on the
student_level = 'GRAD';
Unfortunately, the Oracle database cannot
predict in advance the number of rows that will be returned from a
query. Many SQL optimizers will invoke an index access even though it
may not always be the fastest access method.
Oracle, for example, allows the concatenation
of a null string to the field name in the where clause to
suppress index access. The previous query could be rewritten in Oracle
SQL to bypass the student_level index as follows:
student_level||'' = 'UNDERGRAD';
The concatenation (||) of a null string to the
field tells the Oracle SQL optimizer to bypass index processing for
this field, instead invoking a faster-running full-table scan.
This is a very important point. While the
Oracle9i SQL optimizer is becoming more intelligent about the
best access plan, it still cannot always estimate the number of rows
returned by a specific Boolean predicate and will not always choose
the best access path.
The not equal (<>) operator will cause an index
to be bypassed and the query “show all undergrads who are not computer
science majors” will cause a full-table scan:
student_level = 'UNDERGRAD'
major <> 'computer science';
Here, the <> condition cannot utilize an index,
and this query will invoke a full-table scan. In sum, there are many
query conditions that can invalidate the index, and you must always
explain the SQL to see the execution plan to ensure that your query is
using the expected indexes.
Oracle 9i and Index Usage for Skewed Columns
A new feature in Oracle9i allows the CBO
to change execution plans even when optimizer plan stability is used.
This is called “peeking” and allows the CBO to change execution plans
when the value of a bind variable would cause a significant change to
the execution plan for the SQL.
When using cursor sharing, the CBO changes any
literal values in the SQL to bind variables. In Oracle9i, the
CBO “peeks” at the values of user-defined bind variables on the first
invocation of a cursor. This lets the optimizer determine the
selectivity of the where clause operator and change the
execution plan whenever the south value appears in the SQL.
This enhancement greatly improves the
performance of cursor sharing when a bind variable is used against a
highly skewed column.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.