Shulou(Shulou.com)05/31 Report--

How to optimize the connect by statement in Oracle, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

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

Actual execution plan used:

Instead of using adaptive planning (adaptive plan):

Plan Hash Value: 2596385940- -| Id | Operation | Name | Rows | Bytes | Cost | Time |- -| 0 | SELECT STATEMENT | | 2622 | 228114 | 228114 | 00:00:01 | | 1 | NESTED LOOPS | | | 2622 | 228114 | 00:00:01 | | 2 | NESTED LOOPS | | 2622 | 228114 | 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

The reason is that oracle can't know the quantity after connect by, so it can only be considered as a large quantity.

--

One way is to use tips to solve the problem:

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

So the plan is:

Plan Hash Value: 37846894- -| Id | Operation | Name | Rows | Bytes | Cost | Time |- -| 0 | SELECT STATEMENT | | 2313 | 277560 | 277560 | 00:00:01 | 1 | NESTED LOOPS | | | 2313 | 277560 | 00:00:01 | | 2 | NESTED LOOPS | | 2313 | 277560 | 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") this is the answer to the question on how to optimize connect by statements in Oracle. I hope the above content can help you to a certain extent, if you still have a lot of doubts to be solved, you can follow the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.