Use with as to optimize SQL

I am about to join the Singles' Day. I am wondering which girl I want to harm this year. I suddenly sent a message from my QQ friend asking if I could help optimize an SQL statement. I have done too much SQL tuning, all of them are numb. Optimization of an SQL statement will take several seconds to several minutes.

The following SQL statement takes more than five hours.

Select B. area_id,. party_id, B. area_name, C. name channel_name, B. name party_name, B. access_number, B. prod_spec, B. start_dt,. bo_action_name,. so_staff_id,. atom_action_id,. prod_id from dw_channel C, dw_crm_day_user B, dw_bo_order A where. prod_id = B. prod_id and. channel_id = C. channel_id and. so_staff_id like '200' and. bo_action_name in ('new installation ', 'migration', 'tariff change') and B. prod_spec in ('General telephony ', 'ad SL ', 'lan', 'phone', 'e8-2s ', 'e6 mobile version', 'e9 1 m (old version)', 'ordinary e9 ', 'General new e8', 'province-wide _ tightly integrated E9 package product specifications ',' (new) province-wide _ tightly integrated E9 package product specifications ', and 'e8 package for Happy spring festival ', 'Happy New Year send E6 package ') and not exists (select * From dw_bo_order d Where D. staff_id like '200' and. party_id = D. party_id and. bo_id! = D. bo_id and A. prod_id! = D. prod_id and A. bo_action_name in ('new installation ', 'migration', 'tariff change') and A. complete_dt-interval '7' day <D. complete_dt );

The following is the execution plan and table information.

