RE: ** anyway to create a primary key on table with duplicates that cannot be deleted

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 19 Oct 2005 14:33:51 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262119FCEA@irvmbxw02>

---

De : oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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' ;

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

Received on Wed Oct 19 2005 - 16:36:47 CDT