Bad query plan on bitmapped index table

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 Fri Feb 22 2002 - 08:48:28 CST