Oracle 递归查询以获取视图的基本表，但是从视图列表中

嗨，

我很高兴以前也问过类似的问题，但是如果您愿意，我正在为下一步而苦苦挣扎。

我想获取视图列表使用的所有基表 (递归)。从这里的各种帖子中，我知道如何为一个视图执行此操作，并且工作正常。但是，我想为视图列表 (例如来自子查询) 执行此操作。我尝试了各种各样的东西，但是当我得到一些有效的东西时，性能就很糟糕了。

我的最新尝试在下面，需要提到我根本不是Oracle专家，因此我使用的SQL可能不是最好的。

我在这里试图做的是获取在某一天使用的所有视图的基本表 (因此从DBA_HIST_SQL_PLAN中选择子。这对于几个视图运行良好，但很快性能下降 (我甚至不确定逻辑是否100% 正确在这里)。区别存在，因为我想处理每个视图只有一次 (并且由于递归性质重复将出现)。

SELECT DISTINCT
   OWNER
,   NAME 
,   TYPE 
,   REFERENCED_OWNER
,   REFERENCED_NAME
,   REFERENCED_TYPE 
FROM DBA_DEPENDENCIES 
WHERE TYPE = 'VIEW' 
AND REFERENCED_TYPE='TABLE'
START WITH (OWNER,NAME) IN 
(

    SELECT
        OBJECT_OWNER
    ,   OBJECT_NAME
    FROM DBA_HIST_SQL_PLAN
    WHERE TRUNC(timestamp) BETWEEN TO_DATE('2018-12-16', 'YYYY-MM-DD') AND TO_DATE('2018-12-16', 'YYYY-MM-DD')
    AND OBJECT_TYPE = 'VIEW'
    AND OBJECT_OWNER IS NOT NULL
    AND OBJECT_NAME IS NOT NULL
)
CONNECT BY NOCYCLE (OWNER,NAME) = ((PRIOR REFERENCED_OWNER, PRIOR REFERENCED_NAME ))

因此，总结一下我试图实现的目标是获得一个视图名称列表以及系统上某一天使用的所有基础表。如前所述，我知道如何为一个视图执行此操作，但是我想为另一个 (子) 查询提供的视图列表执行此操作。

任何帮助建议都将受到赞赏，或者如果有人有更好的准备摘要。

更新:
经过一番挖掘，我尝试了一种不同的方法，它似乎工作得更快，但我有一种感觉，这仍然不是100% 正确的...理想情况下，我想以如下所示的结果集结束:

<视图所有者> <视图名称> <引用表所有者> <引用表名称>

(其中视图和视图名称的所有者，前2列是指我们正在查找的视图。我对中间结果 (视图) 没有必要感兴趣，除非我们可以将它们放在单独的一对列中)

这是这个新尝试的代码:

WITH usedViews (name, owner, type, rname, rowner, rtype,  lvl ) AS 
(
   SELECT name, owner, type, referenced_name, referenced_owner, referenced_type, 1 AS lvl
   FROM dba_dependencies
   WHERE (owner,name) IN 
   (
        SELECT
            OBJECT_OWNER
        ,   OBJECT_NAME
        FROM DBA_HIST_SQL_PLAN
        WHERE TRUNC(timestamp) BETWEEN TO_DATE('2018-12-16', 'YYYY-MM-DD') AND TO_DATE('2018-12-16', 'YYYY-MM-DD')
        AND OBJECT_TYPE = 'VIEW'
        AND OBJECT_OWNER IS NOT NULL
        AND OBJECT_NAME IS NOT NULL
   )
   AND referenced_type IN ('TABLE','VIEW')
   
   UNION ALL

   SELECT a.name, a.owner, a.type, a.referenced_name, a.referenced_owner, a.referenced_type, r.lvl + 1
   FROM dba_dependencies a,
        usedViews  r
   WHERE r.rname = a.name
     AND r.rowner = a.owner
     AND r.rtype = a.type
     AND a.referenced_type IN ('TABLE','VIEW')
) cycle name, owner, type set cycle_detected to 1 default 0
   
SELECT DISTINCT
    owner
,   name
,   rowner
,   rname
FROM usedViews
WHERE rtype = 'TABLE'

更新2:
抱歉，这篇文章很长 (但可能会对其他人有所帮助)，但我想我可能已经做到了，但欢迎发表评论/反馈。
这是最新版本:

WITH usedViews (viewname, viewowner, viewtype, interim_name, interim_owner, interim_type, refname, refowner, reftype, lvl ) AS 
(
   SELECT name, owner, type, name, owner, type, referenced_name, referenced_owner, referenced_type, 1 AS lvl
   FROM dba_dependencies
   WHERE (owner,name) IN 
   (
        SELECT DISTINCT
            OBJECT_OWNER
        ,   OBJECT_NAME
        FROM DBA_HIST_SQL_PLAN
        WHERE TRUNC(timestamp) BETWEEN TO_DATE('2018-12-16', 'YYYY-MM-DD') AND TO_DATE('2018-12-16', 'YYYY-MM-DD')
        AND OBJECT_TYPE = 'VIEW'
        AND OBJECT_OWNER IS NOT NULL
        AND OBJECT_NAME IS NOT NULL
   )
   AND referenced_type IN ('TABLE','VIEW')
   
   UNION ALL

   SELECT r.viewname,r.viewowner,r.viewtype,a.name, a.owner, a.type, a.referenced_name, a.referenced_owner, a.referenced_type,r.lvl + 1
   FROM dba_dependencies a,
        usedViews  r
   WHERE r.refname = a.name
     AND r.refowner = a.owner
     AND r.reftype = a.type
     AND a.referenced_type IN ('TABLE','VIEW')
     
)  
SELECT DISTINCT
    viewname
,   viewowner
,   viewtype
,   refname
,   refowner
,   reftype
FROM usedViews
WHERE RefTYPE = 'TABLE'
GROUP BY
    viewname
,   viewowner
,   viewtype
,   refname
,   refowner
,   reftype
ORDER BY 
    viewname
,   viewowner
,   viewtype
,   refname
,   refowner
,   reftype

非常感谢，

米沙

有趣的是…… 当我们从顶部阅读本文时，我在想 “递归似乎是一种更自然的方法”，瞧，我们最终的想法与我一直在阅读的想法相同 :-)

