ORACLE如何找出视图依赖的对象和视图嵌套层数

之前写过一篇文章“SQL Server如何找出视图依赖的对象和视图嵌套层数”，这里我介绍一下Oracle数据库中如何找出视图的依赖对象以及视图嵌套层数关系。主要通过DBA_DEPENDENCIES这个系统视图（这个系统视图中包含有对象的依赖关系数据）。另外，我们使用了Oracle的树形查询（层级查询）来展示这种层级关系。对比SQL Server数据库与Oracle数据库的SQL来说，感觉Oracle由于拥有非常给力的系统函数，感觉写出来的SQL更优雅与简洁。如果你对代码简洁优雅有股执着与偏执的话。就会有这样的感觉。

--==================================================================================================================
--        ScriptName            :            get_view_referenced_objects.sql
--        Author                :            潇湘隐者    
--        CreateDate            :            2018-08-03
--        Description           :            查看视图引用的对象
--        Note                  :             
/*-*****************************************************************************************************************
        Parameters              :                                    参数说明
********************************************************************************************************************
            &OWNER              :            视图的OWNER
            &VIEW_NAME          :            视图的名称
********************************************************************************************************************
   Modified Date    Modified User     Version                 Modified Reason
********************************************************************************************************************
    2018-08-03        潇湘隐者         V01.00.00        新建该脚本。
*******************************************************************************************************************/
SELECT  V.ROW_LEVEL
       ,V.OBJECT_OWNER
       ,V.OBJECT_NAME
       ,V.OBJECT_TYPE
       ,V.REFERENCED_OWNER
       ,V.REFERENCED_NAME
       ,O.OBJECT_TYPE  AS REFERENCED_OBJECT_TYPE
FROM
(
SELECT LEVEL                AS ROW_LEVEL
      ,D.OWNER              AS OBJECT_OWNER
      ,D.NAME               AS OBJECT_NAME
      ,D.TYPE               AS OBJECT_TYPE
      ,D.REFERENCED_OWNER   AS REFERENCED_OWNER
      ,D.REFERENCED_NAME    AS REFERENCED_NAME
FROM DBA_DEPENDENCIES D 
START WITH D.OWNER=UPPER('&OWNER') AND D.NAME =UPPER('&VIEW_NAME') AND D.TYPE='VIEW'
CONNECT BY NOCYCLE PRIOR D.REFERENCED_OWNER = D.OWNER
               AND PRIOR  D.REFERENCED_NAME =D.NAME
) V
INNER JOIN DBA_OBJECTS O ON V.REFERENCED_OWNER =O.OWNER AND V.REFERENCED_NAME=O.OBJECT_NAME
ORDER BY V.ROW_LEVEL,V.OBJECT_OWNER,V.OBJECT_NAME;

这个脚本虽然展示了视图依赖对象的关系，但是感觉还是不够直观，我想将视图依赖的对象用>>这种链条关系给直观的展示出来，所以有了下面脚本。

--==================================================================================================================
--        ScriptName            :            get_view_referenced_objects.sql
--        Author                :            潇湘隐者    
--        CreateDate            :            2021-06-15
--        Description           :            查看视图引用的对象
--        Note                  :            此脚本get_view_referenced_objects.sql的第二个版本。
/*-*****************************************************************************************************************
        Parameters              :                                    参数说明
********************************************************************************************************************
            &OWNER              :            视图的OWNER
            &VIEW_NAME          :            视图的名称
********************************************************************************************************************
   Modified Date    Modified User     Version                 Modified Reason
********************************************************************************************************************
    2018-08-03        潇湘隐者         V01.00.00        新建该脚本。
*******************************************************************************************************************/

SELECT LEVEL                AS ROW_LEVEL
      ,D.OWNER              AS OBJECT_OWNER
      ,D.NAME               AS OBJECT_NAME
      ,D.TYPE               AS OBJECT_TYPE
      ,PRIOR(D.OWNER ||'.' || D.NAME) 
                            AS PARNET_OBJECT_NAME
      ,sys_connect_by_path(D.OWNER ||'.' ||D.NAME,'>>') 
       || '>>' || D.REFERENCED_OWNER || '.' ||  D.REFERENCED_NAME AS NESTED_VIEW_PATH
      ,D.REFERENCED_OWNER   AS REFERENCED_OWNER
      ,D.REFERENCED_NAME    AS REFERENCED_NAME
