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, 2DECODE(INSTR(NestViewPath, '>',1,2), 0,  LENGTH(NestViewPath)-1INSTR(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;
扫描上面二维码关注我
如果你真心觉得文章写得不错,而且对你有所帮助,那就不妨帮忙“推荐"一下,您的“推荐”和”打赏“将是我最大的写作动力!
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.