RE: expensive SQL

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-----
  From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mercadante, Thomas F (LABOR)
  Sent: Friday, March 10, 2006 7:58 AM
  To: bnsarma_at_gmail.com; Oracle-L Freelists   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 Fri Mar 10 2006 - 15:42:12 CST