I see Connor has explained how a unique column
can have a skewed data distribution. To answer
the second paragraph - you don't have to worry
about the timing of the histogram/index builds.
However, a though I've just had, it is possible
that when Oracle builds the histogram, it MAY
be able to
How do randomly inmserted keys result in an unbalanced B-TREE index? Please define
your use of the term unbalanced. Perhaps I understand the term differently.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED],edu
-Original Message-
Sent: Sunday, February 09, 2003
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
For DSS systems (especially where there a lots of ad-hoc queries), be liberal with
histograms. They can have a significant positive impact on the join-order chosen by
the CBO even when the columns don't look skewed.
There is additional cost in CPU, cache and system tablespace(especially if you
A unique index only says that all of the values therein are unique. There is no
need to insert the values in a particular order. That being the case the index
can become unbalanced requiring a rebuild.
Dick Goulet
Murali Menon [EMAIL PROTECTED] wrote on 2/9/03 12:43 pm:
Uniqueness indicates
My thoughts - others may differ:
a) Only have histograms where absolutely necessary.
If you've got large histograms on every table columns
thats a lot of stuff sucking up dictionary cache space
which will never get used. Similarly, it would
probably be more effort to parse (although I have not
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