Re: Temporary space needed to create a constraint

From: Peter Hitchman <>
Date: Wed, 11 Jul 2012 11:13:44 +0100
Message-ID: <>

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.


On Wed, Jul 11, 2012 at 12:08 AM, Mark W. Farnham <> wrote:
> I *thought* that was the next thing that was to be tried. I like the "knit"
> usage. I suppose it could have been inadvertently in parallel before, but it
> should be an ordered walk of the index with no two consecutive values
> matching. I suppose the CBO could come off the rails somehow and do a fast
> full index scan and then have to sort the results, but I would think we'd
> have seen that before now. Hilarious if it is doing a hash it does not have
> room for: Even if that was somehow calculated to be theoretically faster
> using faster i/o methods, not having enough room and going splat is always
> slower.
> mwf

Received on Wed Jul 11 2012 - 05:13:44 CDT

Original text of this message