Re: Creating unique index on huge table.

On 09/23/2016 05:14 PM, Tim Gorman wrote:
> You'll have to substantiate that assertion; COMPUTE STATISTICS incurs
> negligible additional processing and thus resulted in no impact on our
> rebuild. COMPUTE STATISTICS is a "free" stats-gather, a win-win.
>
> Think it through: index creation/rebuild is already scanning all the
> relevant data values from the entire table, so to what additional
> "expensive" operations are you referring? Calculating sums and averages?
> Creating histograms?

Yes to all of that. Calculating sums and averages on a large data set is expensive.

> Updating data dictionary tables at the conclusion of the operation?

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.

>
> Please note too that the original poster stated that he was dealing
> with a unique index, not with a primary key constraint, and of course
> there is no such thing as a "primary key index" in Oracle.

It appears I have been unclear here. By the "primary key index", I meant the index used to enforce the primary key. Those are usually unique and defined on NOT NULL columns. As you have said, it doesn't have to be that way, but it usually is. 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?

> Due to the fact that primary key constraints can be enforced by
> non-unique indexes in some scenarios, this is an important distinction.

Yes it is. Was your monster index a non-unique index?

>
>
>
>
> On 9/23/16 14:32, Mladen Gogala wrote:
>> Good thing for the original poster is that he is creating the primary
>> key index, so he doesn't need to compute statistics. Computing
>> statistics is very expensive and it took significant part of the
>> total time for your procedure.
>> Regards
>>
>> On 09/22/2016 07:32 PM, Tim Gorman wrote:
>>> Vadim,
>>>
>>> Several years ago, I accidentally dropped a unique index on a 55TB
>>> table, which represented about 12TB of index. There are several on
>>> this list who know exactly the circumstances, and one person on this
>>> list who was the recipient of the tearful phone call I made when I
>>> realized what I had done. :)
>>>
>>> High-level description of what worked to rebuild it...
>>>
>>> 1. Run "create partitioned index ... unusable" to create the
>>> partitioned index with all partitions empty.
>>> 2. Create a shell-script to run NN SQL*Plus processes
>>> simultaneously, where "NN" is a number of your choice, each
>>> process doing the following...
>>> * alter index <index-name> partition <partition-name> parallel
>>> <degree> nologging compute statistics
>>>
>>>
>>> We ordered the SQL*Plus calls inside the shell-script so that the
>>> partitions for the most-recent partitions (i.e. the table was
>>> partitioned by a DATE column) were populated first, and then let the
>>> builds progress back in time. Depending on the application, you can
>>> be doing some or all of the normal activities on the table. Our
>>> assumption (which proved correct) was that all DML occurs against
>>> the newest partitions, so those were the partitions that needed to
>>> be made "usable" first.
>>>
>>> This approach won't eliminate downtime or performance problems, but
>>> it will likely minimize them.
>>>
>>> I hope this makes sense?
>>>
>>> Thanks!
>>>
>>> -Tim
>>>
>>>
>>>
>>> On 9/22/16 15:43, Vadim Keylis wrote:
>>>> I am working on adding replication using third party
>>>> tool(dbvisit) to our databases.
>>>> I have couple 5T highly transactional partition tables. I need to
>>>> add a partition unique index to these tables without causing any
>>>> outage or incurring performance hit. Will greatly appreciate
>>>> suggestions on the best approach to do it.
>>>>
>>>> Thanks so much,
>>>> Vadim
>>>
>>
>>
>> --
>> Mladen Gogala
>> Oracle DBA
>> Tel: (347) 321-1217
>

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


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