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