分页语句优化案例

2013 年一唯品会的朋友有如下语句需要优化。

select *
  from (select f.*
          from tms.inf_b2c_djwlzt_f f
         inner join tms.orderstatus os on f.transport_code = os.statuscode
         where f.warehouse = 'VIP_BJ'
           and f.is_send = 0
         order by f.created_dtm_loc, os.Sort_No asc)
 where rownum <= 500;

该 SQL 类似分页语句，因此我们可以用分页语句优化思路对其进行优化。拿到分页语句，我们首先应该检查分页语句是否符合分页语句编写规范。这里该 SQL 排序列来自两个表，不符合分页语句编写规范。我们在第 8 章中讲到，分页语句只能对一个表的列进行排序。该 SQL 排序列来自 f 和 os，并且显示的时候只有 f 表的数据。因此我们建议去掉 os 表的排序字段，如下所示。

select *
  from (select f.*
          from tms.inf_b2c_djwlzt_f f
         inner join tms.orderstatus os on f.transport_code = os.statuscode
         where f.warehouse = 'VIP_BJ'
           and f.is_send = 0
         order by f.created_dtm_loc)
 where rownum <= 500;

排序列来自 f 表，需要对 f 表创建索引，因为过滤条件是等值访问，我们可以把过滤条件放在前面，排序列放在后面，于是创建如下索引。

create index idx_f_inf on inf_b2c_djwlzt_f(warehouse,is_send,created_dtm_loc);

然后强制 f 表与 os 走嵌套循环，同时让 f 表作为嵌套循环驱动表，走刚才创建的索引。

select *
  from (select /*+ use_nl(f,os) leading(f) */
         f.*
          from tms.inf_b2c_djwlzt_f f
         inner join tms.orderstatus os on f.transport_code = os.statuscode
         where f.warehouse = 'VIP_BJ'
           and f.is_send = 0
         order by f.created_dtm_loc)
 where rownum <= 500;

执行计划如下。

-------------------------------------------------------------------------------------
| Id |Operation                      | Name             | Rows  | Bytes | Cost(%CPU)|
-------------------------------------------------------------------------------------
|  0 |SELECT STATEMENT               |                  |   500 |   725K|   754  (1)|
|* 1 | COUNT STOPKEY                 |                  |       |       |           |
|  2 |  VIEW                         |                  |   502 |   728K|   754  (1)|
|  3 |   NESTED LOOPS                |                  |   502 |   121K|   754  (1)|
|  4 |    TABLE ACCESS BY INDEX ROWID| INF_B2C_DJWLZT_F |  2419K|   562M|    71  (0)|
|* 5 |     INDEX RANGE SCAN          | IDX_F_INF        |   502 |       |     5  (0)|
|* 6 |    TABLE ACCESS FULL          | ORDERSTATUS      |     1 |     3 |     1  (0)|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=500)
   5 - access("F"."WAREHOUSE"='VIP_BJ' AND "F"."IS_SEND"=0)
   6 - filter("F"."TRANSPORT_CODE"="OS"."STATUSCODE")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1736  consistent gets
          2  physical reads
          0  redo size
      67968  bytes sent via SQL*Net to client
        883  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        500  rows processed

从执行计划中我们看到被驱动表走了全表扫描，嵌套循环被驱动表不能走全表扫描，必须走索引，于是创建如下索引。

create index STATUSCODE_IDX on ORDERSTATUS(STATUSCODE);

创建索引之后的执行计划如下。

-------------------------------------------------------------------------------------
| Id |Operation                      | Name             | Rows  | Bytes | Cost(%CPU)|
-------------------------------------------------------------------------------------
|  0 |SELECT STATEMENT               |                  |   500 |   725K|    71  (0)|
|* 1 | COUNT STOPKEY                 |                  |       |       |           |
|  2 |  VIEW                         |                  |   502 |   728K|    71  (0)|
|  3 |   NESTED LOOPS                |                  |   502 |   121K|    71  (0)|
|  4 |    TABLE ACCESS BY INDEX ROWID| INF_B2C_DJWLZT_F |  2419K|   562M|    71  (0)|
|* 5 |     INDEX RANGE SCAN          | IDX_F_INF        |   502 |       |     5  (0)|
|* 6 |    INDEX RANGE SCAN           | STATUSCODE_IDX   |     1 |     3 |     0  (0)|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=500)
   5 - access("F"."WAREHOUSE"='VIP_BJ' AND "F"."IS_SEND"=0)
   6 - access("F"."TRANSPORT_CODE"="OS"."STATUSCODE")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        247  consistent gets
          0  physical reads
          0  redo size
      60433  bytes sent via SQL*Net to client
        883  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        500  rows processed

优化完毕之后，该 SQL 逻辑读只有 247 个，最终该 SQL 可以秒杀。