Re: enable foreign key taking huge temporary tablespace

Steve Robin wrote:
> 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.

Can you check what statement is going on when temp tablespace is being extended?
For a statement like that
alter table b add constraint b_a_fk foreign key (a) references a(a);

Oracle is interanally doing check to validate the FK and the statement is as follows (at least for 9.2.0.7)
select /*+ all_rows */ "A".rowid, 'GINTS', 'B', 'B_A_FK' from "GINTS"."B" "A" ,
"GINTS"."A" "B" where( "A"."A" is not null) and( "B"."A" (+)= "A"."A") and( "B"."A" is null)

I assume that for big tables it may result in hash join and that most propbably will need some temp space.

On the other hand 1.5 G temp space is not that big (at least to my mind).

And on the another hand :) if you are completely sure that you have valid data you can enable fks with novalidate keyword and Oracle won't check the data.

Gints Plivna
http://www.gplivna.eu Received on Mon Jan 22 2007 - 05:49:56 CST