Re: Creating unique index on huge table.

From: Tim Gorman <tim_at_evdbt.com>
Date: Sat, 24 Sep 2016 09:11:56 -0600
Message-ID: <052b6a41-d19b-2b13-4cda-ef70f7a448db_at_evdbt.com>

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 10.2.0.5 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 
>> <https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm>...
>>
>>     /*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.
> Regards

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.

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.

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

Received on Sat Sep 24 2016 - 17:11:56 CEST