Re: histograms, buckets, and bind variables

From: Connor McDonald <hamcdc@xxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Thu, 18 Nov 2004 12:52:33 +0000 (GMT)

Simple demo for you...

SQL> create table t
  2  as select rownum x, trunc(dbms_random.value(1,100)) y
  3  from all_Objects;

Table created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select column_name, density
  2  from user_tab_columns
  3  where table_name = 'T';

COLUMN_NAME                       DENSITY
------------------------------ ----------
X                              .000031763
Y                               .01010101

SQL> analyze table t compute statistics for table for all columns;

Table analyzed.

SQL> select column_name, density
  2  from user_tab_columns
  3  where table_name = 'T';

COLUMN_NAME                       DENSITY
------------------------------ ----------
X                              .000031763
Y                              .006666667

different density = possibly different plans....

hth
connor

 --- Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote: 
> 
> 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
>  

=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, 
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------


                
___________________________________________________________ 
Win a castle for NYE with your mates and Yahoo! Messenger 
http://uk.messenger.yahoo.com
--
//www.freelists.org/webpage/oracle-l

References:
- Re: histograms, buckets, and bind variables
  - From: Wolfgang Breitling

Re: histograms, buckets, and bind variables

Other related posts: