RE: Creating unique index on huge table.
Date: Fri, 23 Sep 2016 05:41:01 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9015033FA7F_at_exmbx05.thus.corp>
Does the index have to be unique, or can it be non-unique with a unique constraint behind it ?
What you way partitioned index do you mean it has to be locally partitioned or could it be globally partitioned.
The problem with a unique index is that you can't create it as an unusable index and then rebuild it in steps - the table cannot be modified while the unique index is unusable. (And I have to say, what makes you think that the 5TB data hasn't got any duplicates ? What are you going to do if you do have duplicates ?)
Just in case you've overlooked it - if you want locally partitioned unique index (or unique constraint) the unique key must include the partition key).
If you want virtually no downtime (but a lot of slow time) you can:
Set the system to skip unusable indexes
At 5TB you might want to read the following posting I did some time ago, because the validation could be massively expensive (unless Oracle has changed the code) - so you'll want to do some testing to check what method Oracle uses and how you can optimise it: https://jonathanlewis.wordpress.com/2012/07/12/pk-problem/
Note that my example is about a primary key - which includes nulls; uniqueness by itself may display some differences.
Regards
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,
Create a non-unique partitioned index unusable
Rebuild each partition in turn - perhaps several at once, as described by Tim.
Add a unique constraint constraint in the 'enable novalidate' state using the index you've created
Validate the constraint.
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Vadim Keylis [vkeylis2009_at_gmail.com]
Sent: 22 September 2016 22:43
To: oracle-l_at_freelists.org
Subject: Creating unique index on huge table.
Vadim
[https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif]
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 23 2016 - 07:41:01 CEST
