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.
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@freelists.org [mailto:oracle-l-bounce@freelists.org] 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');
What is he tryig to to
do
Appreciate your help
Regards &
thanks
