一次sql改写优化案例
问题背景：
客户反馈有条sql执行时间要30s左右，需要优化，sql如下 （一次改写案例）
（涉及相关表以及重要字段隐去）
原sql：
问题背景：
客户反馈有条sql执行时间要30s左右，需要优化，sql如下 （一次改写案例，引以为戒）
1 select r.* 2 from (select my_table.*, rownum as my_rownum 3 from (select tableA.*, rownum as oracle_rownum 4 from (select distinct o.id, 5 o.字段1, 6 ... 7 r.字段1, 8 ... 9 from 表1 o, 表2 r 10 where o.字段1 = r.字段1 11 and r.字段2 = 0 12 and o.type = 1 13 and r.istemplate = 0 14 and r.status in (6, 7, 8, 9, 10) 15 and o.optstatus in (-1, 0, 1, 2, 7, 8) 16 and (exists 17 (select 1 18 from 表3 s1 19 where s1.sharetype = 1 20 and s1.objid = 2501 21 and s1.字段1 = r.字段1) or exists 22 (select 1 23 from 表4 h, 表3 s2 24 where s2.sharetype = 2 25 and h.seclevel >= s2.seclevel 26 and s2.objid = h.subcompanyid1 27 and h.id = 2501 28 and s2.字段1 = r.字段1) or exists 29 (select 1 30 from 表4 h, 表3 s3 31 where s3.sharetype = 3 32 and h.seclevel >= s3.seclevel 33 and s3.objid = h.departmentid 34 and h.id = 2501 35 and s3.字段1 = r.字段1) or exists 36 (select 1 37 from 表5 m, 38 表3 s4, 39 40 表4 h 41 where h.id = m.resourceid 42 and s4.objid = m.roleid 43 and s4.字段1 = r.字段1 44 and h.seclevel >= s4.seclevel 45 and s4.sharetype = 4 46 and m.rolelevel >= s4.rolelevel 47 and h.id = 2501) or exists 48 (select 1 49 from 表4 h, 表3 s5 50 where s5.sharetype = 5 51 and s5.字段1 = r.字段1 52 and h.seclevel >= s5.seclevel 53 and s5.foralluser = 1 54 and h.id = 2501) or exists 55 (select 1 56 from 表4 h, 表3 s6 57 where s6.sharetype = 6 58 and s6.字段1 = r.字段1 59 and s6.creater = h.id 60 and h.managerid = 2501) or exists 61 (select 1 62 from 表4 h, 表3 s7 63 where s7.sharetype = 7 64 and s7.字段1 = r.字段1 65 and s7.creater = h.id 66 and h.departmentid = 610) or exists 67 (select 1 68 from 表4 h, 表3 s8 69 where s8.sharetype = 8 70 and s8.字段1 = r.字段1 71 and s8.creater = h.id 72 and h.subcompanyid1 = 121) or 73 (r.creater = 2501) or exists 74 (select 1 75 from 表1 o 76 where o.字段1 = r.字段1 77 and r.status in (6, 7, 8, 9, 10) 78 and o.userid = 2501) or exists 79 (select 1 80 from 表2 a 81 inner join 表6 b 82 on a.字段1 = b.字段1 83 inner join 表7 c 84 on b.id = c.wtlistid 85 where c.userid = '2501' 86 and a.字段1 = o.字段1 87 )) 88 order by o.id desc nulls last) tableA) my_table 89 where oracle_rownum < 6 90 and oracle_rownum > 0) r
执行计划：
1 Plan hash value: 825717004 2 3 4 5 ------------------------------------------------------------------------------------------------------------------------------ 6 7 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | 8 9 ------------------------------------------------------------------------------------------------------------------------------ 10 11 | 0 | SELECT STATEMENT | | 7841 | 9877K| | 1853 (1)| 00:00:23 | 12 13 | 1 | VIEW | | 7841 | 9877K| | 1853 (1)| 00:00:23 | 14 15 | 2 | COUNT | | | | | | | 16 17 |* 3 | VIEW | | 7841 | 9778K| | 1853 (1)| 00:00:23 | 18 19 | 4 | COUNT | | | | | | | 20 21 | 5 | VIEW | | 7841 | 9678K| | 1853 (1)| 00:00:23 | 22 23 | 6 | SORT UNIQUE | | 7841 | 1876K| 2104K| 1435 (1)| 00:00:18 | 24 25 |* 7 | FILTER | | | | | | | 26 27 |* 8 | HASH JOIN | | 21206 | 5073K| | 310 (1)| 00:00:04 | 28 29 |* 9 | TABLE ACCESS FULL | WORKTASK_OPERATOR | 21227 | 621K| | 69 (2)| 00:00:01 | 30 31 |* 10 | TABLE ACCESS FULL | WORKTASK_REQUESTBASE | 21207 | 4452K| | 241 (1)| 00:00:03 | 32 33 |* 11 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 39 | | 1 (0)| 00:00:01 | 34 35 |* 12 | INDEX RANGE SCAN | I_REQUESTSHARESET | 1 | | | 1 (0)| 00:00:01 | 36 37 | 13 | NESTED LOOPS | | 1 | 63 | | 3 (0)| 00:00:01 | 38 39 | 14 | NESTED LOOPS | | 1 | 63 | | 3 (0)| 00:00:01 | 40 41 |* 15 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 52 | | 1 (0)| 00:00:01 | 42 43 |* 16 | INDEX RANGE SCAN | REQUESTID_RSS_INDEX | 1 | | | 1 (0)| 00:00:01 | 44 45 |* 17 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 1 (0)| 00:00:01 | 46 47 |* 18 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 11 | | 2 (0)| 00:00:01 | 48 49 | 19 | NESTED LOOPS | | 1 | 64 | | 2 (0)| 00:00:01 | 50 51 | 20 | NESTED LOOPS | | 1 | 64 | | 2 (0)| 00:00:01 | 52 53 |* 21 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 52 | | 1 (0)| 00:00:01 | 54 55 |* 22 | INDEX RANGE SCAN | REQUESTID_RSS_INDEX | 1 | | | 1 (0)| 00:00:01 | 56 57 |* 23 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 0 (0)| 00:00:01 | 58 59 |* 24 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 12 | | 1 (0)| 00:00:01 | 60 61 | 25 | NESTED LOOPS | | 1 | 82 | | 5 (0)| 00:00:01 | 62 63 | 26 | NESTED LOOPS | | 1 | 82 | | 5 (0)| 00:00:01 | 64 65 | 27 | NESTED LOOPS | | 1 | 75 | | 3 (0)| 00:00:01 | 66 67 | 28 | TABLE ACCESS BY INDEX ROWID| HRMROLEMEMBERS | 2 | 20 | | 3 (0)| 00:00:01 | 68 69 |* 29 | INDEX RANGE SCAN | HRMROLEMEMBERS_RESOURCEID_IN | 2 | | | 1 (0)| 00:00:01 | 70 71 |* 30 | TABLE ACCESS BY INDEX ROWID| REQUESTSHARESET | 1 | 65 | | 0 (0)| 00:00:01 | 72 73 |* 31 | INDEX RANGE SCAN | I_REQUESTSHARESET | 1 | | | 0 (0)| 00:00:01 | 74 75 |* 32 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 1 (0)| 00:00:01 | 76 77 |* 33 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 7 | | 2 (0)| 00:00:01 | 78 79 | 34 | NESTED LOOPS | | 1 | 59 | | 3 (0)| 00:00:01 | 80 81 | 35 | NESTED LOOPS | | 1 | 59 | | 3 (0)| 00:00:01 | 82 83 |* 36 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 52 | | 1 (0)| 00:00:01 | 84 85 |* 37 | INDEX RANGE SCAN | REQUESTID_RSS_INDEX | 1 | | | 1 (0)| 00:00:01 | 86 87 |* 38 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 1 (0)| 00:00:01 | 88 89 |* 39 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 7 | | 2 (0)| 00:00:01 | 90 91 | 40 | NESTED LOOPS | | 1 | 52 | | 2 (0)| 00:00:01 | 92 93 | 41 | NESTED LOOPS | | 1 | 52 | | 2 (0)| 00:00:01 | 94 95 |* 42 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 39 | | 1 (0)| 00:00:01 | 96 97 |* 43 | INDEX RANGE SCAN | REQUESTID_RSS_INDEX | 1 | | | 1 (0)| 00:00:01 | 98 99 |* 44 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 1 (0)| 00:00:01 | 100 101 |* 45 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 13 | | 1 (0)| 00:00:01 | 102 103 | 46 | NESTED LOOPS | | 1 | 47 | | 2 (0)| 00:00:01 | 104 105 | 47 | NESTED LOOPS | | 1 | 47 | | 2 (0)| 00:00:01 | 106 107 |* 48 | TABLE ACCESS BY INDEX ROWID | REQUESTSHARESET | 1 | 39 | | 1 (0)| 00:00:01 | 108 109 |* 49 | INDEX RANGE SCAN | REQUESTID_RSS_INDEX | 1 | | | 1 (0)| 00:00:01 | 110 111 |* 50 | INDEX RANGE SCAN | HRMRESOURCE_ID | 1 | | | 1 (0)| 00:00:01 | 112 113 |* 51 | TABLE ACCESS BY INDEX ROWID | HRMRESOURCE | 1 | 8 | | 1 (0)| 00:00:01 | 114 115 |* 52 | FILTER | | | | | | | 116 117 |* 53 | TABLE ACCESS BY INDEX ROWID | WORKTASK_OPERATOR | 1 | 9 | | 2 (0)| 00:00:01 | 118 119 |* 54 | INDEX RANGE SCAN | REQUESTID_WTO_INDEX | 1 | | | 1 (0)| 00:00:01 | 120 121 |* 55 | HASH JOIN | | 1 | 86 | | 246 (0)| 00:00:03 | 122 123 | 56 | MERGE JOIN CARTESIAN | | 1 | 46 | | 243 (0)| 00:00:03 | 124 125 |* 57 | TABLE ACCESS FULL | WORKTASK_REQUESTBASE | 1 | 5 | | 240 (0)| 00:00:03 | 126 127 | 58 | BUFFER SORT | | 1 | 41 | | 3 (0)| 00:00:01 | 128 129 |* 59 | TABLE ACCESS FULL | WORKTASK_LIST_LIABLEPERSON | 1 | 41 | | 3 (0)| 00:00:01 | 130 131 |* 60 | TABLE ACCESS FULL | WORKTASK_LIST | 2 | 80 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------
原sql不得不说从各个角度来看都是一条糟糕的sql，生产环境执行时间要50s左右，有问题的地方多了优化只能抓主要问题
原sql的结尾有一段sql如下
1 (select 1 2 from 表2 a 3 inner join 表6 b 4 on a.字段1 = b.字段1 5 inner join 表7 c 6 on b.id = c.wtlistid 7 where c.userid = '2501' 8 and a.字段1 = o.字段1 9 )) 10 order by o.id desc nulls last) tableA) my_table 11 where oracle_rownum < 6 12 and oracle_rownum > 0) r
标红的位置，子查询里有两张表关联， a.字段1很好理解，是表2，那么 o.字段1的o在哪？发现是父表，这里使用了子表和父表的关联条件，所以执行计划里才有大量的nested loop循环
尝试改写为执行在子查询里关联字表字段，改写如下
1 (select 1 2 from 表2 a 3 inner join 表6 b 4 on a.字段1 = b.字段1 5 inner join 表7 c 6 on b.id = c.wtlistid 7 where c.userid = '2501' 8 inner join 表1 o.requestid on a.requestid --改写部分 9 -- and a.字段1 = o.字段1 注释 10 )) 11 order by o.id desc nulls last) tableA) my_table 12 where oracle_rownum < 6 13 and oracle_rownum > 0) r
改写后的sql在生产环境执行时间5ms,调优成功！
sql调优一次拨开迷雾的过程，抓主要矛盾放手次要矛盾
