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:

select
   *
from
   student
where
   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 column:

select
   *
from
   student
where
   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:

select
   *
from
   student
where
   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:

 select
   *
from
   student
where
    student_level = 'UNDERGRAD'
and
    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.