Tim related the story in the August 2014 issue of the NoCOUG Journal.
http://www.nocoug.org/Journal/NoCOUG_Journal_201408.pdf. But he had not ;
explained how he recreated the 12 TB index.
Luckily somebody had the foresight to partition the 55 TB table on a date
column.
I remember that when I was a database administrator I was appalled at seeing a
5 GB table. That's GB not TB.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Tim Gorman <tim@xxxxxxxxx>
Sent: Thursday, September 22, 2016 4:32 PM
To: vkeylis2009@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
[https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif]
