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).
