Re: How much time required to add constraint(s)

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Thu, 15 Dec 2011 19:14:08 +0200
Message-ID: <CAN2wOq31G5U1EiUKfjc3i1Kp5L5u-EVXG_szXaY_w3H5VLMyLA_at_mail.gmail.com>

One option:
Run trace for a such alter statement (not necessarily on production) and look at recursive SQL containing both referenced tables. If I remember correctly it was some kind of outer join between the tables. OK it is something like this:
 select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "GINTS"."T77" A , "GINTS"."IEDZIVOTAJI" B where( "A"."ID" is not null) and( "B"."IED_ID" (+)= "A"."ID") and( "B"."IED_ID" is null)
So knowing one, you can quite easily guess what others will be. If the join is done using nested loops then you won't find anything in v$session_longops.

Gints Plivna
http://www.gplivna.eu

2011/12/15 Rich <richa03_at_gmail.com>

> Hi list,
> I'm trying to determine how much time will be required to build a
> constraint - something like:
> ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <NAME> FOREIGN KEY (<FK_NAME>)
> REFERENCES <REF_TABLE_NAME> (<REF_COL_NAME) ENABLE;
>
> Testing this, I don't see any operation in v$session_longops, however
> the test runs for hours.
> There are many of these and we have limited amounts of time to do them
> - we can do them in batches over time, however, we can't exceed our
> windows due to performance.
>
> I also don't see anything in v$sql_plan for this SQL_ID...
>
> How, exactly, does Oracle build a constraint?
> Ie, what plan/operations does it use.
> Is there any way I can estimate time (blocks read, etc.) prior to
> building the constraint?
>
> Thanks,
> Rich
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 15 2011 - 11:14:08 CST