Re: Bad query plan on bitmapped index table

try:
select
from
where a.cat_cd=1 or a.cat_cd=2 or a.cat_cd =3 and a.cat_id = b.cat_id

Keith Brunsting wrote:

> We are building a data warehouse and are using bitmap indexes on some
> of our bigger tables. It seems like some of the easy queries are not
> being optimized correctly by oracle. For example, we have 2 tables, A
> & B. A is a dimension table with 100,000 rows and B is a fact table
> with 30 million rows. We have a bitmap index on the foreign key in
> the fact table.
>
> SELECT COUNT(*)
> FROM A
> ,B
> WHERE A.cat_cd IN (1,2,3)
> AND A.cat_id = B.cat_id
>
> Oracle ends up doing a hash join in this example and is hitting the
> fact table first. If I force a nested_loop using a hint I can get it
> to hit the dimension table first. It seems for many of the simple
> queries I have to end up using hints.
>
> I have also had problems with group by's in these type of queries but
> I think other people might have that problem too.
Received on Sat Feb 23 2002 - 00:04:41 CST