Oracle中connect by语句的优化

数据库 Oracle
很多应用中都会有类似组织机构的表,组织机构的表又通常是典型的层次结构(没有循环节点)。于是通过组织控制数据权限的时候,许多人都喜欢通过connect by获得组织信息,然后再过滤目标数据。

[[199305]]

很多应用中都会有类似组织机构的表,组织机构的表又通常是典型的层次结构(没有循环节点)。于是通过组织控制数据权限的时候,许多人都喜欢通过connect by获得组织信息,然后再过滤目标数据。

在有些情况下,这样写并没有什么问题,但有些情况下,这个就是一个大问题。

归根结底,这是connect by特性导致的,Oracle无法知道connect by之后到底返回多少数据,所以有可能采取一些你所不期望的算法,结果自然不是你所期望的---非常慢。

下面,我就讨论在Oracle 12.1.0.2中如果遇到这样的语句应该如何处理。

为了很好理解,我做了3表:

执行SQL:

  1. SELECT A.CI, A.ENBAJ02 AS CELL_NAME 
  2.   FROM TDL_CM_CELL A, T_ORG_CELL_SCOPE S 
  3.  WHERE S.REGION_NAME = A.REGION_NAME 
  4.   AND S.CITY_NAME = A.CITY_NAME 
  5.   AND (S.ORG_ID) IN (SELECT ID 
  6.                         FROM T_ORG O 
  7.                       START WITH ID = 101021003 --1010210   
  8.                       --START WITH ID=1 
  9.                       CONNECT BY PARENT_ID = PRIOR ID) 

实际使用的执行计划:

