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改写后走的是排序合并连接,查询耗时又恢复导毫秒级别。

 

posted @   阿西吧li  阅读(346)  评论(0编辑  收藏  举报
编辑推荐:
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行:
· 开源Multi-agent AI智能体框架aevatar.ai,欢迎大家贡献代码
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
点击右上角即可分享
微信分享提示