better delete statement to remove duplicate rows from exception table?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 11 Dec 2003 15:59:34 -0800
Message-ID: <F001.005D9948.20031211155934@fatcity.com>

In the situation below, is there a better way to write the delete statement that eliminates duplicates? (assuming duplicate rows form at most 5 % of the table rows) Notice that the exceptions table is not analyzed.

If I analyze the exceptions table, is there then another better way to write it?

create table my_exceptions
  (row_id urowid,
   owner varchar2 (30),
   table_name varchar2 (30),
   constraint varchar2 (30)
  );

create table orders

   (order_id number (8) not null,
    order_date date,
    constraint orders_uq1 unique (order_id) disable    ) ;
/* -- load table orders with millions of rows */ create index orders_idx1
 on orders (order_id) ;
analyze table orders estimate statistics sample 10 percent ; alter table orders

   enable constraint orders_uq1
   exceptions into my_exceptions ;
delete
 from orders a
 where
   a.rowid in

     (select d.delete_row_id
       from
        (select
            min (b.row_id) over (partition by c.order_id) as keep_row_id,
            b.row_id as delete_row_id
          from my_exceptions b, orders c
          where c.rowid = b.row_id
          group by c.order_id, b.row_id
        ) d
       where
         d.delete_row_id != d.keep_row_id
     ) ;

commit ;
alter table orders

   enable constraint orders_uq1 ;
truncate table my_exceptions ;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Dec 11 2003 - 17:59:34 CST