Monster SQL

From: Matt <mccmx_at_hotmail.com>
Date: 28 Feb 2004 03:16:55 -0800
Message-ID: <cfee5bcf.0402280316.607847f0@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 Received on Sat Feb 28 2004 - 05:16:55 CST