Re: Creating unique index on huge table.

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 24 Sep 2016 02:49:04 -0400

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.

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".

Jonathan Lewis
*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

Other related posts: