
-- Examine the Plan chosen by the optimizer

SET VERIFY   OFF
SET FEEDBACK OFF

COLUMN OPERATION   FORMAT A40
COLUMN OPTIONS     FORMAT A15
COLUMN OBJECT_NAME FORMAT A15


-- Explain plan for query, that is given as argument in input-line
EXPLAIN PLAN 
  SET statement_id = '&&username' 
  FOR &1;

-- Explain plan for specified query
-- EXPLAIN PLAN 
--   SET statement_id = '&&username' 
--   FOR 
--     SELECT UNIQUE DiskTitel
--       FROM Disk, Person P
--      WHERE UPPER(Name) LIKE '%COCKER%JOE%'
--        AND  DiskID IN (SELECT DiskID 
--                          FROM Interpret M 
--                         WHERE (P.PID = M.PID) 
--                      UNION 
--                        SELECT DiskID 
--                          FROM Autor M
--                         WHERE (P.PID = M.PID));


SELECT LPAD(' ', 2*(level-1)) || operation operation, options, object_name 
FROM plan_table
START WITH id = 0 AND statement_id = '&&username' 
CONNECT BY PRIOR id = parent_id AND statement_id = '&&username';

DELETE FROM plan_table where statement_id = '&&username';

UNDEFINE username

SET VERIFY   ON
SET FEEDBACK ON
