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
优化完成。