Re: Gathering statistics on function-based index

Tue, 03 Jun 2003 04:40:34 -0700 

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.

-----------
Index used
-----------
exec print_table ('select * from user_tables where table_name=''AC_FORWARD_DEST''');
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
-----------------


--------------
Index Not Used
--------------
exec print_table ('select * from user_tables where table_name=''AC_FORWARD_DEST''');
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 <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: [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).


_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail

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

Reply via email to