Oracle-L: RE: Gathering statistics on function-based index

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Mon, 02 Jun 2003 07:19:42 -0800
Message-ID: <F001.005A81C9.20030602071942@fatcity.com>

Does this look familiar?

Bug No. 2782919
Filed 03-FEB-2003 Updated 20-MAY-2003
Product Oracle Server - Enterprise Edition V7 Product Version 9.2.0.2.0 Platform Sun SPARC Solaris (64-BIT) Platform Version 2.8 Database Version 9.2.0.2.0 Affects Platforms Generic Priority Severe Loss of Service Status Q/A To Development Base Bug N/A Fixed in Product Version 10.0.0

Problem statement:

ORA-904:GATHER_TABLE_STATS FAILS ON TABLE WITH WIH FUNCTION-BASED INDEX 
----------------------------------------------------------------------------
----

02/03/03 10:59 am *** TAR: ---- 2879345.999 . PROBLEM: -------- Using DBMS_STATS.GATHER_TABLE_STATS on a table with greater than 1 million rows gives ORA-904 error. . SQL> select count(*) from TEST_TABLE2; COUNT(*) ----------------- 1018073 . SQL> BEGIN 2 dbms_stats.gather_table_stats( 3 ownname => 'SCOTT', 4 tabname => 'TEST_TABLE2', 5 cascade =>TRUE, 6 block_sample => TRUE, 7 degree => 8, 8 estimate_percent => 25); 9 END; 10 / BEGIN

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:mgogala_at_oxhp.com

-----Original Message-----
Sent: Monday, June 02, 2003 9:45 AM
To: Multiple recipients of list ORACLE-L

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_k eys
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: MGogala_at_oxhp.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 - 10:19:42 CDT