Re: Bad query plan on bitmapped index table

Funny, I had the same problem recently and used the usual lazy way of just adding a first rows hint.

I would almost make it the default.

Indeed, I once set the init.ora parameter optimizer_mode = first_rows (or is it optimizer_goal?).
Performance was great in my system, but it screwed up any processing of system tables e.g. export used to take forever.

Alternatively, you might look at the optimizer_% start-up parameters which can swing the balance towards indexed searches (though I speak only on the basis of theory about this).

Keith Brunsting <kbrunsting_at_email.com> 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 - 14:24:57 CST