Re: bitmap conversion to rowids operation with btree indexes?

It isn't really appropriate to say that
'the driving table selection' may be causing a problem. Oracle does not choose a "driving table" - it only chooses the first join order, and then starts permuting tables from there. Every table (in principle) could be considered as a candidate driving table. The table that ends up as "the driving table" is the one that is in that position when the access mechanisms and
selectivities along the join order produce a minimum cost - in other words, the predicates and selectivities dictated the join order, and not vice versa. (Unless you use the ORDERED or LEADING hints, of course)

One of the reasons why bitmap conversions can be used in appropriately is that the arithmetic involved seems to have a strategy error that can produce a cost that is much lower than it should be. From memory I think the optimizer also forgets to factor in a couple of the costs that ought to be relevant. I think the only heuristic that is involved is the one that says - "if it's logically correct, try it".

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

• Original Message ----- From: "Riyaj Shamsudeen" <rshamsud_at_jcpenney.com> To: <Joze.Senegacnik_at_snt.si>; <oracle-l_at_freelists.org> Sent: Wednesday, January 12, 2005 3:47 PM Subject: RE: bitmap conversion to rowids operation with btree indexes?

Thanks Jose...
*Looks* like just not bitmap conversion is causing this, but due to possible incorrect predicate selection and/or driving table selection..Driving table is POLICE in the first (inefficient ) explain plan and it is object PK in the second case (efficient one). Driving table selection may be driving the predicate selection to access the PK object.

Not sure whether _b_tree_bitmap_plans is purely statistics based or a mix of heuristics & statistics. It might be interesting to see why optimizer chose this inefficient access paths.

--
http://www.freelists.org/webpage/oracle-l