RE: Creating unique index on huge table.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 24 Sep 2016 09:27:44 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9015033FD0D_at_exmbx05.thus.corp>

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
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Mladen Gogala [gogala.mladen_at_gmail.com] Sent: 24 September 2016 07:49
To: oracle-l_at_freelists.org
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
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Mladen Gogala [gogala.mladen_at_gmail.com<mailto:gogala.mladen_at_gmail.com>] Sent: 24 September 2016 02:55
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Sep 24 2016 - 11:27:44 CEST