RE: Temporary space needed to create a constraint
Date: Tue, 10 Jul 2012 19:08:07 -0400
Message-ID: <031701cd5ef0$dee87270$9cb95750$_at_rsiz.com>
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
From: Kellyn Pot'vin [mailto:kellyn.potvin_at_ymail.com]
Sent: Tuesday, July 10, 2012 6:36 PM
To: mwf_at_rsiz.com; 'oracle-l'
Subject: Re: Temporary space needed to create a constraint
I'm just going to chime in here... Didn't the original poster state that this process was being performed in parallel? I would inspect the slave/parallel processes for temp usage on each, which should show the sort in the explain plan as well, as I would expect sorting to occur with the parallel processes as they "knit" the results back together, (I know, this is my term for this step, please do not knock me for it... :))
Kellyn Pot'Vin
Senior Technical Consultant
Enkitec
DBAKevlar.com
From: Mark W. Farnham <mwf_at_rsiz.com>
To: 'oracle-l' <oracle-l_at_freelists.org>
Sent: Tuesday, July 10, 2012 4:16 PM
Subject: RE: Temporary space needed to create a constraint
Yeah, what JL wrote.
Could you send the DDL and version information?
Something is not making sense.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Tuesday, July 10, 2012 5:35 PM
To: oratune_at_yahoo.com; mark.powell2_at_hp.com; oracle-l
Subject: Re: Temporary space needed to create a constraint
But if there is a pre-existing index that contains all the data needed to enforce the constraint Oracle shouldn't need to do any sorting, it need only do a full scan of the index to check that there are no duplicates.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "David Fitzjarrell" <oratune_at_yahoo.com> To: <mark.powell2_at_hp.com>; "oracle-l" <oracle-l_at_freelists.org> Sent: Tuesday, July 10, 2012 9:31 PM Subject: Re: Temporary space needed to create a constraint
Which is exactly what he's done -- the constraint creation runs out of temp space even though no index is being created. The sort segment outgrows the available temp space and then errors out. I can guess that Oracle is ordering the keys to verify no duplicates exist before creating and enforcing the primary key, but I can't prove that on my small playground database.
David Fitzjarrell
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 10 2012 - 18:08:07 CDT