sql优化案例

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133

explain plan for  select  '33101' org_no, nvl(count(*), 0) outNum    from CPSS.k_id_staff_tqjl_v a   where a.pda_status_code = '002'     and a.gds_no in (SELECT b.dim_value                        FROM CPSS.k_ic_dim_value b                       WHERE b.level_id = '5'                         AND b.dim_code = 'org'                       START WITH b.dim_value = '33101'                      CONNECT BY PRIOR b.dim_value = b.p_dim_id)     and not exists (         select 1            from CPSS.K_PDA_HEARTBEAT  k           where date_str = '20200714'             and GMT_CREATE >= sysdate - 15 / 24 / 60             and k.user_no = a.auth_user_no             and k.org_no = a.gds_no             );     Plan hash value: 1785689430                                                                                                                                                                                                                                                                 --------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                                        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                                 |                          |     1 |    94 |    39   (3)| 00:00:01 |       |       | |   1 |  SORT AGGREGATE                                  |                          |     1 |    94 |            |          |       |       | |*  2 |   FILTER                                         |                          |       |       |            |          |       |       | |*  3 |    HASH JOIN                                     |                          |     2 |   188 |    37   (3)| 00:00:01 |       |       | |   4 |     NESTED LOOPS                                 |                          |    53 |  3445 |    36   (3)| 00:00:01 |       |       | |   5 |      NESTED LOOPS                                |                          |   294 |  3445 |    36   (3)| 00:00:01 |       |       | |   6 |       VIEW                                       | VW_NSO_1                 |     6 |   204 |    14   (8)| 00:00:01 |       |       | |   7 |        HASH UNIQUE                               |                          |     6 |   618 |    14   (8)| 00:00:01 |       |       | |*  8 |         FILTER                                   |                          |       |       |            |          |       |       | |*  9 |          CONNECT BY NO FILTERING WITH SW (UNIQUE)|                          |       |       |            |          |       |       | |  10 |           TABLE ACCESS FULL                      | K_IC_DIM_VALUE           |  2579 |    98K|    13   (0)| 00:00:01 |       |       | |* 11 |       INDEX RANGE SCAN                           | IDX_K_ID_STAFF_N1        |    49 |       |     1   (0)| 00:00:01 |       |       | |* 12 |      TABLE ACCESS BY INDEX ROWID                 | K_ID_STAFF               |     9 |   279 |     4   (0)| 00:00:01 |       |       | |  13 |     TABLE ACCESS BY INDEX ROWID                  | K_IC_DIM_VALUE           |    96 |  2784 |     1   (0)| 00:00:01 |       |       | |* 14 |      INDEX RANGE SCAN                            | IDX_K_IC_DIM_VALUE_N1    |    96 |       |     1   (0)| 00:00:01 |       |       | |  15 |    PARTITION RANGE SINGLE                        |                          |     1 |    37 |     1   (0)| 00:00:01 |     8 |     8 | |* 16 |     TABLE ACCESS BY LOCAL INDEX ROWID            | K_PDA_HEARTBEAT          |     1 |    37 |     1   (0)| 00:00:01 |     8 |     8 | |* 17 |      INDEX RANGE SCAN                            | IDX_GMT_CREATE_USER_ORG2 |     1 |       |     1   (0)| 00:00:01 |     8 |     8 | |* 18 |    COUNT STOPKEY                                 |                          |       |       |            |          |       |       | |  19 |     TABLE ACCESS BY INDEX ROWID                  | P_PDA_DEVICE             |     2 |    20 |     1   (0)| 00:00:01 |       |       | |* 20 |      INDEX RANGE SCAN                            | IDX_P_PDA_DEVICE_N1      |     1 |       |     1   (0)| 00:00:01 |       |       | ---------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                Predicate Information (identified by operation id):                                                                                          ---------------------------------------------------                                                                                                                                                                                                                                            2 - filter( NOT EXISTS (SELECT 0 FROM "CPSS"."K_PDA_HEARTBEAT" "K" WHERE "GMT_CREATE" IS NOT NULL AND                                                   "GMT_CREATE">=SYSDATE@!-.0104166666666666666666666666666666666667 AND "DATE_STR"='20200714' AND "K"."USER_NO"=:B1 AND                        "K"."ORG_NO"=:B2) AND  (SELECT "D"."STATUS_CODE" FROM CPSS."P_PDA_DEVICE" "D" WHERE ROWNUM=1 AND "D"."FETCHER_NO"=:B3)='002')     3 - access("S"."ORG_NO"="D"."DIM_VALUE")                                                                                                     8 - filter("B"."LEVEL_ID"=5 AND "B"."DIM_CODE"='org')                                                                                        9 - access("B"."P_DIM_ID"=PRIOR "B"."DIM_VALUE")                                                                                                 filter("B"."DIM_VALUE"='33101')                                                                                                         11 - access("S"."ORG_NO"="DIM_VALUE")                                                                                                        12 - filter("S"."POST"='17' AND "S"."WORK_MODE"='01' AND "S"."STAFF_STATE"='01' AND "S"."DATA_OPER_TYPE"<>'D')                               14 - access("D"."DIM_CODE"='org' AND "D"."LEVEL_ID"=5)                                                                                       16 - filter("DATE_STR"='20200714')                                                                                                           17 - access("GMT_CREATE">=SYSDATE@!-.0104166666666666666666666666666666666667 AND "K"."USER_NO"=:B1 AND "K"."ORG_NO"=:B2 AND                             "GMT_CREATE" IS NOT NULL)                                                                                                             filter("K"."USER_NO"=:B1 AND "K"."ORG_NO"=:B2)                                                                                          18 - filter(ROWNUM=1)                                                                                                                        20 - access("D"."FETCHER_NO"=:B1)     <br>   explain plan for select                                                                                                                                            '33101' org_no, nvl(count(*), 0) outNum                                     from CPSS.k_id_staff_tqjl_v a                                             where a.pda_status_code = '002'                                              and a.gds_no in (SELECT b.dim_value                                                           FROM CPSS.k_ic_dim_value b                                                WHERE b.level_id = '5'                                                       AND b.dim_code = 'org'                                                   START WITH b.dim_value = '33101'                                          CONNECT BY PRIOR b.dim_value = b.p_dim_id)                and (auth_user_no,gds_no) not in (select/*+qb_name(zz)*/ user_no,org_no            from CPSS.K_PDA_HEARTBEAT  k                                              where date_str = '20200714'                                                  and GMT_CREATE >= sysdate - 15 / 24 / 60);                      Plan hash value: 3515795332                                                                                                                                                                                                                                                                                  ----------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                                   |                          |     1 |   131 |    44   (5)| 00:00:01 |       |       | |   1 |  SORT AGGREGATE                                    |                          |     1 |   131 |            |          |       |       | |*  2 |   FILTER                                           |                          |       |       |            |          |       |       | |*  3 |    HASH JOIN                                       |                          |     9 |  1179 |    39   (6)| 00:00:01 |       |       | |   4 |     MERGE JOIN ANTI NA                             |                          |    53 |  5406 |    38   (6)| 00:00:01 |       |       | |   5 |      SORT JOIN                                     |                          |    53 |  3445 |    36   (3)| 00:00:01 |       |       | |   6 |       NESTED LOOPS                                 |                          |    53 |  3445 |    36   (3)| 00:00:01 |       |       | |   7 |        NESTED LOOPS                                |                          |   294 |  3445 |    36   (3)| 00:00:01 |       |       | |   8 |         VIEW                                       | VW_NSO_1                 |     6 |   204 |    14   (8)| 00:00:01 |       |       | |   9 |          HASH UNIQUE                               |                          |     6 |   618 |    14   (8)| 00:00:01 |       |       | |* 10 |           FILTER                                   |                          |       |       |            |          |       |       | |* 11 |            CONNECT BY NO FILTERING WITH SW (UNIQUE)|                          |       |       |            |          |       |       | |  12 |             TABLE ACCESS FULL                      | K_IC_DIM_VALUE           |  2579 |    98K|    13   (0)| 00:00:01 |       |       | |* 13 |         INDEX RANGE SCAN                           | IDX_K_ID_STAFF_N1        |    49 |       |     1   (0)| 00:00:01 |       |       | |* 14 |        TABLE ACCESS BY INDEX ROWID                 | K_ID_STAFF               |     9 |   279 |     4   (0)| 00:00:01 |       |       | |* 15 |      SORT UNIQUE                                   |                          |     2 |    74 |     3  (34)| 00:00:01 |       |       | |  16 |       PARTITION RANGE SINGLE                       |                          |     2 |    74 |     2   (0)| 00:00:01 |     8 |     8 | |* 17 |        TABLE ACCESS BY LOCAL INDEX ROWID           | K_PDA_HEARTBEAT          |     2 |    74 |     2   (0)| 00:00:01 |     8 |     8 | |* 18 |         INDEX RANGE SCAN                           | IDX_GMT_CREATE_USER_ORG2 |     7 |       |     1   (0)| 00:00:01 |     8 |     8 | |  19 |     TABLE ACCESS BY INDEX ROWID                    | K_IC_DIM_VALUE           |    96 |  2784 |     1   (0)| 00:00:01 |       |       | |* 20 |      INDEX RANGE SCAN                              | IDX_K_IC_DIM_VALUE_N1    |    96 |       |     1   (0)| 00:00:01 |       |       | |* 21 |    COUNT STOPKEY                                   |                          |       |       |            |          |       |       | |  22 |     TABLE ACCESS BY INDEX ROWID                    | P_PDA_DEVICE             |     2 |    20 |     1   (0)| 00:00:01 |       |       | |* 23 |      INDEX RANGE SCAN                              | IDX_P_PDA_DEVICE_N1      |     1 |       |     1   (0)| 00:00:01 |       |       | -----------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                  Predicate Information (identified by operation id):                                                                                            ---------------------------------------------------                                                                                                                                                                                                                                                2 - filter( (SELECT "D"."STATUS_CODE" FROM CPSS."P_PDA_DEVICE" "D" WHERE ROWNUM=1 AND "D"."FETCHER_NO"=:B1)='002')                             3 - access("S"."ORG_NO"="D"."DIM_VALUE")                                                                                                      10 - filter("B"."LEVEL_ID"=5 AND "B"."DIM_CODE"='org')                                                                                         11 - access("B"."P_DIM_ID"=PRIOR "B"."DIM_VALUE")                                                                                                   filter("B"."DIM_VALUE"='33101')                                                                                                           13 - access("S"."ORG_NO"="DIM_VALUE")                                                                                                          14 - filter("S"."POST"='17' AND "S"."WORK_MODE"='01' AND "S"."STAFF_STATE"='01' AND "S"."DATA_OPER_TYPE"<>'D')                                 15 - access(INTERNAL_FUNCTION("S"."ORG_NO")=INTERNAL_FUNCTION("ORG_NO") AND                                                                                INTERNAL_FUNCTION("S"."AUTH_USER_NO")=INTERNAL_FUNCTION("USER_NO"))                                                                     filter(INTERNAL_FUNCTION("S"."ORG_NO")=INTERNAL_FUNCTION("ORG_NO") AND                                                                                INTERNAL_FUNCTION("S"."AUTH_USER_NO")=INTERNAL_FUNCTION("USER_NO"))                                                                17 - filter("DATE_STR"='20200714')                                                                                                             18 - access("GMT_CREATE">=SYSDATE@!-.0104166666666666666666666666666666666667 AND "GMT_CREATE" IS NOT NULL)                                    20 - access("D"."DIM_CODE"='org' AND "D"."LEVEL_ID"=5)                                                                                         21 - filter(ROWNUM=1)                                                                                                                          23 - access("D"."FETCHER_NO"=:B1)

　　

实际生产环境中常常能见到执行计划中出现filter

filter有哪些坏处：

1.出现后驱动表固定，优化器不能选择合适的驱动表

2.filter的表关联方式类似与nesloop，对于驱动表数据集比较大的情况下，不适合用该关联方式

 

上述sql

?

1 2 3 4 5 6 7 8 9 10

select                                                                                                                                            '33101' org_no, nvl(count(*), 0) outNum                                     from CPSS.k_id_staff_tqjl_v a                                             where a.pda_status_code = '002'                                              and a.gds_no in (SELECT b.dim_value                                                           FROM CPSS.k_ic_dim_value b                                                WHERE b.level_id = '5'                                                       AND b.dim_code = 'org'                                                   START WITH b.dim_value = '33101'                                          CONNECT BY PRIOR b.dim_value = b.p_dim_id) <br>改sql执行效率ok，返回结果集有1万多（毫秒级别）。<br><br>加入not exists后，查询效率大幅度降低，需要三秒多。<br>执行计划我们看到走的是filter表关联方式<br><br>用not in改写后走的是排序合并连接，查询耗时又恢复导毫秒级别。