Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: histograms, buckets, and bind variables

Re: histograms, buckets, and bind variables

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 17 Nov 2004 12:00:42 -0700
Message-ID: <419B9FDA.2010607@centrexcc.com>

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
Received on Wed Nov 17 2004 - 13:05:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US