Re: Creating unique index on huge table.
Date: Thu, 22 Sep 2016 17:32:17 -0600
Message-ID: <c495222c-36a4-eb8e-aa2f-e5a01a2a38fa_at_evdbt.com>
Vadim,
High-level description of what worked to rebuild it...
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 23 2016 - 01:32:17 CEST