SQL> select count (*) from dw_bo_order; count (*) ---------- 2282548 SQL> select count (*) from dw_crm_day_user; count (*) ---------- 420918 SQL> select count (*) from dw_channel; count (*) ---------- 48031 plan hash value: 2142862569 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | pstart | pstop | latency | 0 | SELECT statement | 905 | 121k | 4152 K (2) | 13:50:32 | * 1 | filter | * 2 | hash join | 905 | 121k | 12616 (2) | 00:02:32 | * 3 | hash join | 905 | 99550 | 12448 (2) | 00:02:30 | 4 | partition range all | 1979 | shard k | 9168 (2) | 00:01:51 | 1 | 5 | * 5 | table access full | dw_bo_order | 1979 | shard k | 9168 (2) | 00:01:51 | 1 | 5 | * 6 | table access full | dw_crm_day_user | 309k | 15 M | 3277 (2) | 00:00:40 | 7 | table access full | dw_channel | 48425 | 1276k | 168 (1) | 00:00:03 | * 8 | filter | 9 | partition range all | 1 | 29 | 9147 (2) | 00:01:50 | 1 | 5 | * 10 | table access full | dw_bo_order | 1 | 29 | 9147 (2) | 00:01:50 | 1 | 5 | your predicate information (identified by Operation ID ): ----------------------------------------------- 1-filter (not exists (select/* + */0 from "dw_bo_order" "D" where (: b1 = 'new installation' or: b2 = 'er' or: b3 = 'tariff change') and "D ". "party_id" =: B4 and to_char ("D ". "staff_id") Like '000000' and "D ". "complete_dt">: B5-INTERVAL '+ 07 00:00:00' Day (2) to second (0) and "D ". "prod_id" <>: B6 and "D ". "bo_id" <>: B7) 2-access ("". "channel_id" = "C ". "channel_id") 3-access ("". "prod_id" = "B ". "prod_id") 5-filter ("". "prod_id" is not null and ("". "bo_action_name" = 'Newly installed 'or "". "bo_action_name" = 'transfer' or "". "bo_action_name" = 'tariff change') and to_char ("". "so_staff_id") Like '000000') 6-filter ("B ". "prod_spec" = '(new) Province _ tightly integrated E9 package product specifications' or "B ". "prod_spec" = 'adsl 'or "B ". "prod_spec" = 'e6 mobile version' or "B ". "prod_spec" = 'e8-2s 'or "B ". "prod_spec" = 'e9 version 1 m (old version) 'or "B ". "prod_spec" = 'lan 'or "B ". "prod_spec" = 'normal e9' or "B ". "prod_spec" = 'regular telephony 'or "B ". "prod_spec" = 'General new e8' or "B ". "prod_spec" = 'province _ tightly integrated E9 package product spec 'or "B ". "prod_spec" = 'cell phone 'or "B ". "prod_spec" = 'Welcome to the e6. 'or "B ". "prod_spec" = 'e8 package for Happy New Year ') 8-filter (: b1 = 'new package' or: b2 =' ter' or: B3 = 'tariff change ') 10-filter ("D ". "party_id" =: B1 and to_char ("D ". "staff_id") Like '000000' and "D ". "complete_dt">: B2-INTERVAL '+ 07 00:00:00' Day (2) to second (0) and "D ". "prod_id" <>: B3 and "D ". "bo_id" <>: B4)

 

Experienced users can see at a glance that the SQL Performance problem is here.

Not exists (select * From dw_bo_order d Where D. staff_id like '2013' and A. party_id = D. party_id and A. bo_id! = D. bo_id and A. prod_id! = D. prod_id and A. bo_action_name in ('new installation ', 'migration', 'tariff change') and A. complete_dt-interval '7' day <D. complete_dt );

You must note that the previous not exists contains two! = Nima, groupid, and Shenma business logic. This SQL statement is too boring! = Exists. cbo cannot select the hash_aj join method. It can only use filter. Haha, it is absolutely dead when using filter, isn't it? Because it has to repeatedly scan dw_bo_order for many times, I suggest that the buddy modify the SQL statement and put it in! = Split, but unfortunately, no matter how it is split, the SQL business logic is always wrong. Nima, who told us to write SQL horizontal dishes? (self-criticism)

We recommend that you use the following method to rewrite the SQL statement:

With D as (select/* + materialize */party_id, bo_id, prod_id from dw_bo_order where staff_id like '2013') Select B. area_id,. party_id, B. area_name, C. name channel_name, B. name party_name, B. access_number, B. prod_spec, B. start_dt,. bo_action_name,. so_staff_id,. atom_action_id,. prod_id from dw_channel C, dw_crm_day_user B, dw_bo_order A where. prod_id = B. prod_id and. channel_id = C. channel_id And. so_staff_id like '200' and. bo_action_name in ('new installation ', 'migration', 'tariff change') and B. prod_spec in ('General phone', 'adsl ', 'lan', 'phone', 'e8-2s ', 'e6 mobile version', 'e9 version 1 m (Old Version) ', 'General e9', 'General new e8', 'province _ closely integrated E9 package product specifications', '(new) Province _ closely integrated E9 package product specifications ', 'e8 package for Happy New Year', 'e6 package for Happy New Year') and not exists (select * from D where D. staff_id like '200' and. party_id = D. party_id and. bo_id! = D. bo_id and A. prod_id! = D. prod_id and A. bo_action_name in ('new installation ', 'migration', 'tariff change') and A. complete_dt-interval '7' day <D. complete_dt );

The execution plan and SQL Execution time are as follows:

SQL> set TIMI onsql> with D as 2 (select/* + materialize */3 party_id, 4 bo_id, 5 prod_id, 6 complete_dt 7 from dw_bo_order 8 where staff_id like '000000' and 9 bo_action_name in ('Newly installed ', 10' ', 11' ') 12 select 13 B. area_id, 14. party_id, 15 B. area_name, 16 C. name channel_name, 17 B. name party_name, 18 B. access_number, 19 B. prod_spec, 20 B. start_dt, 21. bo_action_name, 22. so_staff_id, 23. Atom_action_id, 24. prod_id 25 from dw_channel C, 26 dw_crm_day_user B, 27 dw_bo_order a 28 Where. prod_id = B. prod_id and 29. channel_id = C. channel_id and 30. so_staff_id like '200' and 31. bo_action_name in ('new installation ', 'migration', 'tariff change') and 32 B. prod_spec in ('General phone', 'adsl ', 'lan', 'mobile phone ', 33 'e8-2s', 'e6 mobile version', 'e9 version 1 m (Old Version) ', 34' common e9', 'General new e8', 35' province-wide _ tightly integrated E9 package product specifications', 36' (new) province-wide closely integrated E9 package product specifications, 37 happy new year E8 package ', 38 'Happy New Year send E6 package') and 39 not exists (select * 40 from D 41 Where a. party_id = D. party_id and 42 A. bo_id! = D. bo_id and 43 A. prod_id! = D. prod_id and 44 A. complete_dt-interval '7' day <D. complete_dt); row 49245 has been selected. Execution Plan hash value: 12.37 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | pstart | pstop | latency | 0 | SELECT statement | 905 | 121k | 62428 (2) | 00:12:30 | 1 | temp table transformation | 2 | load as select | dw_bo_order | 3 | partition range all | 114k | 3228k | 9127 (2) | 00:01:50 | 1 | 5 | * 4 | table access full | dw_bo_order | 114k | 3228k | 9127 (2) | 00:01:50 | 1 | 5 | * 5 | filter | * 6 | hash join | 905 | 121k | 12616 (2) | 00:02:32 | * 7 | hash join | 905 | 99550 | 12448 (2) | 00:02:30 | 8 | partition range all | 1979 | shard k | 9168 (2) | 00:01:51 | 1 | 5 | * 9 | table access full | dw_bo_order | 1979 | shard k | 9168 (2) | 00:01:51 | 1 | 5 | * 10 | table access full | dw_crm_day_user | 309k | 15 M | 3277 (2) | 00:00:40 | 11 | table access full | dw_channel | 48425 | 1276k | 168 (1) | 00:00:03 | * 12 | filter | * 13 | View | 114k | 6791k | 90 (3) | 00:00:02 | 14 | table access full | sys_temp_0fd9d662e_d625b872 | 114k | 3228k | 90 (3) | 00:00:02 | descripredicate information (identified by Operation ID ): ------------------------------------------------- 4-filter (to_char ("staff_id") Like '200 ') 5-filter (not exists (select/* + */0 from (select/* + cache_temp_table ("T1") */"C0" "staff_id ", "C1" "party_id", "C2" "bo_id", "C3" "prod_id", "C4" "complete_dt" from "sys ". "sys_temp_0fd9d662e_d625b872" "T1") "D" where (: b1 = 'new installation' or: b2 = 'id' or: B3 = 'tariff change ') and to_char ("D ". "staff_id") Like '000000' and "D ". "party_id" =: B4 and "D ". "bo_id" <>: B5 and "D ". "prod_id" <>: B6 and "D ". "complete_dt">: B7-INTERVAL '+ 07 00:00:00' Day (2) to second (0) 6-access ("". "channel_id" = "C ". "channel_id") 7-access ("". "prod_id" = "B ". "prod_id") 9-filter ("". "prod_id" is not null and ("". "bo_action_name" = 'Newly installed 'or "". "bo_action_name" = 'transfer' or "". "bo_action_name" = 'tariff change') and to_char ("". "so_staff_id") Like '000000') 10-filter ("B ". "prod_spec" = '(new) Province _ tightly integrated E9 package product specifications' or "B ". "prod_spec" = 'adsl 'or "B ". "prod_spec" = 'e6 mobile version' or "B ". "prod_spec" = 'e8-2s 'or "B ". "prod_spec" = 'e9 version 1 m (old version) 'or "B ". "prod_spec" = 'lan 'or "B ". "prod_spec" = 'normal e9' or "B ". "prod_spec" = 'regular telephony 'or "B ". "prod_spec" = 'General new e8' or "B ". "prod_spec" = 'province _ tightly integrated E9 package product spec 'or "B ". "prod_spec" = 'cell phone 'or "B ". "prod_spec" = 'Welcome to the e6. 'or "B ". "prod_spec" = 'e8 package for Happy New Year ') 12-filter (: b1 = 'new package' or: b2 =' ter' or: B3 = 'tariff change ') 13-filter (to_char ("D ". "staff_id") Like '000000' and "D ". "party_id" =: B1 and "D ". "bo_id" <>: B2 and "D ". "prod_id" <>: B3 and "D ". "complete_dt">: B4-INTERVAL '+ 07 00:00:00' Day (2) to second (0 )) statistics 2 recursive cballs 29 db block gets 110506 consistent gets 22 physical reads 656 redo size 2438096 bytes sent via SQL * Net to client 449 bytes encoded ed via SQL * Net from client 11 SQL * net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 49245 rows processed

Now the SQL statement can be run in 12 seconds. This SQL statement can be optimized here. It cannot connect to the database. I am not sure about the business logic, Grandma's, when it comes to Shenma, it helps others optimize one SQL row and one dollar.