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;