Re: expensive SQL

Hi!
I see you make an correct use of rownum, but I better like the use of analytic functions... Greetings
Bjoern

• Original Message ----- From: Mercadante, Thomas F (LABOR) To: bnsarma_at_gmail.com ; Oracle-L Freelists Sent: Friday, March 10, 2006 1:58 PM Subject: RE: expensive SQL

  BN,    

  We can only guess what the sql is supposed to accomplish. Is the programmer trying to get the latest record where server <>='cas1' by using rowed? If so, then this sql is not valid (think of records being deleted and new records being added - they will reuse a lower value rowid).    

  There are a lot of other ways to accomplish what (I think) he is trying to do:    

  select * from

   (select * from max_update

        Where server <> 'cas1'

         Order by (some column to determine the most recent record))

  Where rownum < 2    

  Good Luck!    

  Tom    

  From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of BN   Sent: Thursday, March 09, 2006 6:50 PM   To: Oracle-L Freelists
  Subject: expensive SQL    

  Greetings    

  I see the following SQL from the Developer,    

  select * from max_update where rowid in   (select max(rowid) from vrcdba.max_update group by request_id )   and request_id not in (select request_id from max_update where server ='cas1');        

  Takes for ever    

  What is he tryig to to do    

  Appreciate your help

  Regards & thanks    

--

http://www.freelists.org/webpage/oracle-l Received on Sat Mar 11 2006 - 06:29:14 CST