关闭

Oracle的SQL优化二

标签: sql优化oracle
537人阅读 评论(1) 收藏 举报
分类:
收到开发提交的一条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
优化完成。
1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:153129次
    • 积分:2687
    • 等级:
    • 排名:第14089名
    • 原创:111篇
    • 转载:14篇
    • 译文:1篇
    • 评论:16条
    文章分类
    最新评论