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_at_centrexcc.com> wrote:
>
> 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
>
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_at_yahoo.com
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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 18 2004 - 07:06:11 CST
