RE: Temporary space needed to create a constraint
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'Kellyn Pot'vin'" <kellyn.potvin@xxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Jul 2012 19:08:07 -0400
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@xxxxxxxxx]
Sent: Tuesday, July 10, 2012 6:36 PM
To: mwf@xxxxxxxx; '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@xxxxxxxx>
To: 'oracle-l' <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Tuesday, July 10, 2012 5:35 PM
To: oratune@xxxxxxxxx; mark.powell2@xxxxxx; 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@xxxxxxxxx>
To: <mark.powell2@xxxxxx>; "oracle-l" <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
