Use union instead of or to promote query performance

An Iron Man yesterday asked me for help by QQ, asking if the following SQL can be optimized

Sql> set timing on sql> set autotrace in 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 = ' e90e3fe4237c4 af988477329c7f2059e ') or 9 t.kpr_dm = ' e90e3fe4237c4af988477329c7f2059e ') and t.xjbz = ' 9999 ' A

 

nd t.fromnbgl1 = ' 0 '; Sql> set line sql>/Rowcount_lhy------------60 time Spent: 00:00:20.53 execution plan---------------------- ------------------------------------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" statistics----------------------------------------------------------0 recursive calls 0 db Block gets 804560 consistent gets 71127 physical reads 0 redo size 516 Bytes sent via SQ
          L*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

Read for 5 seconds, reply to the Buddy said to the or into union, but that dude sql is really too much food oh (don't scold me ha), had to help write a

Sql> Select COUNT (*) 2 from (SELECT * 3 swgl_ddjbxx t 4 where t.lrr_dm = ' e90e3fe423 7c4af988477329c7f2059e ' 5 and t.fzgs_dm = ' 001085 ' 6 and t.xjbz = ' 9999 ' 7 and T . FROMNBGL1 = ' 0 ' 8 Union 9 select * from Swgl_ddjbxx t where t.kpr_dm =             ' e90e3fe4237c4af988477329c7f2059e ' and t.fzgs_dm = ' 001085 ' and t.xjbz = ' 9999 ' 14  and T.FROMNBGL1 = ' 0 ' union select * from SWGL_DDJBXX t where Exists (select y.kh_id from khgl_khywdlxx y where y.kh_id = t. kh_id and y.sskhjl_dm = ' e90e3fe4237c4af988477329c7f2059e ') and t.fzgs_dm = ' 00108

  5 ' and t.xjbz = ' 9999 ' and T.FROMNBGL1 = ' 0 '); COUNT (*)----------60 time used: 00:00:06.89 Execution plan----------------------------------------------------------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 ") Statistical information----------------------------------------------------------1 recursive calls 0 db blo  CK gets 128422 consistent gets 10308Physical reads 0 redo size bytes sent via sql*net to client 469 bytes received via Sql*ne
          T from client 2 sql*net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed

After the SQL rewrite, the execution time from the original 20 seconds down to 6 seconds, logical reading from 804560 to 128422, performance has been greatly improved, to the optimization is not finished, you can create a composite index further optimization

CREATE INDEX idx on swgl_ddjbxx (FZGS_DM,XJBZ,FROMNBGL1);

After the index is created, the original SQL execution time, the execution plan, and the statistics are as follows:

Sql> Select COUNT (*) Rowcount_lhy 2 from swgl_ddjbxx t 3 where t.fzgs_dm = ' 001085 ' 4 and (t.lrr_dm = ' E            90e3fe4237c4af988477329c7f2059e ' 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 ') and t.xjbz = ' 9999 ' and T.FROMNBGL1 = ' 0 '; Rowcount_lhy------------60 time Spent: 00:00:02.96 execution plan------------------------------------------------------- ---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" Statistics----------------------------------------------------------1 recursive calls 0 db Block gets 702767 consistent gets 0 physical reads 0 Redo size 516 Bytes sent via SQ
          L*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

Overwritten sql:

Sql> Select COUNT (*) 2 from (SELECT * 3 swgl_ddjbxx t 4 where t.lrr_dm = ' e90e3fe423 7c4af988477329c7f2059e ' 5 and t.fzgs_dm = ' 001085 ' 6 and t.xjbz = ' 9999 ' 7 and T . FROMNBGL1 = ' 0 ' 8 Union 9 select * from Swgl_ddjbxx t where t.kpr_dm =             ' e90e3fe4237c4af988477329c7f2059e ' and t.fzgs_dm = ' 001085 ' and t.xjbz = ' 9999 ' 14  and T.FROMNBGL1 = ' 0 ' union select * from SWGL_DDJBXX t where Exists (select y.kh_id from khgl_khywdlxx y where y.kh_id = t. kh_id and y.sskhjl_dm = ' e90e3fe4237c4af988477329c7f2059e ') and t.fzgs_dm = ' 00108

  5 ' and t.xjbz = ' 9999 ' and T.FROMNBGL1 = ' 0 '); COUNT (*)----------60 time used: 00:00:00.53 Execution plan----------------------------------------------------------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") statistical information----------------------------------------------------------1 recursive calls 0 DB block gets 25628 consistent gets 0 physical reads 0 Redo size Bytes Sent VI
          A 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

Since I can't connect directly to DB, this SQL optimization is over.