Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 
 

Statistics and function-based indexes

Oracle Database Tips by Donald BurlesonJuly 11, 2015



A common issue with Oracle DBAs and developers is "Why is Oracle not using my function-based index"?  Also see these important notes on why is my index not being used?

Because a function-based index applies a function to a data column, special steps must be taken for the optimizer to be able to utilize a function-based index.  The Oracle documentation notes:

"You should analyze the table after creating a function-based index, to allow Oracle to collect column statistics equivalent information for the expression.

Optionally, you can collect histograms for the index expressions by specifying for all hidden columns size number_of_buckets in the METHOD_OPT argument to the DBMS_STATS procedures."

Here are some methods for analyzing a function-based index, but beware that they may not work on all releases of Oracle for reasoned noted below:

EXEC DBMS_STATS.gather_index_stats('OWNER', 'CASE_INDEX');

exec dbms_stats.gather_table_stats(
   ownname=>null,
   tabname=> 'MYTAB',
   estimate_percent=>null,
   cascade=>true,
   method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1′
);

exec dbms_stats.gather_table_stats(
ownname => 'OWNER',
tabname => 'MYTAB',
cascade => TRUE);

In Oracle 11g and beyond, Oracle recommends analyzing "extended" statistics on function-based indexes:

 begin
   dbms_stats.gather_table_stats (
      ownname    => 'SCOTT',
      tabname    => 'EMP',
      method_opt => 'for all columns size skewonly for columns (nvl(ename,o))'  
);
end;

See my notes on what happens when an FBI gets ignored.



Oracle guru Guy Harrison also offers this advice for 11g statistics collection on function-based index columns.  

In 11g, I think there are two other ways to get statistics collected for indexed expressions:

1) Collect extended statistics directly on the expression. So for instance, if we had a function SALES_CATEGORY, we might do this:

DBMS_STATS.gather_table_stats
   (ownname => USER,

    tabname => 'SALES',
    method_opt => 'FOR ALL COLUMNS FOR COLUMNS
   (sale_category(amount_sold))' );

2) Create a virtual column on the expression, then index that column. So for the same example as above we might create the following virtual column, then index the column and collect stats as usual:

ALTER TABLE
   SALES
ADD
   sales_category
GENERATED ALWAYS AS
   (sale_category(amount_sold));

I think I like the first method better, because the statistics will still exist even if the index is dropped and - unlike the second approach - it doesn't change the logical structure of the table.


There are reports of function-based indexes not being used by the CBO. some due to bugs and others due to issues with the dbms_stats analysis routine. 

One important bug (bug 2782919) is titled  "GATHER_TABLE_STATS FAILS ON TABLE WITH WITH FUNCTION-BASED INDEX". 

Also, Oracle developer Flavio Casetta notes special techniques for analyzing statistics on a function-based index.

After working for years on 10g, I had almost forgotten that on 9iR2 and earlier, the cost based optimizer would not work properly [on a function based index]until the statistics were collected on the index and on the underlying table.

exec dbms_stats.gather_table_stats(
   ownname => 'TEST',
   tabname => 'TEST_FIDX_TAB',
   cascade => TRUE);


After gathering the statistics, the function based index was picked up by the optimizer *without* having to specify any hint. Then I decided to repeat this exercise on a 10gR2 instance where I could verify that the optimizer was instantly picking up the function-based index because oracle 10g automatically gathers the statistics for objects having stale or missing statistics.  Again, no need of adding optimizer hints.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.