Oracle中如何優(yōu)化connect by語(yǔ)句

Oracle中如何優(yōu)化connect by語(yǔ)句，針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

執(zhí)行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)

實(shí)際使用的執(zhí)行計(jì)劃:

而不會(huì)采用自適應(yīng)計(jì)劃(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無(wú)法知道connect by之后的數(shù)量，所以只能認(rèn)為是很大的量

--

有一種方式就是，就是使用提示來(lái)解決：

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

這樣計(jì)劃就是：

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

關(guān)于Oracle中如何優(yōu)化connect by語(yǔ)句問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開(kāi)，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。