代码改变世界

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

  潇湘隐者  阅读(448)  评论(0编辑  收藏  举报

之前写过一篇文章“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;
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
历史上的今天:
2020-06-13 AutoMySQLBackup 3.0在MySQL 5.7中的问题修复
2019-06-13 ApexSQL Log中的Redo Script跟原始SQL不一致问题
2017-06-13 SQL Server使用sys.master_files计算tempdb大小不正确
2017-06-13 MySQL 如何查看表的存储引擎
2016-06-13 SQL Server 关于列的权限控制
2014-06-13 Linux系统查看系统是32位还是64位方法总结
点击右上角即可分享
微信分享提示