时间：2021-07-01 10:21:17 帮助过：13人阅读
实际使用的执行计划:
而不会采用自适应计划（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之后的数量，所以只能认为是很大的量
--
有一种方式就是，就是使用提示来解决：
- <span style="color: #0000ff">SELECT</span> <span style="color: #008080">/*</span><span style="color: #008080">+ no_merge(x) use_nl(a x) </span><span style="color: #008080">*/</span><span style="color: #000000">
- A.CI, A.ENBAJ02 </span><span style="color: #0000ff">AS</span><span style="color: #000000"> CELL_NAME
- </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> TDL_CM_CELL A,
- (</span><span style="color: #0000ff">select</span><span style="color: #000000"> s.city_name, s.region_name
- </span><span style="color: #0000ff">from</span><span style="color: #000000"> T_ORG_CELL_SCOPE S
- </span><span style="color: #0000ff">WHERE</span> (S.ORG_ID) <span style="color: #808080">IN</span><span style="color: #000000">
- (</span><span style="color: #0000ff">SELECT</span><span style="color: #000000"> ID
- </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> T_ORG O
- START </span><span style="color: #0000ff">WITH</span> ID <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">101021003</span> <span style="color: #008080">--</span><span style="color: #008080">1010210 </span>
- <span style="color: #008080">--</span><span style="color: #008080">START WITH ID=1</span>
- CONNECT <span style="color: #0000ff">BY</span> PARENT_ID <span style="color: #808080">=</span><span style="color: #000000"> PRIOR ID)
- ) x
- </span><span style="color: #0000ff">where</span> x.REGION_NAME <span style="color: #808080">=</span><span style="color: #000000"> A.REGION_NAME
- </span><span style="color: #808080">AND</span> x.CITY_NAME <span style="color: #808080">=</span> 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语句的优化
标签：format 原因 nba 应用 目标 font log com time