Thank you so much to all of you for excellent advises.
Vadim
On Sat, Sep 24, 2016 at 2:41 PM, Mladen Gogala <gogala.mladen@xxxxxxxxx>
wrote:
Thanks for the clarification Jonathan.
Regards
On 09/24/2016 05:27 AM, Jonathan Lewis wrote:
Mladen,
You seem to have read far more out of my statement than I put into it.
(i) I didn't say ANYTHING about indexes, my comment was about uniqueness.
(ii) I didn't say ANYTHING about multiple columns
(iii) My comment was about the possible requirement for a histogram on a
(declared) unique single column
Your question about histograms and underlying columns suggests that you
think Oracle might have a mechanism for creating histograms on the columns
defining an index as the index is created. This is not the case. The only
stats Oracle generates as it creates the index are the statistic you find
in views like user_indexes, viz: blevel, leaf_blocks, distinct_keys,
clustering_factor, num_rows; and these can all be derived by keeping track
of running totals.
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 07:49
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Creating unique index on huge table.
Hi Jonathan,
Histograms are created for columns, not for indexes. So if there is a
unique compound index, comprised of several columns, the histograms may
still be useful. I completely agree with you. Did I understand you
correctly that Oracle will create histograms for the underlying columns, if
needed? Thanks, this is good to know. I have never checked.
Regards
On 09/24/2016 02:16 AM, Jonathan Lewis 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?
--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
