RE: ** anyway to create a primary key on table with duplicates that cannot be deleted
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <ajoshi977@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Oct 2005 14:33:51 -0700
_____
De : oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] De la part de A Joshi
I have a table under replication and corrupted with duplicates. I
cannot do any update/delete operation on the table since it needs a
primary key. I cannot create a primary key since duplicates exist.
catch..
i tried creating index by appending rowid/rownum but no luck. Any way
out of this. Cannot remove the table from replication since that needs
quiescing and cannot quiese right now due to processing that will last
another 4 days. Thanks for help.
Would any of these methods work for you?
0) create test table (has duplicates)
drop table order_items ;
create table order_items
(order_id number (6),
product_id number (6),
quantity number (10)
) ;
insert into order_items (order_id, product_id, quantity) values (1, 10,
200) ;
insert into order_items (order_id, product_id, quantity) values (2, 19,
591) ;
insert into order_items (order_id, product_id, quantity) values (2, 20,
612) ;
insert into order_items (order_id, product_id, quantity) values (3, 30,
111) ;
insert into order_items (order_id, product_id, quantity) values (1, 17,
223) ;
insert into order_items (order_id, product_id, quantity) values (1, 10,
172) ;
insert into order_items (order_id, product_id, quantity) values (4, 50,
677) ;
insert into order_items (order_id, product_id, quantity) values (4, 49,
987) ;
insert into order_items (order_id, product_id, quantity) values (3, 30,
364) ;
insert into order_items (order_id, product_id, quantity) values (5, 23,
657) ;
insert into order_items (order_id, product_id, quantity) values (3, 30,
934) ;
-- add primary key constraint DISABLE
alter table order_items
add (constraint order_items_pk primary key (order_id, product_id)
disable) ;
1) delete duplicates method 1
delete
from
order_items a
where
a.rowid in
(select b.row_id
from
(select
c.rowid as row_id,
row_number () over (partition by c.order_id, c.product_id
order by c.rowid) as rn
from order_items c
) b
where b.rn != 1
) ;
alter table order_items enable constraint order_items_pk ;
2) delete duplicates method 2
delete
from order_items a
where a.rowid > (select min (c.rowid)
from order_items c
where c.order_id = a.order_id
and c.product_id = a.product_id) ;
alter table order_items enable constraint order_items_pk ;
3) using EXCEPTIONS table - method 1 (saving duplicates in temp table)
----------------------------------------------------------
-- Finding rowids for duplicate rows, listing duplicate rows,
-- deleteting duplicate rows, and enabling PK constraint
-- may be faster method for large table
-- N.B. exceptions table created with
ORACLE_HOME\rdbms\admin\utlexcpt.sql
delete from exceptions
where owner = user
and table_name = 'ORDER_ITEMS' ;
drop table duplicate_orders ;
alter table order_items
enable constraint order_items_pk exceptions into exceptions ;
create table duplicate_orders
as
select a.order_id, a.product_id,
a.rowid as duplicate_row_rowid
from order_items a
where a.rowid in
(select b.row_id from exceptions b
where b.owner = user
and b.table_name = 'ORDER_ITEMS') ;
delete from duplicate_orders a
where a.rowid =
(select min (b.rowid)
from duplicate_orders b
where a.order_id = b.order_id
and a.product_id = b.product_id
) ;
delete from order_items a
where a.rowid in
(select b.duplicate_row_rowid from duplicate_orders b);
alter table order_items enable constraint order_items_pk ;
delete from exceptions
where owner = user
and table_name = 'ORDER_ITEMS' ;
4) using EXCEPTIONS table - method 2 (no temp table)
----------------------------------------------------------
-- Finding rowids for duplicate rows, listing duplicate rows,
-- deleteting duplicate rows, and enabling PK constraint
-- N.B. exceptions table created with
ORACLE_HOME\rdbms\admin\utlexcpt.sql
alter table order_items
enable constraint order_items_pk exceptions into exceptions ;
select
a.order_id, a.product_id, a.quantity
from order_items a, exceptions b
where b.owner = user
and b.table_name = 'ORDER_ITEMS'
and b.row_id = a.rowid
order by a.order_id, a.product_id ;
delete
from order_items a
where exists
(select * from exceptions b
where b.owner = user
and b.table_name = 'ORDER_ITEMS'
and b.row_id = a.rowid
)
and a.rowid > (select min (c.rowid)
from order_items c
where c.order_id = a.order_id
and c.product_id = a.product_id) ;
alter table order_items enable constraint order_items_pk ;
delete from exceptions
where owner = user
and table_name = 'ORDER_ITEMS' ;
Other related posts:
