DBMS_STATS and CBO

From: <Prasada.Gunda_at_hartfordlife.com>
Date: Fri, 08 Aug 2003 17:24:23 -0800
Message-ID: <F001.005C9B33.20030808172423@fatcity.com>

We have a table (monthly fact table) which has 24 partitions and partitioned by month. There is data in only 3 partitions. All the indexes are locally partitioned.

In dev db, I analyzed the table and indexes with analyze table..compute statistics. When I query the no. of rows group by month, it returns the query in couple of seconds and does the index scan(bitmap) on month column.

I created this table in production db and this time I used the dbms_stats to create the statistics (compute both on table and indexes). When I run this query, it does full table scan. The only way I could make it to use index scan by specifying hints.

All the parameters(init.ora) are exactly the same on both databases and it is 8.1.7.4. In prod db, I tried various combinations of optimizer_index_caching, optimizer_index_cost_adj to favor the index scan. There was no use.

Then, I did analyze table .. compute statistics on prod table but it was still doing the FTS. I did not delete the stats created by dbms_stats before using analyze table stmnt.

Finally, I deleted the stats generated by dbms_stats/analyze table before generating stats again with Analyze table <table> compute statistics. It is doing the index scan now.

I have no clue 1. why it does not use index when I generated the stats w/dbms_stats. 2. why I had to explicitly delete the stats before generating the stats again to make use of the index.

Thanks for your help in advance.

Thanks.

Best Regards,
Prasad
860 843 8377

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
  INET: Prasada.Gunda_at_hartfordlife.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 Fri Aug 08 2003 - 20:24:23 CDT