Re: enable foreign key taking huge temporary tablespace

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 22 Jan 2007 02:42:47 -0800
Message-ID: <1169462567.624934.38230@q2g2000cwa.googlegroups.com>

Steve Robin wrote:
> I have one foreign key, when I enable that one, it is taking huge temp.
> I increased my temp size more than 1.5 gb, but temp gets full again and
> again and giving temp full error, it is still taking more temp .
> Could anyone please tell me what can be the reason for that and what
> can be the solution.
>
> Oracle database : 9.2.0.4
> Platform : Sun Solaris 9
> Table (Primary Key) size : 500000 records
> Table (Foreign Key) size : 1500000 records

Does this happen immediately when the foreign key contraint is enabled, or when the first query is executed against the table?

If it happens when queries are executed, a 10053 trace or DBMS_XPLAN may show that the foreign key constraint is causing additional predicates to be added to the SQL statement, in an effort to improve performance. The additional predicates can cause a very different access path to be used to retrieve the data from the tables.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jan 22 2007 - 04:42:47 CST