在SQL Server数据库查询的过程中，通过对SQL语句的优化来提高SQL查询的性能。本文我们主要介绍了使用UNION代替OR提升查询性能的一个实例，首先我们先来看一下优化前的代码：
- SQL> set timing on
- SQL> set autotrace on
- SQL> select count(*) rowcount_lhy
- 2 from swgl_ddjbxx t
- 3 where t.fzgs_dm = '001085'
- 4 and (t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e' or exists
- 5 (select y.kh_id
- 6 from khgl_khywdlxx y
- 7 where y.kh_id = t.kh_id
- 8 and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e') or
- 9 t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e')
- 10 and t.xjbz = '9999'
- 11 and t.FROMNBGL1 = '0';
- SQL> set line 300
- SQL> /
- ROWCOUNT_LHY
- ------------
- 60
- 已用时间: 00: 00: 20.53
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 1217125969
- --------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 86 | 28048 (1)| 00:05:37 |
- | 1 | SORT AGGREGATE | | 1 | 86 | | |
- |* 2 | FILTER | | | | | |
- |* 3 | TABLE ACCESS FULL | SWGL_DDJBXX | 5926 | 497K| 28048 (1)| 00:05:37 |
- |* 4 | TABLE ACCESS BY INDEX ROWID| KHGL_KHYWDLXX | 1 | 57 | 5 (0)| 00:00:01 |
- |* 5 | INDEX RANGE SCAN | IDX_KHGL_KHYWDLXX_KHID | 1 | | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e' OR
- "T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e' OR EXISTS (SELECT 0 FROM "KHGL_KHYWDLXX" "Y"
- WHERE "Y"."KH_ID"=:B1 AND "Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')) 3 - filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085')
- 4 - filter("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')
- 5 - access("Y"."KH_ID"=:B1)
- 统计信息
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 804560 consistent gets
- 71127 physical reads
- 0 redo size
- 516 bytes sent via SQL*Net to client
- 469 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
用UNION代替OR对其进行优化后的代码如下：
- SQL> select count(*)
- 2 from (select *
- 3 from swgl_ddjbxx t
- 4 where t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e'
- 5 and t.fzgs_dm = '001085'
- 6 and t.xjbz = '9999'
- 7 and t.FROMNBGL1 = '0'
- 8 union
- 9 select *
- 10 from swgl_ddjbxx t
- 11 where t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e'
- 12 and t.fzgs_dm = '001085'
- 13 and t.xjbz = '9999'
- 14 and t.FROMNBGL1 = '0'
- 15 union
- 16 select *
- 17 from swgl_ddjbxx t
- 18 where exists
- 19 (select y.kh_id
- 20 from khgl_khywdlxx y
- 21 where y.kh_id = t.kh_id
- 22 and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e')
- 23 and t.fzgs_dm = '001085'
- 24 and t.xjbz = '9999'
- 25 and t.FROMNBGL1 = '0');
- COUNT(*)
- ----------
- 60
- 已用时间: 00: 00: 06.89
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3846872744
- -----------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
- -----------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | | | 52263 (1)| 00:10:28 |
- | 1 | SORT AGGREGATE | | 1 | | | | |
- | 2 | VIEW | | 5996 | | | 52263 (1)| 00:10:28 |
- | 3 | SORT UNIQUE | | 5996 | 2238K| 6344K| 52263 (47)| 00:10:28 |
- | 4 | UNION-ALL | | | | | | |
- |* 5 | TABLE ACCESS FULL | SWGL_DDJBXX | 59 | 19234 | | 28037 (1)| 00:05:37 |
- |* 6 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 10 | 3260 | | 1209 (1)| 00:00:15 |
- |* 7 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_KPRDM | 4748 | | | 34 (0)| 00:00:01 |
- |* 8 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 1 | 326 | | 5 (0)| 00:00:01 |
- | 9 | NESTED LOOPS | | 5927 | 2216K| | 22527 (1)| 00:04:31 |
- | 10 | SORT UNIQUE | | 10165 | 565K| | 1916 (1)| 00:00:23 |
- | 11 | TABLE ACCESS BY INDEX ROWID| KHGL_KHYWDLXX | 10165 | 565K| | 1916 (1)| 00:00:23 |
- |* 12 | INDEX RANGE SCAN | IDX_KHGL_KHYWDLXX_SSKHJL | 10165 | | | 111 (0)| 00:00:02 |
- |* 13 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_KHID | 2 | | | 2 (0)| 00:00:01 |
- -----------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 5 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e' AND "T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999'
- AND "T"."FZGS_DM"='001085')
- 6 - filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085')
- 7 - access("T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e')
- 8 - filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085')
- 12 - access("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')
- 13 - access("Y"."KH_ID"="T"."KH_ID")
- 统计信息
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 128422 consistent gets
- 10308 physical reads
- 0 redo size
- 512 bytes sent via SQL*Net to client
- 469 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 2 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
SQL改写之后，执行时间由原来的20秒下降到6秒，逻辑读由804560降低到128422，性能还是有很大提升的，到了这里优化还没完，可以创建一个组合索引进一步优化。
create index idx on swgl_ddjbxx(fzgs_dm,xjbz,FROMNBGL1);
创建索引之后，原始的SQL执行时间，执行计划，统计信息如下：
- SQL> select count(*) rowcount_lhy
- 2 from swgl_ddjbxx t
- 3 where t.fzgs_dm = '001085'
- 4 and (t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e' or exists
- 5 (select y.kh_id
- 6 from khgl_khywdlxx y
- 7 where y.kh_id = t.kh_id
- 8 and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e') or
- 9 t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e')
- 10 and t.xjbz = '9999'
- 11 and t.FROMNBGL1 = '0';
- ROWCOUNT_LHY
- ------------
- 60
- 已用时间: 00: 00: 02.96
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3049366449
- --------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 86 | 506 (0)| 00:00:07 |
- | 1 | SORT AGGREGATE | | 1 | 86 | | |
- |* 2 | FILTER | | | | | |
- | 3 | TABLE ACCESS BY INDEX ROWID| SWGL_DDJBXX | 5926 | 497K| 506 (0)| 00:00:07 |
- |* 4 | INDEX RANGE SCAN | IDX | 2370 | | 12 (0)| 00:00:01 |
- |* 5 | TABLE ACCESS BY INDEX ROWID| KHGL_KHYWDLXX | 1 | 57 | 5 (0)| 00:00:01 |
- |* 6 | INDEX RANGE SCAN | IDX_KHGL_KHYWDLXX_KHID | 1 | | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e' OR
- "T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e' OR EXISTS (SELECT 0 FROM "KHGL_KHYWDLXX" "Y"
- WHERE "Y"."KH_ID"=:B1 AND "Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')) 4 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0')
- 5 - filter("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')
- 6 - access("Y"."KH_ID"=:B1)
- 统计信息
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 702767 consistent gets
- 0 physical reads
- 0 redo size
- 516 bytes sent via SQL*Net to client
- 469 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
改写的SQL：
- SQL> select count(*)
- 2 from (select *
- 3 from swgl_ddjbxx t
- 4 where t.lrr_dm = 'e90e3fe4237c4af988477329c7f2059e'
- 5 and t.fzgs_dm = '001085'
- 6 and t.xjbz = '9999'
- 7 and t.FROMNBGL1 = '0'
- 8 union
- 9 select *
- 10 from swgl_ddjbxx t
- 11 where t.kpr_dm = 'e90e3fe4237c4af988477329c7f2059e'
- 12 and t.fzgs_dm = '001085'
- 13 and t.xjbz = '9999'
- 14 and t.FROMNBGL1 = '0'
- 15 union
- 16 select *
- 17 from swgl_ddjbxx t
- 18 where exists
- 19 (select y.kh_id
- 20 from khgl_khywdlxx y
- 21 where y.kh_id = t.kh_id
- 22 and y.sskhjl_dm = 'e90e3fe4237c4af988477329c7f2059e')
- 23 and t.fzgs_dm = '001085'
- 24 and t.xjbz = '9999'
- 25 and t.FROMNBGL1 = '0');
- COUNT(*)
- ----------
- 60
- 已用时间: 00: 00: 00.53
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 2947849958
- -------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | | | 3469 (1)| 00:00:42 |
- | 1 | SORT AGGREGATE | | 1 | | | | |
- | 2 | VIEW | | 5995 | | | 3469 (1)| 00:00:42 |
- | 3 | SORT UNIQUE | | 5995 | 2238K| 4760K| 3469 (86)| 00:00:42 |
- | 4 | UNION-ALL | | | | | | |
- |* 5 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 59 | 19234 | | 506 (0)| 00:00:07 |
- |* 6 | INDEX RANGE SCAN | IDX | 2370 | | | 12 (0)| 00:00:01 |
- | 7 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 10 | 3260 | | 50 (0)| 00:00:01 |
- | 8 | BITMAP CONVERSION TO ROWIDS | | | | | | |
- | 9 | BITMAP AND | | | | | | |
- | 10 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
- |* 11 | INDEX RANGE SCAN | IDX | 2370 | | | 12 (0)| 00:00:01 |
- | 12 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
- |* 13 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_KPRDM | 2370 | | | 34 (0)| 00:00:01 |
- |* 14 | HASH JOIN RIGHT SEMI | | 5926 | 2216K| | 2423 (1)| 00:00:30 |
- | 15 | TABLE ACCESS BY INDEX ROWID | KHGL_KHYWDLXX | 10165 | 565K| | 1916 (1)| 00:00:23 |
- |* 16 | INDEX RANGE SCAN | IDX_KHGL_KHYWDLXX_SSKHJL | 10165 | | | 111 (0)| 00:00:02 |
- | 17 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 5926 | 1886K| | 506 (0)| 00:00:07 |
- |* 18 | INDEX RANGE SCAN | IDX | 2370 | | | 12 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 5 - filter("T"."LRR_DM"='e90e3fe4237c4af988477329c7f2059e')
- 6 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0')
- 11 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0')
- filter("T"."FROMNBGL1"='0' AND "T"."XJBZ"='9999' AND "T"."FZGS_DM"='001085')
- 13 - access("T"."KPR_DM"='e90e3fe4237c4af988477329c7f2059e')
- 14 - access("Y"."KH_ID"="T"."KH_ID")
- 16 - access("Y"."SSKHJL_DM"='e90e3fe4237c4af988477329c7f2059e')
- 18 - access("T"."FZGS_DM"='001085' AND "T"."XJBZ"='9999' AND "T"."FROMNBGL1"='0')
- 统计信息
- ---------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 25628 consistent gets
- 0 physical reads
- 0 redo size
- 512 bytes sent via SQL*Net to client
- 469 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
以上就是SQL Server使用UNION代替OR提升查询性能的实例，本文我们就介绍到这里了，希望本次的介绍能够对您有所收获！
【编辑推荐】
