Single column unique.
Not all queries are for single row by primary key. Queries for ranges of
primary key need to estimate the number of rows in the range; histograms can
describe gaps in ranges, not just frequency of values. I'll post a blog note on
Monday.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Patrick Jolliffe [jolliffe@xxxxxxxxx]
Sent: 24 September 2016 08:21
To: Jonathan Lewis
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Creating unique index on huge table.
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<mailto: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<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Mladen Gogala [gogala.mladen@xxxxxxxxx<mailto:gogala.mladen@xxxxxxxxx>]
Sent: 24 September 2016 02:55
To: oracle-l@xxxxxxxxxxxxx<mailto: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?
