My Oracle Support Banner

DBMS_STATS slow for large Partitioned/Non Partition Table with Bitmap Indexes (Doc ID 1330418.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 9.2 to 10.2]
Information in this document applies to any platform.


DBMS_STATS very slow for large Partitioned/Non Partition table with Bitmap indexes;irrespective of Degree of parallelism.

Much time spent could be seen in the statement like below:

select /*+ gather_plan_statistics * / count(rep)
from (select /*+ leading(v1) use_nl(v2) */ count(v1.val) rep
from (select /*+ no_merge noparallel(t) noparallel_index(t)
cursor_sharing_exact dynamic_sampling(0) no_monitoring */
"Column from the table" val
from "Table where stats are gathered" t
where "Column from the table" is not null
group by "Column from the table"
having count("Column from the table") = 1 ) v1,
(select /*+ index(t2) */ "Column from the table" val
from "Table where stats are gathered" t2 ) v2
where v2.val = v1.val
group by v1.val
having count(v1.val) <= 2;


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.