共計 3670 個字符，預計需要花費 10 分鐘才能閱讀完成。
Oracle 中如何優化 connect by 語句，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。
執行 SQL:
SELECT A.CI, A.ENBAJ02 AS CELL_NAME FROM TDL_CM_CELL A, T_ORG_CELL_SCOPE S WHERE S.REGION_NAME = A.REGION_NAME AND S.CITY_NAME = A.CITY_NAME AND (S.ORG_ID) IN (SELECT ID FROM T_ORG O START WITH ID = 101021003 --1010210 --START WITH ID=1 CONNECT BY PARENT_ID = PRIOR ID)實際使用的執行計劃:
而不會采用自適應計劃 (adaptive plan)：
Plan Hash Value : 2596385940 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2622 | 228114 | 227 | 00:00:01 | | 1 | NESTED LOOPS | | 2622 | 228114 | 227 | 00:00:01 | | 2 | NESTED LOOPS | | 2622 | 228114 | 227 | 00:00:01 | | * 3 | HASH JOIN | | 1 | 31 | 7 | 00:00:01 | | 4 | VIEW | VW_NSO_1 | 1 | 13 | 4 | 00:00:01 | | 5 | HASH UNIQUE | | 1 | 20 | 4 | 00:00:01 | | * 6 | CONNECT BY NO FILTERING WITH SW (UNIQUE) | | | | | | | 7 | TABLE ACCESS FULL | T_ORG | 75 | 825 | 3 | 00:00:01 | | 8 | TABLE ACCESS FULL | T_ORG_CELL_SCOPE | 85 | 1530 | 3 | 00:00:01 | | * 9 | INDEX RANGE SCAN | IDX_TDL_CM_CELL_SCOPE | 257 | | 8 | 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID | TDL_CM_CELL | 2313 | 129528 | 220 | 00:00:01 | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 3 - access(S . ORG_ID = ID) * 6 - access(PARENT_ID =PRIOR ID) * 6 - filter(ID =101021003) * 9 - access(S . REGION_NAME = A . REGION_NAME AND S . CITY_NAME = A . CITY_NAME) Notes ----- - This is an adaptive plan原因在于，oracle 無法知道 connect by 之后的數量，所以只能認為是很大的量
—
有一種方式就是，就是使用提示來解決：
SELECT /*+ no_merge(x) use_nl(a x) */ A.CI, A.ENBAJ02 AS CELL_NAME FROM TDL_CM_CELL A, (select s.city_name, s.region_name from T_ORG_CELL_SCOPE S WHERE (S.ORG_ID) IN (SELECT ID FROM T_ORG O START WITH ID = 101021003 --1010210 --START WITH ID=1 CONNECT BY PARENT_ID = PRIOR ID) ) x where x.REGION_NAME = A.REGION_NAME AND x.CITY_NAME = A.CITY_NAME這樣計劃就是：
Plan Hash Value : 37846894 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2313 | 277560 | 227 | 00:00:01 | | 1 | NESTED LOOPS | | 2313 | 277560 | 227 | 00:00:01 | | 2 | NESTED LOOPS | | 2313 | 277560 | 227 | 00:00:01 | | 3 | VIEW | | 1 | 64 | 7 | 00:00:01 | | * 4 | HASH JOIN | | 1 | 31 | 7 | 00:00:01 | | 5 | VIEW | VW_NSO_1 | 1 | 13 | 4 | 00:00:01 | | 6 | HASH UNIQUE | | 1 | 20 | 4 | 00:00:01 | | * 7 | CONNECT BY NO FILTERING WITH SW (UNIQUE) | | | | | | | 8 | TABLE ACCESS FULL | T_ORG | 75 | 825 | 3 | 00:00:01 | | 9 | TABLE ACCESS FULL | T_ORG_CELL_SCOPE | 85 | 1530 | 3 | 00:00:01 | | * 10 | INDEX RANGE SCAN | IDX_TDL_CM_CELL_SCOPE | 257 | | 8 | 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | TDL_CM_CELL | 2313 | 129528 | 220 | 00:00:01 | --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 4 - access(S . ORG_ID = ID) * 7 - access(PARENT_ID =PRIOR ID) * 7 - filter(ID =101021003) * 10 - access(X . REGION_NAME = A . REGION_NAME AND X . CITY_NAME = A . CITY_NAME)關于 Oracle 中如何優化 connect by 語句問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注丸趣 TV 行業資訊頻道了解更多相關知識。
正文完
