Re: Temporary space needed to create a constraint
Date: Wed, 11 Jul 2012 12:39:28 +0100
What's your code to define the constraint and the existing index, and what's the execution plan you're seeing in the trace file ? And is the original table a simple heap table, or is it partitioned, and if partitioned was the index local or global ?
Create non-unique index;
add unique (or pk) constraint;
Shouldn't need to do anything other than walk the index - unless there's something funny going on with an attempt to do this online.
Have you tried
add constraint enabled novalidate ?
alter constraint validate ?
Author: Oracle Core (Apress 2011)
- Original Message ----- From: "Peter Hitchman" <pjhoraclel_at_gmail.com> To: "oracle-l" <oracle-l_at_freelists.org> Sent: Wednesday, July 11, 2012 11:13 AM Subject: Re: Temporary space needed to create a constraint
Thanks for the comments. I forgot to say I think that the index I created is non-unique. After some more tracing I saw that Mark got it right, the SQL being run to ensure there are no duplicates is doing a hash join. I compared this to what happens when a unique index is created and the 10046 trace just showed a full table scan, where as the constraint creation runs some SQL that groups by the primary columns and has the predicate "having count(1) >1". I could not see in the trace for creating a unique index any SQL being run to ensure uniqueness, only the STAT line "SORT CREATE INDEX".
So now I am trying with a large HASH_AREA_SIZE and parallel slaves.
http://www.freelists.org/webpage/oracle-l Received on Wed Jul 11 2012 - 06:39:28 CDT