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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
