----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:13.11 | 3115K|
| 1 | SORT ORDER BY | | 1 | 2 | 0 |00:00:13.11 | 3115K|
| 2 | CONCATENATION | | 1 | | 0 |00:00:13.11 | 3115K|
|* 3 | FILTER | | 1 | | 0 |00:00:06.44 | 1557K|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 4 | TABLE ACCESS BY INDEX ROWID | yy | 1 | 1 | 0 |00:00:06.44 | 1557K| 6
|* 5 | INDEX RANGE SCAN | yy| 1 | 169 | 181K|00:00:05.81 | 1452K| 5
|* 6 | TABLE ACCESS BY INDEX ROWID | yy | 181K| 1 | 181K|00:00:02.41 | 725K| 2
|* 7 | INDEX RANGE SCAN | yy| 181K| 1 | 181K|00:00:01.69 | 544K| 1
|* 8 | TABLE ACCESS BY INDEX ROWID| yy | 181K| 1 | 181K|00:00:02.21 | 725K| 4
|* 9 | INDEX RANGE SCAN | yy | 181K| 2 | 181K|00:00:01.49 | 544K| 3
| 10 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 11 | TABLE ACCESS BY INDEX ROWID | yy | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 12 | INDEX UNIQUE SCAN | yy| 0 | 1 | 0 |00:00:00.01 | 0 |
|* 13 | TABLE ACCESS FULL | yy| 0 | 1 | 0 |00:00:00.01 | 0 |
|* 14 | FILTER | | 1 | | 0 |00:00:06.67 | 1557K|
|* 15 | TABLE ACCESS BY INDEX ROWID | yy | 1 | 1 | 0 |00:00:06.67 | 1557K|
|* 16 | INDEX RANGE SCAN | yy| 1 | 459 | 181K|00:00:05.99 | 1452K|
|* 17 | TABLE ACCESS BY INDEX ROWID | yy | 181K| 1 | 181K|00:00:02.53 | 725K|
|* 18 | INDEX RANGE SCAN |yy| 181K| 1 | 181K|00:00:01.80 | 544K|
|* 19 | TABLE ACCESS BY INDEX ROWID| yy | 181K| 1 | 181K|00:00:02.29 | 725K|
|* 20 | INDEX RANGE SCAN | yy | 181K| 2 | 181K|00:00:01.55 | 544K|
|* 21 | TABLE ACCESS BY INDEX ROWID | yy | 181K| 1 | 181K|00:00:02.41 | 725K|
|* 22 | INDEX RANGE SCAN | yy| 181K| 1 | 181K|00:00:01.69 | 544K|
|* 23 | TABLE ACCESS BY INDEX ROWID | yy | 181K| 1 | 181K|00:00:02.21 | 725K|
|* 24 | INDEX RANGE SCAN | yy | 181K| 2 | 181K|00:00:01.49 | 544K|
| 25 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 26 | TABLE ACCESS BY INDEX ROWID| yy | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 27 | INDEX UNIQUE SCAN | yy | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 28 | TABLE ACCESS FULL | yy| 0 | 1 | 0 |00:00:00.01 | 0 |
开发使用了内联子查询，内联子查询会根据条件把外层表的每条数据到内层表进行一次匹配，如果是全表扫描就要进行这样多行的全表扫描，有点像NEST LOOP，所以这里
start才会是181K，所以重点落到如何改写内联子查询。
我如下改写
select count(*) from (
Select *
From ppp
Where (c1 = '0501' and c2 = 'test' and c3 AND (1 = 0 OR SUBSTR(c4, 4, 1) = '0')
AND c5 IN
('1', '2', '3', '4', '5',
'6')
or (c6 = '0501' and c6 = 'test' and c7 AND (1 = 0 OR SUBSTR(c4, 4, 1) = '0')
and (123 in
(select 123
from 123
where 123 in (select 123
from 123
where 123 = 'vicky.li')) and
agentcode = 'vicky.li')
and riskcode = '0501') t,
(SELECT DISTINCT t1 FROM sdf where 123 = '0501') p,
(SELECT DISTINCT t2 FROM 123 WHERE 123 = '0501') c
where t.123=p.ProposalNo and t.123=c.123
ORDER BY t.123 desc, t.123 desc;
以前语句13秒，现在语句2秒。