Oracle性能优化之sql嵌套循环改写案例

select r.*
from (select my_table.*, rownum as my_rownum
from (select tableA.*, rownum as oracle_rownum
from (select distinct o.id,
o.字段1,
...
r.字段1,
...
from 表1 o, 表2 r
where o.字段1 = r.字段1
and r.字段2 = 0
and o.type = 1
and r.istemplate = 0
and r.status in (6, 7, 8, 9, 10)
and o.optstatus in (-1, 0, 1, 2, 7, 8)
and (exists
(select 1
from 表3 s1
where s1.sharetype = 1
and s1.objid = 2501
and s1.字段1 = r.字段1) or exists
(select 1
from 表4 h, 表3 s2
where s2.sharetype = 2
and h.seclevel >= s2.seclevel
and s2.objid = h.subcompanyid1
and h.id = 2501
and s2.字段1 = r.字段1) or exists
(select 1
from 表4 h, 表3 s3
where s3.sharetype = 3
and h.seclevel >= s3.seclevel
and s3.objid = h.departmentid
and h.id = 2501
and s3.字段1 = r.字段1) or exists
(select 1
from 表5 m,
表3 s4,
表4 h
where h.id = m.resourceid
and s4.objid = m.roleid
and s4.字段1 = r.字段1
and h.seclevel >= s4.seclevel
and s4.sharetype = 4
and m.rolelevel >= s4.rolelevel
and h.id = 2501) or exists
(select 1
from 表4 h, 表3 s5
where s5.sharetype = 5
and s5.字段1 = r.字段1
and h.seclevel >= s5.seclevel
and s5.foralluser = 1
and h.id = 2501) or exists
(select 1
from 表4 h, 表3 s6
where s6.sharetype = 6
and s6.字段1 = r.字段1
and s6.creater = h.id
and h.managerid = 2501) or exists
(select 1
from 表4 h, 表3 s7
where s7.sharetype = 7
and s7.字段1 = r.字段1
and s7.creater = h.id
and h.departmentid = 610) or exists
(select 1
from 表4 h, 表3 s8
where s8.sharetype = 8
and s8.字段1 = r.字段1
and s8.creater = h.id
and h.subcompanyid1 = 121) or
(r.creater = 2501) or exists
(select 1
from 表1 o
where o.字段1 = r.字段1
and r.status in (6, 7, 8, 9, 10)
and o.userid = 2501) or exists
(select 1
from 表2 a
inner join 表6 b
on a.字段1 = b.字段1
inner join 表7 c
on b.id = c.wtlistid
where c.userid = '2501'
and a.字段1 = o.字段1
))
order by o.id desc nulls last) tableA) my_table
where oracle_rownum < 6
and oracle_rownum > 0) r
复制

Plan hash value: 825717004
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------


| 0 | SELECT STATEMENT | | 7841 | 9877K| | 1853 (1)| 00:00:23 |
| 1 | VIEW | | 7841 | 9877K| | 1853 (1)| 00:00:23 |
| 2 | COUNT | | | | | | |
|* 3 | VIEW | | 7841 | 9778K| | 1853 (1)| 00:00:23 |
| 4 | COUNT | | | | | | |
| 5 | VIEW | | 7841 | 9678K| | 1853 (1)| 00:00:23 |
| 6 | SORT UNIQUE | | 7841 | 1876K| 2104K| 1435 (1)| 00:00:18 |
|* 7 | FILTER | | | | | | |
|* 8 | HASH JOIN | | 21206 | 5073K| | 310 (1)| 00:00:04 |
|* 9 | TABLE ACCESS FULL |  | 21227 | 621K| | 69 (2)| 00:00:01 |
|* 10 | TABLE ACCESS FULL |  | 21207 | 4452K| | 241 (1)| 00:00:03 |
|* 11 | TABLE ACCESS BY INDEX ROWID |  | 1 | 39 | | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 63 | | 3 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 63 | | 3 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID |  | 1 | 52 | | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID |  | 1 | 11 | | 2 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 64 | | 2 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 64 | | 2 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID |  | 1 | 52 | | 1 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN |  | 1 | | | 0 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID |  | 1 | 12 | | 1 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 1 | 82 | | 5 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 82 | | 5 (0)| 00:00:01 |
| 27 | NESTED LOOPS | | 1 | 75 | | 3 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID|  | 2 | 20 | | 3 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN |  | 2 | | | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID|  | 1 | 65 | | 0 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN |  | 1 | | | 0 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 33 | TABLE ACCESS BY INDEX ROWID |  | 1 | 7 | | 2 (0)| 00:00:01 |
| 34 | NESTED LOOPS | | 1 | 59 | | 3 (0)| 00:00:01 |
| 35 | NESTED LOOPS | | 1 | 59 | | 3 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID |  | 1 | 52 | | 1 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID |  | 1 | 7 | | 2 (0)| 00:00:01 |
| 40 | NESTED LOOPS | | 1 | 52 | | 2 (0)| 00:00:01 |
| 41 | NESTED LOOPS | | 1 | 52 | | 2 (0)| 00:00:01 |
|* 42 | TABLE ACCESS BY INDEX ROWID |  | 1 | 39 | | 1 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 45 | TABLE ACCESS BY INDEX ROWID |  | 1 | 13 | | 1 (0)| 00:00:01 |
| 46 | NESTED LOOPS | | 1 | 47 | | 2 (0)| 00:00:01 |
| 47 | NESTED LOOPS | | 1 | 47 | | 2 (0)| 00:00:01 |
|* 48 | TABLE ACCESS BY INDEX ROWID |  | 1 | 39 | | 1 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 50 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 51 | TABLE ACCESS BY INDEX ROWID |  | 1 | 8 | | 1 (0)| 00:00:01 |
|* 52 | FILTER | | | | | | |
|* 53 | TABLE ACCESS BY INDEX ROWID |  | 1 | 9 | | 2 (0)| 00:00:01 |
|* 54 | INDEX RANGE SCAN |  | 1 | | | 1 (0)| 00:00:01 |
|* 55 | HASH JOIN | | 1 | 86 | | 246 (0)| 00:00:03 |
| 56 | MERGE JOIN CARTESIAN | | 1 | 46 | | 243 (0)| 00:00:03 |
|* 57 | TABLE ACCESS FULL |  | 1 | 5 | | 240 (0)| 00:00:03 |
| 58 | BUFFER SORT | | 1 | 41 | | 3 (0)| 00:00:01 |
|* 59 | TABLE ACCESS FULL |  | 1 | 41 | | 3 (0)| 00:00:01 |
|* 60 | TABLE ACCESS FULL |  | 2 | 80 | | 3 (0)| 00:00:01 |
复制

(select 1
from 表2 a
inner join 表6 b
on a.字段1 = b.字段1
inner join 表7 c
on b.id = c.wtlistid
where c.userid = '2501'
and a.字段1 = o.字段1
))
order by o.id desc nulls last) tableA) my_table
where oracle_rownum < 6
and oracle_rownum > 0) r
复制

(select 1
from 表2 a
inner join 表6 b
on a.字段1 = b.字段1
inner join 表7 c
on b.id = c.wtlistid
where c.userid = '2501'
inner join 表1 o.requestid on a.requestid --改写部分
-- and a.字段1 = o.字段1 注要矛盾释
))
order by o.id desc nulls last) tableA) my_table
where oracle_rownum < 6
and oracle_rownum > 0) r
复制