Re: bad SQL, naughty SQL....

One more feature (Oracle 7.3.3.4 for SCO UNIX)

select rowid from tablea where rowid in (select rowid from tableb);

results in

ORA-08103: object no longer exists

and

select rowid from tablea where rowid in (select '1' from tableb);

hangs the session.

Both tablea and tableb are current user's small tables, there are no other
users connected at present, everything works OK. --
 Alexander I.Doroshko, aid_at_grant.kharkov.ua

• Bank "Grant", Kharkov, Ukraine

Jason Salter <jason_at_seahorseNOSPAM.demon.co.uk> wrote in article <37ff66e8.5297954_at_news.demon.co.uk>...
: On Fri, 8 Oct 1999 21:53:00 +0100, "Jonathan Lewis"
: <jonathan_at_jlcomp.demon.co.uk> wrote:
:
: >
: >That has to rate as a highly unusual bit of SQL,
: >but, whatever anyone else may have said so far
: >it could be doing what it's meant to be doing.
: >
: >The following is a spool direct from an SQL*Plus session:
: >
: >-----------------------------------------------------------------
: >
: >SQL> delete from tablea where rowid not in
: > 2 (select rowid from tableb);
: >
: >0 rows deleted.
: >
: >SQL> delete from tablea where rowid in
: > 2 (select rowid from tableb);
: >
: >1 row deleted.
: >
: >----------------------------------------------------------
: >
: >And yes - rowid really does mean rowid,
: >and tableA and tableB really are two different
: >tables, and I haven't done silly tricks with
: >views or synonyms.
: >
: >Oracle 8.0.5, but it would do the same on 7.3
: >
: >Interpretation left as exercise for the reader.
: >I should be okay for 48 hours, Thomas Kyte
: >doesn't get back until Monday.
:
: Any idea's on why the 'db sequential file read' event in
: v$session_wait? It tells me that it's waiting for a certain block on
: the datafile in which TableA resides.
:
: Is this just 'ticking over' as the 'NOT IN' comparisons are being
: handled by the CPU's? I can't think of any reasons why it should be
: waiting otherwise. The filesystem is quiet, no swapping or paging of
: any kind is going on, the block buffers at running at 100% (although
I
: know that this not always an indication of a well tune SGA), no sorts
: to disk are happening, in fact it's just using CPU.
:
: Puzzling.
:
: Jason.
:
Received on Sat Oct 09 1999 - 05:27:30 CDT