Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: trouble with an SQL statement

Re: trouble with an SQL statement

From: FairPlay <not_at_here.com>
Date: Fri, 8 Apr 2005 00:43:55 +0100
Message-ID: <4255c5bb$0$289$cc9e4d1f@news-text.dial.pipex.com>

"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US