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:[EMAIL PROTECTED]
-----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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gogala, Mladen
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).
