method 3: analyze index <index_name> compute statistics
method 4: exec dbms_stats.gather_index_stats(...)
What do you mean by "the output below is similar for both methods"? What are the differences? Can you use Tom Kyte's print_table procedure to list the contents of user_indexes for the index after each of the analyzes?
At 05:45 AM 6/2/2003 -0800, you wrote:
Hi,
Can someone shed some light on the differences of gathering statistics on a function-based index using the following two methods?
method 1: analyze table <table_name> compute statistics
vs
method 2: create unique index <index_name> on <table_name> (upper(columne_name)) compute statistics;
I could not get the CBO optimizer to use the function-based index if I were to gather statistics on my index using method 1. However, if I were to use method 2, the function-based index is used. Method 2 would require me to drop the index everytime I gather statistics on the index.
I tested this on 8.1.7.4 and 9.2.0.3.
Method 1: Execution Plan -------------------------------------- SELECT STATEMENT Cost = 3211 COUNT STOPKEY VIEW SORT ORDER BY STOPKEY TABLE ACCESS FULL AC_FORWARD_DEST
Method 2: Execution Plan -------------------------------------- SELECT STATEMENT Cost = 1068 COUNT STOPKEY VIEW TABLE ACCESS BY INDEX ROWID AC_FORWARD_DEST INDEX RANGE SCAN DESCENDING ACFD_INDX1
After analyzing the index using both method 1 and 2, the output below is similar for both methods:
select clustering_factor,avg_leaf_blocks_per_key,avg_data_blocks_per_key,distinct_keys from user_indexes where table_name='AC_FORWARD_DEST' and index_name='ACFD_INDX1'
/
CLUSTERING_FACTOR=80774
AVG_LEAF_BLOCKS_PER_KEY=1
AVG_DATA_BLOCKS_PER_KEY=1
DISTINCT_KEYS=914532
select num_rows, blocks from user_Tables where table_name='AC_FORWARD_DEST' / NUM_ROWS=914532 BLOCKS=13066
Thanks!
Elain
_________________________________________________________________ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: elain he INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
