请教关于connect by的问题

baolei

hi ML

遇到关于树型查询的性能问题，附件ash报告，
关于树型查询，其中的执行计划 使用hint /+ no_connect_by_filtering */ 和不使用差别为什么如此之大，想了解2个执行计划，到底哪里是不要filtering ，


select  count(*)
  2    from JS4AZH50.sm_organization org
  3   where org.parent_user_group_id != 0
  4     and org.ACTIVE_FLAG = '0'
  5     and org.user_group_name like '%a%'
  6   start with org.id in (select t.id
  7                           from JS4AZH50.sm_organization t
  8                          where exists (select u.id
  9                                   from JS4AZH50.sm_user_visible u
10                                  where u.oid = t.id
11                                    and u.userid = 1132054))
12  connect by prior org.id = org.parent_user_group_id;
Elapsed: 00:00:00.67
Execution Plan
----------------------------------------------------------
Plan hash value: 3333574043
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                               |     1 |    39 |    44   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                    |                               |     1 |    39 |            |          |
|*  2 |   FILTER                           |                               |       |       |            |          |
|*  3 |    CONNECT BY WITH FILTERING       |                               |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID    | SM_ORGANIZATION               |       |       |            |          |
|   5 |      NESTED LOOPS                  |                               |     1 |    25 |     3  (34)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                               |     1 |    18 |     3  (34)| 00:00:01 |
|   7 |        SORT UNIQUE                 |                               |     1 |    11 |     2   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| SM_USER_VISIBLE               |     1 |    11 |     2   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | IDX_SM_USER_V_USERID          |     1 |       |     1   (0)| 00:00:01 |
|* 10 |        INDEX UNIQUE SCAN           | PK_SM_ORGANIZATION            |     1 |     7 |     0   (0)| 00:00:01 |
|* 11 |       INDEX UNIQUE SCAN            | PK_SM_ORGANIZATION            |     1 |     7 |     0   (0)| 00:00:01 |
|  12 |     NESTED LOOPS                   |                               |       |       |            |          |
|  13 |      CONNECT BY PUMP               |                               |       |       |            |          |
|  14 |      TABLE ACCESS BY INDEX ROWID   | SM_ORGANIZATION               |    56 |  2184 |    44   (0)| 00:00:01 |
|* 15 |       INDEX RANGE SCAN             | IDX_SM_ORGANIZATION_PARENT_UG |    56 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORG"."PARENT_USER_GROUP_ID"<>0 AND "ORG"."ACTIVE_FLAG"=0 AND "ORG"."USER_GROUP_NAME" LIKE
              '%arrtxqwt%')
   3 - access("ORG"."PARENT_USER_GROUP_ID"=PRIOR "ORG"."ID")
   9 - access("U"."USERID"=1132054)
  10 - access("U"."OID"="T"."ID")
  11 - access("ORG"."ID"="T"."ID")
  15 - access("ORG"."PARENT_USER_GROUP_ID"=PRIOR "ORG"."ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      96111  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>
SQL> select /*+ no_connect_by_filtering*/count(*)
  2    from JS4AZH50.sm_organization org
  3   where org.parent_user_group_id != 0
  4     and org.ACTIVE_FLAG = '0'
  5     and org.user_group_name like '%a%'
  6   start with org.id in (select t.id
  7                           from JS4AZH50.sm_organization t
  8                          where exists (select u.id
  9                                   from JS4AZH50.sm_user_visible u
10                                  where u.oid = t.id
11                                    and u.userid = 1132054))
12  connect by prior org.id = org.parent_user_group_id;
         6
Elapsed: 00:00:00.30
Execution Plan
----------------------------------------------------------
Plan hash value: 4178681623
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                      |     1 |    39 |   535   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE                    |                      |     1 |    39 |            |          |
|*  2 |   FILTER                           |                      |       |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING    |                      |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID    | SM_ORGANIZATION      |       |       |            |          |
|   5 |      NESTED LOOPS                  |                      |     1 |    25 |     3  (34)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                      |     1 |    18 |     3  (34)| 00:00:01 |
|   7 |        SORT UNIQUE                 |                      |     1 |    11 |     2   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| SM_USER_VISIBLE      |     1 |    11 |     2   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | IDX_SM_USER_V_USERID |     1 |       |     1   (0)| 00:00:01 |
|* 10 |        INDEX UNIQUE SCAN           | PK_SM_ORGANIZATION   |     1 |     7 |     0   (0)| 00:00:01 |
|* 11 |       INDEX UNIQUE SCAN            | PK_SM_ORGANIZATION   |     1 |     7 |     0   (0)| 00:00:01 |
|  12 |     TABLE ACCESS FULL              | SM_ORGANIZATION      | 54660 |  2081K|   535   (1)| 00:00:07 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORG"."PARENT_USER_GROUP_ID"<>0 AND "ORG"."ACTIVE_FLAG"=0 AND
              "ORG"."USER_GROUP_NAME" LIKE '%a%')
   3 - access("ORG"."PARENT_USER_GROUP_ID"=PRIOR "ORG"."ID")
   9 - access("U"."USERID"=1132054)
  10 - access("U"."OID"="T"."ID")
  11 - access("ORG"."ID"="T"."ID")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2390  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed