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...
- Run "create partitioned index ... unusable" to create the
partitioned index with all partitions empty.
- 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
