Re: histograms, buckets, and bind variables

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: ryan_gaffuri@xxxxxxxxxxx
  • Date: Wed, 17 Nov 2004 12:00:42 -0700

ryan_gaffuri@xxxxxxxxxxx wrote:
> I can't remember where I read about bind variables and histograms. 
> anyone know who wrote this?

A lot of authors spread this, and other, myths.

>  
> also, how does oracle use histograms with bind variables if they need to 
> get a general case solution that is useful with multiple where clauses?

It depends on the version.

Prior to Oracle 9 the cbo used the column density to determine the 
selectivity of a column predicate. Collecting histograms affects the 
value of density and therefore the selectivity of the predicate, the 
cardinality estimate and ultimately the access path.

 From Oracle 9 on, the optimizer will use the bind variable value at the 
first parse to determine the predicate selectivity, cardinality estimate 
and access path, just as if it was coded as a literal. All other sql 
then share this access plan. That is clearly spelled out in the docs. 
Oracle assumes, and warns you, that plans are meant to be shared when 
you use bind variables.


-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2004