Re: Creating unique index on huge table.

Absolutely correct, thanks for catching that.

I don't recall using the ONLINE clause, but it's been a few years.

On 9/22/16 21:50, Chitale, Hemant K wrote:
>
> I think you missed the REBUILD in the
>
> alter index <index-name> partition <partition-name> parallel <degree>
> nologging compute statistics
>
> What is scary is that this is a UNIQUE Index.
>
> Hemant K Chitale
>
> *From:*oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Tim Gorman
> *Sent:* Friday, September 23, 2016 7:32 AM
> *To:* vkeylis2009_at_gmail.com; oracle-l_at_freelists.org
> *Subject:* Re: Creating unique index on huge table.
>
> 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
>
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all
> copies and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered
> Bank and their subsidiaries at
> https://www.sc.com/en/incorporation-details.html

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