DBMS_STATS and partitioned tables with Bitmap indexes

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Wed, 25 Oct 2006 01:44:46 GMT
Message-ID: <ikz%g.23585$7I1.2474@newssvr27.news.prodigy.net>

We're in the process of switching old code over from using ANALYZE to the DBMS_STATS and have run into something we can't figure out.

We have a large partitioned table of over 72 million rows and 10 partitions. When we run DBMS_STATS on the table with bitmap indexes the DBMS_STATS will take up to 4 hours to complete. However, if we drop the bitmap indexes and replace them with normal b-tree indexes the DBMS_STATS will only take about 20 minutes.

When the DBMS_STATS is run on an equally large, not non-partitioned, table with bitmap indexes the DBMS_STATS will complete in roughly the same time as the partitioned table without bitmap indexes.

Is this normal behavior for the DBMS_STATS? Is there a special parameter or something we have to use for the bitmap indexes on large partitioned tables?

We're running Oracle 10g R2 version 2.02. Received on Tue Oct 24 2006 - 20:44:46 CDT