Re: Creating unique index on huge table.

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

On 09/24/2016 11:11 AM, Tim Gorman wrote:

On 9/24/16 00:44, Mladen Gogala wrote:

On 09/24/2016 12:40 AM, Tim Gorman wrote:

I have rebuilt a large index on a database as a DBA several times and I do remember that the difference between "compute statistics" and without "compute statistics" was several minutes. In the large scheme of things, that may not be important. However, my index was nowhere near as large as yours. The index was < 1 TB, around 800 GB.

COMPUTE STATISTICS was deprecated in Oracle10gR2; excerpted from the 10.2 documentation <>...

    /*COMPUTE STATISTICS* //In earlier releases, you could use this
    clause to start or stop the collection of statistics on an
    index. This clause has been deprecated. Oracle Database now
    automatically collects statistics during index creation and
    rebuild. This clause is supported for backward compatibility and
    will not cause errors.

I was doing the same thing as you were: partition rebuild. If the "compute statistics" was obsolete for me, then it would be equally obsolete for you. I am no longer a DBA, and I don't have a DB that I could use for testing. However, computing statistics as a part of the index rebuild doesn't look cheap to me, especially not with very large data sets. Gathering statistics doesn't look cheap to me.

Do not try to equate what we have each said in this thread. I have not promoted speculation as fact, as you have. Softening assertions into opinion, as you now appear to be doing, would have been a more credible way to enter the thread.
It appears that I wasn't clear enough. I was just asking about the "compute statistics" part. I checked the 12.1 documentation and I didn't find the "compute statistics" option. So, it appears that both of us have used unnecessary options. Oracle does compute statistics during the index creation/rebuild and there is nothing to be done about it.

And FWIW, calculating counts, sums, and averages on already-fetched data is computationally trivial. Proof is in the deprecation cited above.

I'm done with this branch of the thread.

Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

Other related posts: