Re: Bad query plan on bitmapped index table

I am by no means experienced with Oracle, but one relevant question, would be how many records will this query return. From my experiences with another DBMS's which shall remain nameless, if you are getting more than 1-4 percent of the records out of the table, the index is fairly useless.

But maybe this is not true with Oracle.

Will

kbrunsting_at_email.com (Keith Brunsting) wrote in message news:<2ff9103c.0202220648.14490b08_at_posting.google.com>...
> 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 - 13:39:06 CST