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 改写后走的是排序合并连接,查询耗时又恢复导毫秒级别。 |
分类:
SQL优化
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 开发者必知的日志记录最佳实践
· 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