sql优化小例子

客户业务系统随着数据量的增大出现运行速度降低问题，通过AWR报告分析发现运行效率低的SQL，如下：

select * from scott.计划产品出生证表 where 序号=(select min(序号) from scott.计划产品出生证表 where 任务单号= ‘xxxx-xxx-xxx’ and 产品状态=’正在执行’)

其执行计划

-----------------------------------------------------------------------------------------------
Plan hash value: 2054637612
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |   131 |   851   (2)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL            | 计划_产品出生证   |    1 |   131 |   838   (2)| 00:00:11 |
|   2 |   SORT AGGREGATE              |                |     1 |    29 |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| 计划_产品出生证  |     1 |    29 |    13   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | 计划_产品出生证  |   366 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------------------------------------------
   1 - filter("序号"= (SELECT MIN("序号") FROM "SCOTT"."计划_产品出生证表" "计划_产品出生证表" WHERE
              "产品状态"='正在执行' AND "任务单号"='170511-P22-001'))
   3 - filter("任务单号"='170511-P22-001')
   4 - access("产品状态"='正在执行')
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3802  consistent gets
          0  physical reads
          0  redo size
       1636  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

分析：
执行计划中id=1的SQL通过全表扫描方式获取数据，整体的cost值为851，consistent gets 3802，SQL效率低下。

解决方法：
create index scott.计划产品出生证表_indx_zxq on scott.计划产品出生证表(序号);

效果：
优化后的SQL执行计划执行计划

Plan hash value: 1523355933
--------------------------------------------------------------------------------------------------------
 Id  | Operation                       | Name             | Rows |  Bytes | Cost (%CPU)| Time   |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |   1  |   131 |  15   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | 计划_产品出生证表   |   1  |  131  |   2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN             | 计划_产品出生证表_I |   1  |       |   1   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE              |                  |    1  |   29 |           |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID| 计划_产品出生证表   |     1 |  29  | 13 (0)   | 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | 计划_产品出生证表_I |   366 |      |   4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("序号"= (SELECT MIN("序号") FROM "SCOTT"."计划_产品出生证表" "计划_产品出生证表" WHERE
              "产品状态"='正在执行' AND "任务单号"='xxxx-xxx-xxx'))
   4 - filter("任务单号"='xxxx-xxx-xxx')
   5 - access("产品状态"='正在执行')

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          23  consistent gets
          0  physical reads
          0  redo size
       1636  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
0   rows processed

执行计划中SQL由全表扫描方式改为索引读取方式获取数据，cost值由851降低至15，consistent gets由 3802降低至23， SQL效率明显提高。