Re: Gathering statistics on function-based index

Have you tried methods 3 or preferrably method 4:

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: elainhe_at_hotmail.com
>
>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: ListGuru_at_fatcity.com (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: breitliw_at_centrexcc.com

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: ListGuru_at_fatcity.com (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).