BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter