Re: Creating unique index on huge table.

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


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