There is no correlation between uniqueness and
"skewness". For example, you might have unique
values: 1,2,3,4,5,...,9999, and then 100000000000000
Obviously that last one is an skew value. If you have
predicates on this column along the lines of:
where col > ...
where col < ...
where col between ... and ...
then a histogram might be useful, otherwise the
optimizer assumes an even distribution of values
between 1 and 100000000000000
hth
connor
- Murali Menon <mjgnmenon_at_yahoo.com> wrote: >
> Uniqueness indicates that there are less skewed
> values correct! Under what circumstances would a
> skewed column value for a unique column exist.
>
> Another question, what about histograms on indexes
> itself? If histograms are generated on columns with
> skewed values, does it matter when the indexes are
> generated, before or after? Do indexes on these
> columns need to be rebuilt.
>
> Thanks
>
>
>
>
>
> Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> It's worth mentioning a frequently overlooked
> point that even if a column has a unique
> constraint (whether or not declared and/or
> supported by an index) then if it has a
> skewed pattern of values the optimiser
> can use a histogram to optimise a queries
> that target a range.
>
> (NB The grammar suggests that the word
> "not" was intended as the last word of the
> first line of (b) below).
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon one-day tutorials:
> Cost Based Optimisation
> Trouble-shooting and Tuning
> Indexing Strategies
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> ____UK_______March 19th
> ____USA_(FL)_May 2nd
>
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L
> Date: 08 February 2003 10:44
>
>
> >
> >b) The number of distinct/duplicate values should
> >really be relevant - its whether you will regularly
> >need to probe a table using column values that are
> >skewed in such a way as to have the optimizer make
> >poor assumptions about their distribution.
> >
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Connor McDonald
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"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"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Feb 11 2003 - 18:54:00 CST
