FAQ
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).

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 11, '03 at 11:59p
activeDec 11, '03 at 11:59p
posts1
users1
websiteoracle.com

1 user in discussion

Jacques Kilchoer: 1 post

People

Translate

site design / logo © 2022 Grokbase