Re: Temporary space needed to create a constraint
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 11 Jul 2012 14:55:12 +0100
Alternatively you could set up a temporary "rewrite equivalence" between
the statement and something like
select null,null,null,null from dual where 1 = 0
(you might need to include some explicit coercion on the nulls)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, July 11, 2012 2:52 PM
Subject: Re: Temporary space needed to create a constraint
When in doubt, try it out.
Oracle's been doing this since at least 8.1.7.4
Looking at the SQL (which has improved a little since 8.1.7.4) I think it's
trying to collect all rows that break the constraint in anticipation of
writing them into the exceptions table - should you choose to specify an
exceptions table. The nasty bit happens even if you add with novalidate and
then validate.
I think you could try capturing the SQL for the check, then creating an SQL
Baseline for it that forces it to take a path which would basically be a
nested loop on an index full scan. Ideally you do this with a sort group by
clause first so that no data is ever found and the second step of the loop
never happens. (That's based on a quick inspection of the code - I don't
guaranteed at the moment that there is such a legal path.)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, July 11, 2012 12:39 PM
Subject: Re: Temporary space needed to create a constraint
What's your code to define the constraint and the existing index, and
what's the execution plan you're seeing in the trace file ?
And is the original table a simple heap table, or is it partitioned, and if
partitioned was the index local or global ?
--
//www.freelists.org/webpage/oracle-l
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2193 / Virus Database: 2437/5124 - Release Date: 07/10/12
--
//www.freelists.org/webpage/oracle-l
Other related posts:
