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;