而不会采用自适应计划(adaptive plan):

  1.  Plan Hash Value  : 2596385940  
  2.  
  3. ------------------------------------------------------------------------------------------------------------------- 
  4. | Id  | Operation                                       | Name                  | Rows | Bytes  | Cost | Time     | 
  5. ------------------------------------------------------------------------------------------------------------------- 
  6. |   0 | SELECT STATEMENT                                |                       | 2622 | 228114 |  227 | 00:00:01 | 
  7. |   1 |   NESTED LOOPS                                  |                       | 2622 | 228114 |  227 | 00:00:01 | 
  8. |   2 |    NESTED LOOPS                                 |                       | 2622 | 228114 |  227 | 00:00:01 | 
  9. | * 3 |     HASH JOIN                                   |                       |    1 |     31 |    7 | 00:00:01 | 
  10. |   4 |      VIEW                                       | VW_NSO_1              |    1 |     13 |    4 | 00:00:01 | 
  11. |   5 |       HASH UNIQUE                               |                       |    1 |     20 |    4 | 00:00:01 | 
  12. | * 6 |        CONNECT BY NO FILTERING WITH SW (UNIQUE) |                       |      |        |      |          | 
  13. |   7 |         TABLE ACCESS FULL                       | T_ORG                 |   75 |    825 |    3 | 00:00:01 | 
  14. |   8 |      TABLE ACCESS FULL                          | T_ORG_CELL_SCOPE      |   85 |   1530 |    3 | 00:00:01 | 
  15. | * 9 |     INDEX RANGE SCAN                            | IDX_TDL_CM_CELL_SCOPE |  257 |        |    8 | 00:00:01 | 
  16. |  10 |    TABLE ACCESS BY INDEX ROWID                  | TDL_CM_CELL           | 2313 | 129528 |  220 | 00:00:01 | 
  17. ------------------------------------------------------------------------------------------------------------------- 
  18.  
  19. Predicate Information (identified by operation id): 
  20. ------------------------------------------ 
  21. * 3 - access("S"."ORG_ID"="ID"
  22. * 6 - access("PARENT_ID"=PRIOR "ID"
  23. * 6 - filter("ID"=101021003) 
  24. * 9 - access("S"."REGION_NAME"="A"."REGION_NAME" AND "S"."CITY_NAME"="A"."CITY_NAME"
  25.  
  26.  
  27. Notes 
  28. ----- 
  29. - This is an adaptive plan 

原因在于,oracle无法知道connect by之后的数量,所以只能认为是很大的量

--

有一种方式就是,就是使用提示来解决:

  1. SELECT /*+ no_merge(x) use_nl(a x) */ 
  2.   A.CI, A.ENBAJ02 AS CELL_NAME 
  3.    FROM TDL_CM_CELL A, 
  4.         (select s.city_name, s.region_name 
  5.            from T_ORG_CELL_SCOPE S 
  6.           WHERE (S.ORG_ID) IN 
  7.                 (SELECT ID 
  8.                    FROM T_ORG O 
  9.                   START WITH ID = 101021003 --1010210   
  10.                  --START WITH ID=1 
  11.                  CONNECT BY PARENT_ID = PRIOR ID) 
  12.           
  13.          ) x 
  14.   where x.REGION_NAME = A.REGION_NAME 
  15.     AND x.CITY_NAME = A.CITY_NAME 

这样计划就是:

  1. Plan Hash Value  : 37846894  
  2.  
  3. --------------------------------------------------------------------------------------------------------------------- 
  4. | Id   | Operation                                        | Name                  | Rows | Bytes  | Cost | Time     | 
  5. --------------------------------------------------------------------------------------------------------------------- 
  6. |    0 | SELECT STATEMENT                                 |                       | 2313 | 277560 |  227 | 00:00:01 | 
  7. |    1 |   NESTED LOOPS                                   |                       | 2313 | 277560 |  227 | 00:00:01 | 
  8. |    2 |    NESTED LOOPS                                  |                       | 2313 | 277560 |  227 | 00:00:01 | 
  9. |    3 |     VIEW                                         |                       |    1 |     64 |    7 | 00:00:01 | 
  10. |  * 4 |      HASH JOIN                                   |                       |    1 |     31 |    7 | 00:00:01 | 
  11. |    5 |       VIEW                                       | VW_NSO_1              |    1 |     13 |    4 | 00:00:01 | 
  12. |    6 |        HASH UNIQUE                               |                       |    1 |     20 |    4 | 00:00:01 | 
  13. |  * 7 |         CONNECT BY NO FILTERING WITH SW (UNIQUE) |                       |      |        |      |          | 
  14. |    8 |          TABLE ACCESS FULL                       | T_ORG                 |   75 |    825 |    3 | 00:00:01 | 
  15. |    9 |       TABLE ACCESS FULL                          | T_ORG_CELL_SCOPE      |   85 |   1530 |    3 | 00:00:01 | 
  16. | * 10 |     INDEX RANGE SCAN                             | IDX_TDL_CM_CELL_SCOPE |  257 |        |    8 | 00:00:01 | 
  17. |   11 |    TABLE ACCESS BY INDEX ROWID                   | TDL_CM_CELL           | 2313 | 129528 |  220 | 00:00:01 | 
  18. --------------------------------------------------------------------------------------------------------------------- 
  19.  
  20. Predicate Information (identified by operation id): 
  21. ------------------------------------------ 
  22. * 4 - access("S"."ORG_ID"="ID"
  23. * 7 - access("PARENT_ID"=PRIOR "ID"
  24. * 7 - filter("ID"=101021003) 
  25. * 10 - access("X"."REGION_NAME"="A"."REGION_NAME" AND "X"."CITY_NAME"="A"."CITY_NAME")  

如果一个应用的start id可能是一个很大的范围,如果强制使用提示,也会出现问题,所以如果有这样的应用,可以考虑使用oracle 12c的adaptive特性。

如果不行,就必须把不同范围的查询,定义为不同的功能提交给用户。

责任编辑:武晓燕 来源: Linux社区
相关推荐

2010-04-20 15:58:15

Oracle 语句

2010-04-13 15:04:16

Oracle优化

2011-05-20 15:59:06

Oracle存储Sql语句

2009-03-04 09:06:56

优化sqlOracle

2010-10-25 12:05:40

SYS_CONNECT

2010-04-12 10:53:07

Oracle SQL

2010-04-26 14:32:21

Oracle SQL

2010-10-28 14:02:00

oracle conn

2009-01-14 09:28:12

OracleSQL10g

2010-04-20 09:06:25

Oracle优化

2010-05-05 10:37:40

Oracle优化查询

2011-04-11 17:28:50

oracle存储select语句

2011-06-27 16:26:39

Oracle优化器RBOCBO

2010-04-13 15:14:31

Oracle优化

2010-10-27 15:03:47

Oracle with

2010-04-29 16:53:18

Oracle多表关联

2011-04-15 09:53:37

Oracle 10gSQL优化

2011-05-26 13:26:42

if

2010-10-27 11:25:13

ORACLE ROWN

2010-11-18 09:19:10

Oracle循环语句
点赞
收藏

51CTO技术栈公众号