set linesize 150
set pagesize 500
col PLANLINE for a120
SELECT EXECORD EXEC_ORDER, PLANLINE
FROM (SELECT PLANLINE, ROWNUM EXECORD, ID, RID
FROM (SELECT PLANLINE, ID, RID, LEV
FROM (SELECT lpad(" ",2*(LEVEL),rpad(" ",80," "))
||OPERATION||" "|| -- Operation
DECODE(OPTIONS,NULL,"","("||OPTIONS || ") ")
|| -- Options
DECODE(OBJECT_OWNER,null,"","OF """|| OBJECT_OWNER
||".")|| -- Owner
DECODE(OBJECT_NAME,null,"",OBJECT_NAME|| """ ")
|| -- Object Name
DECODE(OBJECT_TYPE,null,"","("||OBJECT_TYPE|| ") ")
|| -- Object Type
DECODE(ID,0,"OPT_MODE:")|| -- Optimizer
DECODE(OPTIMIZER,null,"","ANALYZED","", OPTIMIZER)||
DECODE(NVL(COST,0)+NVL(CARDINALITY,0)+NVL(BYTES,0),
0,null," (COST="||TO_CHAR(COST)||",CARD="||
TO_CHAR(CARDINALITY)||",BYTES="||TO_CHAR(BYTES)||")")
PLANLINE, ID, LEVEL LEV,
(SELECT MAX(ID)
FROM PLAN_TABLE PL2
CONNECT BY PRIOR ID = PARENT_ID
AND PRIOR STATEMENT_ID = STATEMENT_ID
START WITH ID = PL1.ID
AND STATEMENT_ID = PL1.STATEMENT_ID) RID
FROM PLAN_TABLE PL1
CONNECT BY PRIOR ID = PARENT_ID
AND PRIOR STATEMENT_ID = STATEMENT_ID
START WITH ID = 0
AND STATEMENT_ID = "aaa")
ORDER BY RID, -LEV))
ORDER BY ID; |