Re: Monster SQL

You've discovered one of the major defects in scalar subqueries. If you use a correlated scalar subquery in the select list, Oracle doesn't have ANY visible information about the execution of the subquery until Oracle 9 - and then it tends to report the execution plan for each subquery as if it were an independent query, with no indication of frequency of execution.

If you have very precise indexes that suit the three subqueries, then the execution path is probably equivalent to a 'for each row' nested loop access into the three copies of the table.

You may get some confirmation by running with event 10046 set to level 8, and checking the trace file for file reads. You might get a clue from v$sesstat by checking the 'rows fetched by rowid' compared to 'rows fetched by tablescan' as the query runs - do array fetches with pauses. You could get some clues by taking snapshots of the cache buffers chains latches and cross checking latches with buffered blocks.

For this example, I think I'd look carefully at a four way (outer) join. I dislike any Oracle feature that isn't instrumented to show me how much work it is doing, or expects to to.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearances:
 March 2004 Hotsos Symposium - The Burden of Proof
   DYnamic Sampling - an investigation
 March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June


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


"Matt" <mccmx_at_hotmail.com> wrote in message
news:cfee5bcf.0402280316.607847f0_at_posting.google.com...


> Does anyone know how Oracle would resolve this piece of SQL....

>

> SELECT DISTINCT

> A.PROCESS_INSTANCE, A.EMPLID, A.EMPL_RCD, A.DUR, A.SEQ_NBR, 0, 0, 0,

> 0, A.TRC,

> NVL(

> (

> select B.TL_QUANTITY from PS_TL_IPT14 b

> where A.PROCESS_INSTANCE = B.PROCESS_INSTANCE

> AND A.EMPLID = B.EMPLID

> AND A.SEQ_NBR = B.SEQ_NBR

> AND A.DUR = B.DUR AND

> B.TRC= 'C1'

> )

> ,0),

> NVL(

> (

> select C.TL_QUANTITY from PS_TL_IPT14 C

> where A.PROCESS_INSTANCE = C.PROCESS_INSTANCE

> AND A.EMPLID = C.EMPLID

> AND A.SEQ_NBR = C.SEQ_NBR

> AND A.DUR = C.DUR

> AND C.TRC= 'C3DMY'

> )

> ,0),

> NVL(

> (

> select D.TL_QUANTITY from PS_TL_IPT14 D

> where A.PROCESS_INSTANCE = D.PROCESS_INSTANCE

> AND A.EMPLID = D.EMPLID

> AND A.SEQ_NBR = D.SEQ_NBR

> AND A.DUR = D.DUR

> AND D.TRC= 'C8'

> )

> ,0),

> 123, 456

> FROM

> PS_TL_IPT14 A

> WHERE

> A.PROCESS_INSTANCE = :1

> AND

> TRC IN ('C1', 'C3DMY', 'C8')

>

>

>

> Running it through explain plan just returns:

>

> Execution Plan

> ----------------------------------------------------------

>    0      SELECT STATEMENT Optimizer=CHOOSE

>    1    0   SORT (UNIQUE)

>    2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PS_TL_IPT14'

>    3    2       INDEX (RANGE SCAN) OF 'PSBTL_IPT14' (NON-UNIQUE)

>

> Since there is no join listed in the explain plan, how does Oracle

> perform the self join of A, B, C, and D (which are all PS_TL_IPT14).

>

> NB:  the bind variable :1 is always every single row in the

> table.....!

>

> I'm sure that this logic can be coded procedurally or even with

> analytical SQL but I'm curious as to how Oracle would resolve it

> because the explain is not explicit.

>

> Version 8.1.7.4.1 EE.

>

> Matt