Re: Creating foreign key

Thank you Jonathan. I tested on a ~90million rows table which was having an existing unique index. So adding constraint to it just happens within seconds. Thank You.

Regards
Lok

On Sat, May 1, 2021 at 1:13 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> Try this:
>
> create table t1 pctfree 90 pctused 10 as select * from all_objects;
>
> alter table t1 modify object_id null;
>
> create unique index t1_i1 on t1(object_id) pctfree 90;
>
> alter system flush buffer_cache;
>
> alter session set events '10046 trace name context forever, level 8';
>
> alter table t1 add constraint t1_uk unique(object_id);
>
> alter session set events '10046 trace name context off';
>
>
> Tested on 11.2.0.4
> You should find that you have a unique constraint on the table protected
> by the index, with virtually no work done (apart from the dictionary checks
> and updates).
>
> If you want a little more "physical" confidence, create a table as "select
> 10M rows from your big table" and do the same "unique index", flush, add
> constraint cycle and see how little work it takes.
>
> Regards
> Jonathan Lewis
>
>
>
>
> On Fri, 30 Apr 2021 at 20:06, Lok P <loknath.73_at_gmail.com> wrote:
>
>> Hello All, We are using version 11.2.0.4 of Oracle. We have a requirement
>> in which we want to create foreign key on a new table which is going to
>> reference one of the big transaction table holding ~1 billion+ rows. The
>> transaction table holds a unique index on that column but doesn't have an
>> unique constraint , so the foreign key creation on the new table is
>> failing( *ORA-02270: no matching unique or primary key * ) .
>>
>> So my doubt was, to create the foreign key in the fastest way we will
>> first need to add the unique constraint to the big transaction table and
>> for that , if the "älter table add constraint con1 unique ( col1)... using
>> index Idx1 "will be the fastest approach here?
>>
>> Regards
>> Lok
>>
>

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