Optimization of an ultra-poor SQL statement

Recently, I encountered an ultra-poor SQL statement. The table name is processed because the SQL statement involves data from a certain tax bureau.

The original SQL statement is as follows:

Select * from (select T. zxid, T. GH, T. XM, T. BM, T. fzjtpd, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh) slzl, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '0') No rating, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '1') satisfied, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '2') satisfied, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '3') General, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '4') is not satisfied, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '5') not satisfied from XXX t where T. yxbz = 'y') Where slzl <> 0;

The test_v table is scanned multiple times without looking at the execution plan. Therefore, this SQL statement must be rewritten. How can I rewrite this SQL statement? See the following analysis:

First, this SQL statement is spelled out. We can simplify the complicated problem, so let's look at this SQL statement first:

select t.zxid,t.gh,t.xm,t.bm,t.fzjgdm,       (select count(a.session_id) from test_v a where to_char(t.zxid) = a.ZCRYZH) slzl  from CC_ZXJBXX t  WHERE t.yxbz='Y';  

The execution plan is as follows:

SQL> select T. zxid, T. GH, T. XM, T. BM, T. fzjtpd, 2 (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh) slzl 3 from cc_zxjbxx T 4 where T. yxbz = 'y'; 1956 rows have been selected. Used time: 00: 00: 00.37 execution plan -------------------------------------------------------- plan hash value: 4286326665 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | -------------------------------------------------------------------------------- | 0 | SELECT statement | 1956 | 105k | 11 (0) | 00:00:01 | 1 | sort aggregate | 1 | 22 | * 2 | table access full | test_v | 4 | 88 | 5 (0) | 00:00:01 | * 3 | table access full | cc_zxjbxx | 1956 | 105k | 11 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 2-filter ("". "zcryzh" = to_char (: B1) 3-filter ("T ". "yxbz" = 'y ')

The preceding SQL statement will return 1956 rows. According to the execution plan, we can see that it performs the filter operation. Let's talk about the above SQL rewriting. The preliminary rewriting is as follows:

SQL> select T. zxid, T. GH, T. XM, T. BM, T. fzjtpd, count (*) from 2 cc_zxjbxx T, test_v A where to_char (T. zxid) =. zcryzh 3 and T. yxbz = 'y' group by T. zxid, T. GH, T. XM, T. BM, T. fzjtpd; 20 rows have been selected. Used time: 00: 00: 00.03 execution plan -------------------------------------------------------- plan hash value: 2833546679 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | hour | 0 | SELECT statement | 32157 | 2418k | 22 (28) | 00:00:01 | 1 | hash group by | 32157 | 2418k | 22 (28) | 00:00:01 | * 2 | hash join | 32157 | 2418k | 17 (6) | 00:00:01 | 3 | table access full | test_v | 411 | 9042 | 5 (0) | 00:00:01 | * 4 | table access full | cc_zxjbxx | 1956 | 105k | 11 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 2-access ("". "zcryzh" = to_char ("T ". "zxid") 4-filter ("T ". "yxbz" = 'y ')

 

After rewriting, the SQL statement returns only 20 data records, but the original SQL statement returns 1956 data records. What is the problem? The problem is that the first SQL statement is filter, which is filter and count filter.

The second SQL statement is equivalent to jion. The count in the first SQL statement will return data no matter whether the filtering is successful or not. If the filtering fails, Count = 0, therefore, left join should be used for the second SQL statement, so the SQL statement is changed as follows:

SQL> select T. zxid, T. GH, T. XM, T. BM, T. fzjtpd, count (*) from 2 cc_zxjbxx T, test_v A where to_char (T. zxid) =. zcryzh (+) 3 and T. yxbz = 'y' group by T. zxid, T. GH, T. XM, T. BM, T. fzjtpd; 1956 rows have been selected. Used time: 00: 00: 00.12 execution plan -------------------------------------------------------- plan hash value: 3281235561 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | hour | 0 | SELECT statement | 32157 | 2418k | 22 (28) | 00:00:01 | 1 | hash group by | 32157 | 2418k | 22 (28) | 00:00:01 | * 2 | hash join right outer | 32157 | 2418k | 17 (6) | 00:00:01 | 3 | table access full | test_v | 411 | 9042 | 5 (0) | 00:00:01 | * 4 | table access full | cc_zxjbxx | 1956 | 105k | 11 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 2-access ("". "zcryzh" (+) = to_char ("T ". "zxid") 4-filter ("T ". "yxbz" = 'y ')

After rewriting, the number of records returned is correct, but the SQL logic changes a little bit. The first SQL statement is filtered, And if Count () does not match, 0 is returned, but the second SQL statement is join. If it does not match, 1 is returned.
 

Therefore, the SQL statement must be rewritten as follows:

select  t.zxid,t.gh,t.xm,t.bm,t.fzjgdm,decode(count(*),1,0,count(*)) from CC_ZXJBXX t,test_v a where to_char(t.zxid) = a.ZCRYZH(+)and t.yxbz='Y' group by  t.zxid,t.gh,t.xm,t.bm,t.fzjgdm order by 1;

After one rewrite, the subsequent SQL statement is simple, so the overall SQL statement can be rewritten as follows:

Select T. zxid, T. GH, T. XM, T. BM, T. fzjtpd, decode (count (*), 1, 0, count (*), sum (decode (. MYD, '0', 1, 0) No rating, sum (decode (. MYD, '1', 1, 0) satisfied, sum (decode (. MYD, '2', 1, 0) satisfied, sum (decode (. MYD, '3', 1, 0) General, sum (decode (. MYD, '4', 1, 0) not satisfied, sum (decode (. MYD, '5', 1, 0) not satisfied from cc_zxjbxx T, test_v A where to_char (T. zxid) =. zcryzh (+) and T. yxbz = 'y' group by T. zxid, T. GH, T. XM, T. BM, T. fzjtpd;

The preceding SQL statement is equivalent

Select T. zxid, T. GH, T. XM, T. BM, T. fzjtpd, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh) slzl, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '0') No rating, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '1') satisfied, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '2') satisfied, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '3') General, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '4') is not satisfied, (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '5') not satisfied from XXX t where T. yxbz = 'y ';

Because the original SQL statement needs to remove slzl <> 0, wrap it again.

Select * from (select T. zxid, T. GH, T. XM, T. BM, T. fzjtpd, decode (count (*), 1, 0, count (*) slzl, sum (decode (. MYD, '0', 1, 0) No rating, sum (decode (. MYD, '1', 1, 0) satisfied, sum (decode (. MYD, '2', 1, 0) satisfied, sum (decode (. MYD, '3', 1, 0) General, sum (decode (. MYD, '4', 1, 0) not satisfied, sum (decode (. MYD, '5', 1, 0) not satisfied from cc_zxjbxx T, test_v A where to_char (T. zxid) =. zcryzh (+) and T. yxbz = 'y' group by T. zxid, T. GH, T. XM, T. BM, T. fzjtpd) Where slzl <> 0;

Comparison of the Execution Plan of the original SQL statement and the modified execution plan:

SQL> select * from (2 select T. zxid, T. GH, T. XM, T. BM, T. fzjtpd, 3 (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh) slzl, 4 (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '0') No rating, 5 (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '1') satisfied, 6 (select count (. session_id) from test_v A where to_char (T. ZX ID) =. zcryzh and. MYD = '2') satisfied, 7 (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '3') Generally, 8 (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '4') is not satisfied, 9 (select count (. session_id) from test_v A where to_char (T. zxid) =. zcryzh and. MYD = '5') not satisfied with 10 from xxx t 11 where T. yxbz = 'y' 12) Where slzl <> 0; 20 rows have been selected. Used time: 00: 00: 00.28 execution plan -------------------------------------------------------- plan hash value: 2669170809 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | ------------------------------------------------------------------------------ | 0 | SELECT statement | 1956 | 76284 | 17 (0) | 00:00:01 | 1 | sort aggregate | 1 | 4 | * 2 | table access full | test_v | 16 | 64 | 5 (0) | 00:00:01 | 3 | sort aggregate | 1 | 6 | * 4 | table access full | test_v | 9 | 54 | 5 (0) | 00:00:01 | 5 | sort aggregate | 1 | 6 | * 6 | table access full | test_v | 5 | 30 | 5 (0) | 00:00:01 | 7 | sort aggregate | 1 | 6 | * 8 | table access full | test_v | 1 | 6 | 5 (0) | 00:00:01 | 9 | sort aggregate | 1 | 6 | * 10 | table access full | test_v | 2 | 12 | 5 (0) | 00:00:01 | 11 | sort aggregate | 1 | 6 | * 12 | table access full | test_v | 1 | 6 | 5 (0) | 00:00:01 | 13 | sort aggregate | 1 | 6 | * 14 | table access full | test_v | 1 | 6 | 5 (0) | 00:00:01 | * 15 | filter | * 16 | table access full | XXX | 1956 | 76284 | 12 (0) | 00:00:01 | 17 | sort aggregate | 1 | 4 | * 18 | table access full | test_v | 16 | 64 | 5 (0) | 00:00:01 | identified by Operation ID: ----------------------------------------------------------------- 2-filter ("". "zcryzh" = to_char (: B1) 4-filter ("". "zcryzh" = to_char (: B1) and "". "MYD" = '0') 6-filter ("". "zcryzh" = to_char (: B1) and "". "MYD" = '1') 8-filter ("". "zcryzh" = to_char (: B1) and "". "MYD" = '2') 10-filter ("". "zcryzh" = to_char (: B1) and "". "MYD" = '3') 12-filter ("". "zcryzh" = to_char (: B1) and "". "MYD" = '4') 14-filter ("". "zcryzh" = to_char (: B1) and "". "MYD" = '5') 15-filter (select count (*) from "test_v" "A" where "". "zcryzh" = to_char (: B1) <> 0) 16-filter ("T ". "yxbz" = 'y') 18-filter ("". "zcryzh" = to_char (: B1 )) statistics 1 recursive cballs 0 dB block gets 33261 consistent gets 0 physical reads 0 redo size 2011 bytes sent via SQL * Net to client 411 bytes received via SQL * Net from client 3 SQL * net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 20 rows processed

The original SQL statement consumes 33261 logical reads. The test_v table is scanned eight times and the modified SQL statement

SQL> select * from 2 (select T. zxid, 3 T. GH, 4 TB. XM, 5 TB. BM, 6 TB. fzjtpd, 7 decode (count (*), 1, 0, count (*) slzl, 8 sum (decode (. MYD, '0', 1, 0) No rating, 9 sum (decode (. MYD, '1', 1, 0) satisfied, 10 sum (decode (. MYD, '2', 1, 0) satisfied, 11 sum (decode (. MYD, '3', 1, 0) General, 12 sum (decode (. MYD, '4', 1, 0) not satisfied, 13 sum (decode (. MYD, '5', 1, 0) not satisfied 14 from cc_zxjbxx T, test_v a 15 where to_char (T. zxid) =. zcryz H (+) 16 and T. yxbz = 'y' 17 group by T. zxid, T. GH, T. XM, T. BM, T. fzjtpd 18) Where slzl <> 0; 20 rows have been selected. Used time: 00: 00: 00.01 execution plan -------------------------------------------------------- plan hash value: 1348291491 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | period | 0 | SELECT statement | 98 | 4410 | 18 (12) | 00:00:01 | * 1 | filter | 2 | hash group by | 98 | 4410 | 18 (12) | 00:00:01 | * 3 | hash join right outer | 1956 | 88020 | 17 (6) | 00:00:01 | 4 | table access full | test_v | 411 | 2466 | 5 (0) | 00:00:01 | * 5 | table access full | cc_zxjbxx | 1956 | 76284 | 11 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ----------------------------------------------- 1-filter (decode (count (*), 1, 0, count (*) <> 0) 3-access ("". "zcryzh" (+) = to_char ("T ". "zxid") 5-filter ("T ". "yxbz" = 'y ') statistics: 0 recursive cballs 0 dB block gets 62 consistent gets 0 physical reads 0 redo size 1958 bytes sent via SQL * Net to client 411 bytes received via SQL * Net from client 3 SQL * net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 20 rows processed

The number of logical reads is reduced to 62, and the test_v table only scans once. In fact, this SQL is only a summary SQL, and anyone who has a little development knowledge knows that, the person who wrote SQL at the beginning is definitely writing SQL. SQL optimization is not complete yet,

Because we already know that the purpose of this SQL statement is to summarize it, we directly abandon the optimization idea just now and directly rewrite the SQL statement as follows:

SQL> select * 2 from (select T. zxid, 3 T. GH, 4 TB. XM, 5 TB. BM, 6 TB. fzjtpd, 7 sum (1) slzl, 8 sum (decode (. MYD, '0', 1, 0) No rating, 9 sum (decode (. MYD, '1', 1, 0) satisfied, 10 sum (decode (. MYD, '2', 1, 0) satisfied, 11 sum (decode (. MYD, '3', 1, 0) General, 12 sum (decode (. MYD, '4', 1, 0) not satisfied, 13 sum (decode (. MYD, '5', 1, 0) not satisfied 14 from cc_zxjbxx T, test_v a 15 where. zcryzh = T. zxid 16 and T. yxbz = 'y' 17 group by T. Zxid, T. gh, T. XM, T. bm, T. fzjtpd) order by 1; 20 rows have been selected. Used time: 00: 00: 00.01 execution plan -------------------------------------------------------- plan hash value: 1317439664 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | minute | 0 | SELECT statement | 411 | 18495 | 18 (12) | 00:00:01 | 1 | sort group by | 411 | 18495 | 18 (12) | 00:00:01 | * 2 | hash join | 411 | 18495 | 17 (6) | 00:00:01 | 3 | table access full | test_v | 411 | 2466 | 5 (0) | 00:00:01 | * 4 | table access full | cc_zxjbxx | 1956 | 76284 | 11 (0) | 00:00:01 | identified predicate information (identified by Operation ID ): ------------------------------------------------- 2-access ("T ". "zxid" = to_number ("". "zcryzh") 4-filter ("T ". "yxbz" = 'y ') statistics: 0 recursive cballs 0 dB block gets 62 consistent gets 0 physical reads 0 redo size 1945 bytes sent via SQL * Net to client 411 bytes received via SQL * Net from client 3 SQL * net roundtrips to/from client 1 sorts (memory) 0 sorts (Disk) 20 rows processed

Sometimes, to complete the task, the developer can complete the function, regardless of whether your SQL is well written. I believe you may have a deep understanding of the DBA.