Re: histograms, buckets, and bind variables

ryan_gaffuri_at_comcast.net 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
--
http://www.freelists.org/webpage/oracle-l