我认为你的方法很好。我可能补充的唯一一件事是，DBA_DEPENDENCIES有时会有点慢 (它是一个相当复杂的字典视图)，所以你可能会得到一些好处，通过在查询中做艰苦的工作之前把它变成自己的临时副本。

如下所示:

WITH 
  tmp_Dep as ( 
    select /*+ materialize */ name, owner, type, referenced_name, referenced_owner, referenced_type
    FROM dba_dependencies
    where owner not in ( select distinct schema from dba_registry )
    ),
usedViews (viewname, viewowner, viewtype, interim_name, interim_owner, interim_type, refname, refowner, reftype, lvl ) AS 
(
   SELECT name, owner, type, name, owner, type, referenced_name, referenced_owner, referenced_type, 1 AS lvl
   FROM tmp_Dep
   WHERE (owner,name) IN 
   (
        SELECT DISTINCT
            OBJECT_OWNER
        ,   OBJECT_NAME
        FROM DBA_HIST_SQL_PLAN
        WHERE TRUNC(timestamp) BETWEEN TO_DATE('2018-12-16', 'YYYY-MM-DD') AND TO_DATE('2018-12-16', 'YYYY-MM-DD')
        AND OBJECT_TYPE = 'VIEW'
        AND OBJECT_OWNER IS NOT NULL
        AND OBJECT_NAME IS NOT NULL
   )
   AND referenced_type IN ('TABLE','VIEW')
   UNION ALL
   SELECT r.viewname,r.viewowner,r.viewtype,a.name, a.owner, a.type, a.referenced_name, a.referenced_owner, a.referenced_type,r.lvl + 1
   FROM tmp_Dep a,
        usedViews  r
   WHERE r.refname = a.name
     AND r.refowner = a.owner
     AND r.reftype = a.type
     AND a.referenced_type IN ('TABLE','VIEW')
     
)  
SELECT DISTINCT
    viewname
,   viewowner
,   viewtype
,   refname
,   refowner
,   reftype
FROM usedViews
WHERE RefTYPE = 'TABLE'
GROUP BY
    viewname
,   viewowner
,   viewtype
,   refname
,   refowner
,   reftype
ORDER BY 
    viewname
,   viewowner
,   viewtype
,   refname
,   refowner
,   reftype

我使用对DBA_REGISTRY的查询排除了内部内容，但是如果要保留这些内容，可以将其删除。