Jonathan,
Any chance you could clarify your statement? My initial assumption would
be that as column is unique a histogram WOULD be unnecessary.
Thinking about it I guess that a histogram on unique column might be useful
for CBO to know whether a particular value exists for the column rather
than how many values (because that will always be one).
Although I imagine in this case, as the number of unique values is likely
to be huge, it would be height-balanced histogram and hence the information
available would likely be not particularly useful.
Or are you saying that if it is a multi-column index, that although the
combination of columns in the index is unique, the individual columns will
not be?
Regards
Patrick
On 24 September 2016 at 14:16, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
Just for the record - uniqueness doesn't automatically mean that a
histogram will be unnecessary; moreover if a histogram on a unique column
is necessary Oracle will detect it build it during a default
"gather_table_stats".
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Mladen Gogala [gogala.mladen@xxxxxxxxx]
*Sent:* 24 September 2016 02:55
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Creating unique index on huge table.
And the data distribution for the unique index is trivial and does not
need statistics. Hopefully, you didn't create histograms on the unique
index?