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_at_jlcomp.demon.co.uk> To: "oracle-l" <oracle-l_at_freelists.org> 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 ?

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jul 11 2012 - 08:52:49 CDT