Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: enable foreign key taking huge temporary tablespace

Re: enable foreign key taking huge temporary tablespace

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: 22 Jan 2007 03:49:56 -0800
Message-ID: <1169466596.714300.307260@11g2000cwr.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US