Re: Temporary space needed to create a constraint
- From: Peter Hitchman <pjhoraclel@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 11 Jul 2012 11:13:44 +0100
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@xxxxxxxx> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
