sql优化案例(filter)

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

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) 
改sql执行效率ok,返回结果集有1万多(毫秒级别)。

加入not exists后,查询效率大幅度降低,需要三秒多。
执行计划我们看到走的是filter表关联方式

用not in改写后走的是排序合并连接,查询耗时又恢复导毫秒级别。

 

sql优化案例(filter)

上一篇:MySQL多表Update语句


下一篇:Redis-单数据库的实现