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@xxxxxxxxx> 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
