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

Home -> Community -> Usenet -> c.d.o.server -> Re: Anyone have a better way??

Re: Anyone have a better way??

From: John L. Viescas <JohnLV_at_nomail.Please>
Date: 1997/07/12
Message-ID: <01bc8f0a$30bbf300$812d2299@jv-tecra>#1/1

Phil-

I have to first admit that I'm not an Oracle expert by any stretch of the imagination. However, my comments below apply, in general, to optimizing any SQL correlated subquery.

Your problem is somewhat complicated in that you want only ONE row per dev.  But you're still going to have to go with a correlated subquery (in your example, b) to get it solved with one query. You might try saving the innermost query as a view and then do a join with that to try to get the optimizer to not run b over and over.  

-- 
John Viescas
author, "Running Microsoft Access 97"
http://mspress.microsoft.com/mspress/books/bio/64.htm

Phil Mattson <phil.mattson_at_itron.com> wrote in article
<01bc8e45$af462a30$b30ca8c0_at_mattson>...

> Hello all,
>
> I'm having a problem coming up with an optimal way to perform a certain
> query with an Oracle 7 DB. Anyone up to the task?
>
> Given:
>
> Oracle 7.3 DB
> A single table (ii.rdgtemp) has a dozen columns. The table is "ordered by
> dev ASC, quality DESC".
>
> An example of the data would be:
>
>
> dev quality owner misc1 misc2 ...
>
> 1 8 ABC .............
> 1 8 BCA .............
> 1 8 AAA .............
> 1 5 BBB .............
> 1 5 CCC .............
> 1 4 BAA .............
> 2 9 AAA .............
> 2 9 CCC .............
> 2 1 BCA .............
> 3 4 CCC .............
> 19 9 AAA .............
> 19 2 BBB .............
> 20 4 BCA .............
>
>
>
> The "Goal" is to retrieve a single highest quality row for each dev.
> If doesn't matter which "highest quality" row is selected for each dev.
>
> The select should return something like:
>
> dev quality owner misc1 misc2 ...
>
> 1 8 ABC .............
> 2 9 AAA .............
> 3 4 CCC .............
> 19 9 AAA .............
> 20 4 BCA .............
>
> It might also return something like (note a differnent row was used for
dev
> 1):
>
> dev quality owner misc1 misc2 ...
>
> 1 8 BCA .............
> 2 9 AAA .............
> 3 4 CCC .............
> 19 9 AAA .............
> 20 4 BCA .............
>
>
> I'm currently accomplishing this by performing this statement, which
isn't
> so fast when the number of rows gets large (10 million rows may be
present
> in ii.rdgtemp).
>
> select * from ii.rdgtemp where rowid in ( select max(a.rowid) hirow from
> ii.rdgtemp a,
> ( select dev, max(quality) maxquality
> from II.RDGTEMP group by dev) b where
> a.dev = b.dev AND
> a.quality = b.maxquality
> group by a.dev ) )
>
>
> Anyone got a better way?
>
>
> Thanks for any and all advice!!!
>
>
> Phil Mattson
> Itron, Inc.
> phil.mattson_at_itron.com
> (509)-891-3748
>
>
>
Received on Sat Jul 12 1997 - 00:00:00 CDT

Original text of this message

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