Re: enable foreign key taking huge temporary tablespace

Charles Hooper wrote:
> 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.

Actually I was refreshing all schema. So I disabled all constraints. Now after refresh all tables in schema. When I am try to enable this constraint, it is creating a lot of problem. Received on Mon Jan 22 2007 - 05:40:03 CST