oracle中connect by語句的最佳化

標籤：format   原因   nba   應用   目標   font   log   com   time   

很多應用中都會有類似組織機構的表，組織機構的表又通常是典型的階層（沒有迴圈節點）。於是通過組織控制資料許可權的時候，許多人都喜歡通過connect by獲得組織資訊，然後再過濾目標資料。

在有些情況下，這樣寫並沒有什麼問題，但有些情況下，這個就是一個大問題。

歸根結底，這是connect by特性導致的，oracle無法知道connect by之後到底返回多少資料，所以有可能採取一些你所不期望的演算法，結果自然不是你所期望的---非常慢。

下面，我就討論在12.1.0.2中如果遇到這樣的語句應該如何處理。

為了很好理解，我做了3表：

 

執行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")

 

如果一個應用的start id可能是一個很大的範圍，如果強制使用提示，也會出現問題，所以如果有這樣的應用，可以考慮使用oracle 12c的adaptive特性。

如果不行，就必須把不同範圍的查詢，定義為不同的功能提交給使用者。

 

oracle中connect by語句的最佳化