一次內鏈子查詢優化 2

   ----------------------------------------------------------------------------------------------------------------------
| 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|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 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 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  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 <= sysdate)
   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 <= sysdate)
   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秒。