Re: bitmap conversion to rowids operation with btree indexes?

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <rshamsud@xxxxxxxxxxxx>, <Joze.Senegacnik@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 14 Jan 2005 22:29:23 -0000

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@xxxxxxxxxxxx>
To: <Joze.Senegacnik@xxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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.



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

Other related posts: