Oracle的SQL优化二

收到开发提交的一条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
优化完成。