Use Union instead of or to improve query performance

Yesterday, an iron buddy asked me for help via QQ and asked if the following SQL statement could be optimized.

SQL> set timing onsql> set autotrace onsql> select count (*) rowcount_lhy 2 from swgl_ddjbxx T 3 where T. fzgs_dm = '000000' 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 = '000000' 11 and T. fromnbgl1 = '0'; SQL> set line 300sql>/rowcount_lhy ------------ 60 used time: 00: 00: 20.53 execution plan ------------------------------------------------------ plan hash value: 1217125969 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 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 | descripredicate 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" = '000000' and "T ". "fzgs_dm" = '000000') 4-filter ("Y ". "sskhjl_dm" = 'e90e3fe4237c4af988477329c7f2059e ') 5-access ("Y ". "kh_id" =: B1) statistics: 0 recursive cballs 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

After five seconds, I replied to my buddy and said that he had expanded or into union, but that guy's SQL was really amazing (Don't scold me), so I had to help write one.

SQL> select count (*) 2 from (select * 3 from swgl_ddjbxx T 4 where T. lrr_dm = 'e90e3fe4237c4af988477329c7f2059e '5 and T. fzgs_dm = '000000' 6 and T. xjbz = '000000' 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 = '000000' 13 and T. xjbz = '000000' 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 = '000000' 24 and T. xjbz = '000000' 25 and T. fromnbgl1 = '0'); count (*) ---------- 60 used time: 00: 00: 06.89 execution plan -------------------------------------------------------- plan hash value: 3846872744 bytes | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | bytes | 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 | descripredicate information (identified by Operation ID ): ------------------------------------------------- 5-filter ("T ". "lrr_dm" = 'e90e3fe4237c4af988477329c7f2059e' and "T ". "fromnbgl1" = '0' and "T ". "xjbz" = '000000' and "T ". "fzgs_dm" = '000000') 6-filter ("T ". "fromnbgl1" = '0' and "T ". "xjbz" = '000000' and "T ". "fzgs_dm" = '000000') 7-access ("T ". "kpr_dm" = 'e90e3fe4237c4af988477329c7f2059e ') 8-filter ("T ". "fromnbgl1" = '0' and "T ". "xjbz" = '000000' and "T ". "fzgs_dm" = '000000') 12-access ("Y ". "sskhjl_dm" = 'e90e3fe4237c4af988477329c7f2059e ') 13-access ("Y ". "kh_id" = "T ". "kh_id ") statistics 1 recursive cballs 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

After the SQL statement is rewritten, the execution time is reduced from 20 seconds to 6 seconds, and the logical read speed is reduced from 804560 to 128422. The performance is still greatly improved. The optimization is not complete yet, you can create a composite index for further optimization.

Create index idx on swgl_ddjbxx (fzgs_dm, xjbz, fromnbgl1 );

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

SQL> select count (*) rowcount_lhy 2 from swgl_ddjbxx T 3 where T. fzgs_dm = '000000' 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 = '000000' 11 and T. fromnbgl1 = '0'; rowcount_lhy ------------ 60 used time: 00: 00: 02.96 execution plan -------------------------------------------------------- plan hash value: 3049366449 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 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 | descripredicate 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" = '000000' and "T ". "xjbz" = '000000' and "T ". "fromnbgl1" = '0') 5-filter ("Y ". "sskhjl_dm" = 'e90e3fe4237c4af988477329c7f2059e ') 6-access ("Y ". "kh_id" =: B1) statistics 1 recursive cballs 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

Rewritten SQL:

SQL> select count (*) 2 from (select * 3 from swgl_ddjbxx T 4 where T. lrr_dm = 'e90e3fe4237c4af988477329c7f2059e '5 and T. fzgs_dm = '000000' 6 and T. xjbz = '000000' 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 = '000000' 13 and T. xjbz = '000000' 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 = '000000' 24 and T. xjbz = '000000' 25 and T. fromnbgl1 = '0'); count (*) ---------- 60 used time: 00: 00: 00.53 execution plan -------------------------------------------------------- plan hash value: 2947849958 bytes | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | bytes | 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 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 5-filter ("T ". "lrr_dm" = 'e90e3fe4237c4af988477329c7f2059e ') 6-access ("T ". "fzgs_dm" = '000000' and "T ". "xjbz" = '000000' and "T ". "fromnbgl1" = '0') 11-access ("T ". "fzgs_dm" = '000000' and "T ". "xjbz" = '000000' and "T ". "fromnbgl1" = '0') filter ("T ". "fromnbgl1" = '0' and "T ". "xjbz" = '000000' and "T ". "fzgs_dm" = '000000') 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" = '000000' and "T ". "xjbz" = '000000' and "T ". "fromnbgl1" = '0 ') statistics 1 recursive cballs 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

Since I cannot directly connect to the DB, this SQL optimization ends here.