PV: bitmap indexes used on GROUP fuctions?

From: Mark Baker <mark_at_mrb-basys.demon.co.uk>
Date: Thu, 3 Sep 1998 23:54:30 +0100
Message-ID: <904863316.11817.0.nnrp-07.c1ed77a9@news.demon.co.uk>

We're running on AIX 4.2 Oracle Server 7.3.4.2.

When I execute a simple group function query on a partioned view of 24 identical partitions (24 months of data) I see the execution plan using a bitmap index (1 of 7 identical on each partition) which results in a slow execution.

We use cost based optimizer and the stats are all computed and up to date.

The execution plan for the following simple sql statement shows the use of a (randomly chosen?) bitmap index to count rows within each partition:

Select count(1) from pv_table_name;

And it soesn't get much easier than that for the parser!

If I add a /*+ FULL (pv_table_name) */ hint the result is considerably faster!

The problem being that ad-hoc queries from end-users are not going to have hints in, nor are those generated by the 'Business Objects' product widely used by them.

Also - the same thing happens with MAX(colname) as COUNT(colname) and others.

Anyone thrashed this one out?

Regards,
Mark

PS. 'twas the same on v7.3.2. Received on Thu Sep 03 1998 - 17:54:30 CDT