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;
