Re: Temporary space needed to create a constraint

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 ?

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• 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

Hi
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.

Regards
Pete

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jul 11 2012 - 06:39:28 CDT