Re: SELECT col_a, (SELECT col_b .....

All experiments I have done to date indicate that the column-select is done read-consistently.

An interesting point to note is that when calling a pl/sql function, the trace file shows any SQL executed by the function, highlighting your point about functions not being read-consistent; however for column-select SQL, the trace file has NOTHING in it at all to give you any clues about what has happened.

I have raised a TAR with Oracle about this (and the related issue of casting a SQL
statement into an object-table/varray and finding that there is no indication of the run-time execution path). The response to date is that the issue has not been raised before, and even 9.0.2 doesn't help at
present. It is being considered by development.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Keith Boulton wrote in message
<9cgdkt88pel237ujmkdmt7dgie0e9t4k8e_at_4ax.com>...


>

>I always assumed that it was being implemented by the same mechanism

>as user defined functions. Which makes me wonder if it is read

>consistent with the start of the main statement, because user-defined

>functions are not.

>

>On Fri, 6 Jul 2001 14:28:02 +0200, "Frank" <franjoe_at_frisurf.no> wrote:

>

>>Hi!

>>

>>I didn't find anything very instructive from the explain plan either.

>>Could it be that the optimizer considers the column inline view a query on

>>its own, and that the

>>select enters the sql-engine "on its own"- it might get a cursor "of its

>>own" (v$open_cursor)

>>I find that the documentation is "reluctant" about this issue :-) unless

>>someone else has found it.

>>

>>Frank

>>

>>

>>

>