Oracle-L: Gathering statistics on function-based index

From: elain he <elainhe_at_hotmail.com>
Date: Mon, 02 Jun 2003 05:45:04 -0800
Message-ID: <F001.005A7FEF.20030602054504@fatcity.com>

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

Received on Mon Jun 02 2003 - 08:45:04 CDT