FROM DBA_DEPENDENCIES D 
START WITH D.OWNER=UPPER('&OWNER') AND D.NAME =UPPER('&VIEW_NAME') AND D.TYPE='VIEW'
CONNECT BY NOCYCLE PRIOR D.REFERENCED_OWNER = D.OWNER
               AND PRIOR D.REFERENCED_NAME =D.NAME
ORDER BY ROW_LEVEL, OBJECT_OWNER, OBJECT_NAME;

其实我写这个SQL的目的是将数据库中嵌套超过1层的视图给找出来，嵌套层数过多的视图对SQL性能来说往往是一个灾难，而且是仅仅灾难的开始，而且嵌套视图也是SQL性能优化中一个很头疼的问题。如果你能杜绝这种现象，最好将其扼杀在萌芽状态，如果你无法杜绝的话，性能优化中，你会经常与其打交道。那么问题来了，一个数据库里面如果存在视图嵌套视图或者说嵌套超过2层的视图，我们如何将其找出来呢？ 这里分析一个我写的脚本，简单测试过了，应该没有什么问题，如有问题，欢迎反馈指教。

注意：这个SQL只是找出视图的嵌套关系，如果要找出嵌套2层或超过2层的视图，加上一个查询条件即可。这里不做展开赘述了

--==================================================================================================================
--        ScriptName            :            get_netsted_view_level.sql
--        Author                :            潇湘隐者    
--        CreateDate            :            2023-06-01
--        Description           :            查看/找出数据库视图嵌套视图信息(例如嵌套层数/嵌套层次关系)
--        Note                  :            这里使用了一个中间表T_OBJECT_DEPENDENCIES存储数据，主要原因是因为直接查询DBA_DEPENDENCIES
--                                           的SQL性能非常差.
/*-*****************************************************************************************************************
        Parameters              :                                    参数说明
********************************************************************************************************************
                                :            无参数
********************************************************************************************************************
   Modified Date    Modified User     Version                 Modified Reason
********************************************************************************************************************
    2023-06-01        潇湘隐者         V01.00.00        新建该脚本。
*******************************************************************************************************************/
DROP TABLE T_OBJECT_DEPENDENCIES PURGE;
CREATE TABLE T_OBJECT_DEPENDENCIES
AS
SELECT * FROM DBA_DEPENDENCIES 
WHERE OWNER NOT IN ('SYS','SYSTEM', 'OLAPSYS', 'PUBLIC', 'CTXSYS', 'DVSYS','APEX_040200', 'AUDSYS'
                    ,'WMSYS','XDB', 'LBACSYS','LBACSYS', 'MDSYS', 'IC_ADMIN','GSMADMIN_INTERNAL', 'DBSNMP'
                   );


WITH NESTED_VIEW  AS 
(
SELECT LEVEL                AS ROW_LEVEL
      ,D.OWNER              AS OBJECT_OWNER
      ,D.NAME               AS OBJECT_NAME
      ,D.TYPE               AS OBJECT_TYPE
      ,PRIOR(D.OWNER ||'.' || D.NAME) 
                            AS PARNET_OBJECT_NAME
      ,sys_connect_by_path(D.OWNER ||'.' ||D.NAME,'>') AS NestViewPath
      ,D.REFERENCED_OWNER   AS REFERENCED_OWNER
      ,D.REFERENCED_NAME    AS REFERENCED_NAME
FROM T_OBJECT_DEPENDENCIES D 
START WITH  D.TYPE='VIEW' 
CONNECT BY NOCYCLE PRIOR D.REFERENCED_OWNER =D.OWNER
               AND PRIOR D.REFERENCED_NAME =D.NAME
)
SELECT DISTINCT SUBSTR(NestViewPath, 2, DECODE(INSTR(NestViewPath, '>',1,2), 0,  LENGTH(NestViewPath)-1, INSTR(NestViewPath, '>',1,2)-2)) AS PARENT_OBJ_NAME,
       NestViewPath ||'>' ||REFERENCED_OWNER ||'.' || REFERENCED_NAME AS NestViewPath,
       REFERENCED_NAME, ROW_LEVEL   
FROM  NESTED_VIEW
ORDER BY 1;
DROP TABLE T_OBJECT_DEPENDENCIES PURGE;