Re: Temporary space needed to create a constraint
Date: Wed, 11 Jul 2012 11:13:44 +0100
Message-ID: <CAPMSPxN+D+o8rpoQeafjr1oyz5_=M3vY8Fu-jLKcRGYzr+VRCA_at_mail.gmail.com>
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
On Wed, Jul 11, 2012 at 12:08 AM, Mark W. Farnham <mwf_at_rsiz.com> 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
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 11 2012 - 05:13:44 CDT