Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: expensive SQL

RE: expensive SQL

From: Mark W. Farnham <>
Date: Fri, 10 Mar 2006 16:42:12 -0500
Message-ID: <>

Tom's points that we can only guess and that rowid is no guarantee that he is getting the most recent row is valid.

We can't tell if "where server <> 'cas1' " is correct. Since there are apparently possibly many rows per request_id, we have no way of knowing if some request_ids have been serviced on both cas1 and some other server. So if the developer meant to exclude request_ids that had any row with server cas1, then that part of the rewrite is broken. If request_ids get rows on exactly one server, then the rewrite looks okay.

If you're pre-ASSM and never delete rows and you only have one table in the tablespace, then I *think* you could use rowid in that manner, but since so many things could change at the physical layer and break your results it would certainly be a bad thing to rely on.

Either this developer is doing something very sophisticated that I don't groc, or the developer would be well served by a good course or book on sql.

Oh, and if the bit about never on cas1 is correct, and there are possibly many request_ids equal for cas1, a group by there would sure shorten the in list search.
  -----Original Message-----
[]On Behalf Of Mercadante, Thomas F (LABOR)
  Sent: Friday, March 10, 2006 7:58 AM
  To:; Oracle-L Freelists   Subject: RE: expensive SQL


  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!



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


  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

-- Received on Fri Mar 10 2006 - 15:42:12 CST

Original text of this message