Oracle的SQL最佳化二，OracleSQL最佳化二

Oracle的SQL最佳化二，OracleSQL最佳化二
收到開發提交的一條SQL，在高並發下較慢，看能否做一些最佳化：
該SQL是1個表對另一個表做Left join,首先執行下看看，
SQL> select l.vcno,opttype,add_points,optdate,remark,memid,id,FAMILYID,create_Username,billno,billtype,billsubcase,reduce_Points, l.addnum,addpresentum,reducenum,reducepresentnum 
  2  from  HQ_07310066.m_mempoint_logs l left join  HQ_07310066.m_memdetail m on l.vcno = m.vcno where 1=1 AND l.memid = 'WX000000361' order by optdate desc;
已選擇208行。
執行計畫
----------------------------------------------------------
Plan hash value: 2095947206
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |     4 |   464 |   172   (2)| 00:00:03 |
|   1 |  SORT ORDER BY     |                 |     4 |   464 |   172   (2)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| M_MEMPOINT_LOGS |     4 |   464 |   171   (1)| 00:00:03 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("L"."MEMID"='WX000000361')
統計資訊
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        626  consistent gets
          0  physical reads
          0  redo size
       7125  bytes sent via SQL*Net to client
        663  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        208  rows processed
可能是主外鍵約束的原因，最佳化器自動對HQ_07310066.m_memdetail做了表消除。所以如果確定兩表之間存在嚴格的主外鍵約束，可以直接在SQL中把不需要的表去掉，如下：
SQL> select l.vcno,opttype,add_points,optdate,remark,memid,id,FAMILYID,create_Username,billno,billtype,billsubcase,reduce_Points, l.addnum,addpresentum,reducenum,
  2  reducepresentnum 
  3  from  HQ_07310066.m_mempoint_logs l 
  4  where  l.memid = 'WX000000361' 
  5  order by optdate desc;
已選擇208行。
經過時間:  00: 00: 00.02
執行計畫
----------------------------------------------------------
Plan hash value: 2095947206
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |     4 |   464 |   172   (2)| 00:00:03 |
|   1 |  SORT ORDER BY     |                 |     4 |   464 |   172   (2)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| M_MEMPOINT_LOGS |     4 |   464 |   171   (1)| 00:00:03 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("L"."MEMID"='WX000000361')
統計資訊
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        626  consistent gets
          0  physical reads
          0  redo size
      11245  bytes sent via SQL*Net to client
        663  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        208  rows processed
再建立索引：create index  HQ_07310066.i_m_mempoint_logs_optdate on  HQ_07310066.m_mempoint_logs(optdate);
SQL> select l.vcno,opttype,add_points,optdate,remark,memid,id,FAMILYID,create_Username,billno,billtype,billsubcase,reduce_Points, l.addnum,addpresentum,reducenum,
  2  reducepresentnum 
  3  from  HQ_07310066.m_mempoint_logs l 
  4  where  l.memid = 'WX000000361' 
  5  order by optdate desc;
已選擇208行。
經過時間:  00: 00: 00.02
執行計畫
----------------------------------------------------------
Plan hash value: 3466595853
----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |     4 |   464 |     6  (17)| 00:00:01 |
|   1 |  SORT ORDER BY               |                           |     4 |   464 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| M_MEMPOINT_LOGS           |     4 |   464 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I_M_MEMPOINT_LOGS_OPTDATE |     4 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("L"."MEMID"='WX000000361')
統計資訊
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         44  consistent gets
          2  physical reads
          0  redo size
      11245  bytes sent via SQL*Net to client
        663  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        208  rows processed
最佳化完成。