Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: trouble with an SQL statement
"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
news:1112912995.145557.9860_at_l41g2000cwc.googlegroups.com...
> If you can easily identify the rows you want to keep and you have
> exclusive access to the table then why not use a Create Table As Select
> of the rows you want, truncate the original table, and then reinsert
> the CTAS'd rows back.
>
> The above method would probably be much faster than say deleting where
> the rowid in not in a group by subselect where you select the
> min(timestamp) row grouped by the key columns and description.
>
Thanks mark. I "think" I got it working with a "minus". The the performance is poor I'll look at your suggestion.
For info:
select evt_code
from evt_event
where evt_desc in(
select evt_desc
from evt_event
group by evt_desc
having count(*) > 1
)
minus
select distinct mkt_evt_code
from mkt_snapshot,evt_event
where (evt_desc,mkt_timestamp) in
(
select evt_desc,max(mkt_timestamp)
from evt_event,mkt_snapshot
where evt_desc in(
select distinct evt_desc
from evt_event
group by evt_desc
having count(*) > 1
)
and mkt_evt_code=evt_code
group by evt_desc
)
Received on Thu Apr 07 2005 - 18:43:55 CDT