| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Gathering statistics on function-based index
Wolfgang,
Thanks for your respond.
I tried gathering stats on the function based index using
- analyze index ACFD_INDX1 compute statistics;
- exec
dbms_stats.gather_table_stats(ownname=>'ACPO',tabname=>'AC_FORWARD_DEST',cascade=>TRUE);
- exec dbms_stats.gather_index_stats('ACPO','ACFD_INDX1');
but the Optimizer still does a full table scan. The only way that I can get
the optimizer to use the index is when I
- create unique index ACFD_INDX1 on AC_FORWARD_DEST (upper(dtname)) compute
statistics;
Puzzling...
Without using the index, the query returns in about 6 secs vs a few ms with the index.
Here are the output from user_indexes and user_tables when the index is being utilized and not utilized. As you will notice, the results are identical.
TABLE_NAME : AC_FORWARD_DEST TABLESPACE_NAME : AFD_D CLUSTER_NAME : IOT_NAME : PCT_FREE : 10 PCT_USED : 40 INI_TRANS : 1 MAX_TRANS : 255 INITIAL_EXTENT : 1048576 NEXT_EXTENT : 1048576 MIN_EXTENTS : 1 MAX_EXTENTS : 1024 PCT_INCREASE : 0 FREELISTS : 1 FREELIST_GROUPS : 1 LOGGING : YES BACKED_UP : N NUM_ROWS : 914532 BLOCKS : 13066 EMPTY_BLOCKS : 89 AVG_SPACE : 426 CHAIN_CNT : 0 AVG_ROW_LEN : 69 AVG_SPACE_FREELIST_BLOCKS : 2734 NUM_FREELIST_BLOCKS : 2 DEGREE : 1 INSTANCES : 1 CACHE : N TABLE_LOCK : ENABLED SAMPLE_SIZE : 914532 LAST_ANALYZED : 02-jun-2003 08:56:49 PARTITIONED : NO IOT_TYPE : TEMPORARY : N SECONDARY : N NESTED : NO BUFFER_POOL : DEFAULT ROW_MOVEMENT : DISABLED GLOBAL_STATS : YES USER_STATS : NO DURATION : SKIP_CORRUPT : DISABLED MONITORING : NO CLUSTER_OWNER : -----------------
exec print_table ('select * from user_indexes where
index_name=''ACFD_INDX1'''); INDEX_NAME : ACFD_INDX1 INDEX_TYPE : FUNCTION-BASED NORMAL TABLE_OWNER : ACPO TABLE_NAME : AC_FORWARD_DEST TABLE_TYPE : TABLE UNIQUENESS : UNIQUE COMPRESSION : DISABLED PREFIX_LENGTH : TABLESPACE_NAME : AFD_X INI_TRANS : 2 MAX_TRANS : 255 INITIAL_EXTENT : 1048576 NEXT_EXTENT : 1048576 MIN_EXTENTS : 1 MAX_EXTENTS : 1024 PCT_INCREASE : 0 PCT_THRESHOLD : INCLUDE_COLUMN : FREELISTS : 1 FREELIST_GROUPS : 1 PCT_FREE : 10 LOGGING : YES BLEVEL : 2 LEAF_BLOCKS : 9050 DISTINCT_KEYS : 914532 AVG_LEAF_BLOCKS_PER_KEY : 1 AVG_DATA_BLOCKS_PER_KEY : 1 CLUSTERING_FACTOR : 807743 STATUS : VALID NUM_ROWS : 914532 SAMPLE_SIZE : 914532 LAST_ANALYZED : 02-jun-2003 08:59:43 DEGREE : 1 INSTANCES : 1 PARTITIONED : NO TEMPORARY : N GENERATED : N SECONDARY : N BUFFER_POOL : DEFAULT USER_STATS : NO DURATION : PCT_DIRECT_ACCESS : ITYP_OWNER : ITYP_NAME : PARAMETERS : GLOBAL_STATS : NO DOMIDX_STATUS : DOMIDX_OPSTATUS : FUNCIDX_STATUS : ENABLED -----------------
TABLE_NAME : AC_FORWARD_DEST TABLESPACE_NAME : AFD_D CLUSTER_NAME : IOT_NAME : PCT_FREE : 10 PCT_USED : 40 INI_TRANS : 1 MAX_TRANS : 255 INITIAL_EXTENT : 1048576 NEXT_EXTENT : 1048576 MIN_EXTENTS : 1 MAX_EXTENTS : 1024 PCT_INCREASE : 0 FREELISTS : 1 FREELIST_GROUPS : 1 LOGGING : YES BACKED_UP : N NUM_ROWS : 914532 BLOCKS : 13066 EMPTY_BLOCKS : 89 AVG_SPACE : 426 CHAIN_CNT : 0 AVG_ROW_LEN : 69 AVG_SPACE_FREELIST_BLOCKS : 2734 NUM_FREELIST_BLOCKS : 2 DEGREE : 1 INSTANCES : 1 CACHE : N TABLE_LOCK : ENABLED SAMPLE_SIZE : 914532 LAST_ANALYZED : 02-jun-2003 12:07:38 PARTITIONED : NO IOT_TYPE : TEMPORARY : N SECONDARY : N NESTED : NO BUFFER_POOL : DEFAULT ROW_MOVEMENT : DISABLED GLOBAL_STATS : YES USER_STATS : NO DURATION : SKIP_CORRUPT : DISABLED MONITORING : NO CLUSTER_OWNER : -----------------
exec print_table ('select * from user_indexes where
index_name=''ACFD_INDX1'''); INDEX_NAME : ACFD_INDX1 INDEX_TYPE : FUNCTION-BASED NORMAL TABLE_OWNER : ACPO TABLE_NAME : AC_FORWARD_DEST TABLE_TYPE : TABLE UNIQUENESS : UNIQUE COMPRESSION : DISABLED PREFIX_LENGTH : TABLESPACE_NAME : AFD_X INI_TRANS : 2 MAX_TRANS : 255 INITIAL_EXTENT : 1048576 NEXT_EXTENT : 1048576 MIN_EXTENTS : 1 MAX_EXTENTS : 1024 PCT_INCREASE : 0 PCT_THRESHOLD : INCLUDE_COLUMN : FREELISTS : 1 FREELIST_GROUPS : 1 PCT_FREE : 10 LOGGING : YES BLEVEL : 2 LEAF_BLOCKS : 9050 DISTINCT_KEYS : 914532 AVG_LEAF_BLOCKS_PER_KEY : 1 AVG_DATA_BLOCKS_PER_KEY : 1 CLUSTERING_FACTOR : 807743 STATUS : VALID NUM_ROWS : 914532 SAMPLE_SIZE : 914532 LAST_ANALYZED : 02-jun-2003 12:08:45 DEGREE : 1 INSTANCES : 1 PARTITIONED : NO TEMPORARY : N GENERATED : N SECONDARY : N BUFFER_POOL : DEFAULT USER_STATS : NO DURATION : PCT_DIRECT_ACCESS : ITYP_OWNER : ITYP_NAME : PARAMETERS : GLOBAL_STATS : NO DOMIDX_STATUS : DOMIDX_OPSTATUS : FUNCIDX_STATUS : ENABLED -----------------
>From: Wolfgang Breitling <breitliw_at_centrexcc.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: Gathering statistics on function-based index
>Date: Mon, 02 Jun 2003 07:30:07 -0800
>
>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).
>
-- 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 - 13:49:52 CDT
 |
 |