実行計画順序の表示
1415 ワード
/*
。
:1. select 。2。 。
, ,
*/
WITH t AS
(SELECT LEVEL AS lv,
connect_by_isleaf AS isleaf,
connect_by_root(id) AS root_id,
sys_connect_by_path(id, ',') AS id_path,
id,
operation,
object_name,
parent_id
FROM (SELECT id, operation, object_name, parent_id
FROM v$sql_plan
WHERE sql_id = '&sqlid'
AND child_number = &child_number)
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id),
l AS
(SELECT id2,
MAX(CASE
WHEN id >= id0 AND id2 > 0 THEN
id
ELSE
id + 999999
END) AS lv2
FROM (SELECT id,
MAX(to_number(regexp_substr(id_path, '[^,]+', 1, 2))) over() AS id0,
to_number(regexp_substr(id_path, '[^,]+', 1, LEVEL)) AS id2
FROM t
WHERE isleaf = 1
CONNECT BY LEVEL <= lv
AND PRIOR id = id
AND PRIOR dbms_random.value() IS NOT NULL)
GROUP BY id2)
SELECT row_number() over(ORDER BY l.lv2, t.lv DESC) AS sn,
t.id,
t.root_id,
lpad('*', lv, '*') || operation AS operation,
t.object_name,
t.parent_id,
t.isleaf,
t.id_path
FROM t
INNER JOIN l
ON l.id2 = t.id
ORDER BY 2