性能调优：分页SQL语句的优化

性能调优：分页SQL语句的优化

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

此案例来自朋友分享，是一个非常经典的分页SQL语句和视图展开的案例。
分页查询是业务系统中最常见的SQL场景之一，但如果SQL写得不合理，随着数据量的增加，性能问题会非常突出。本文通过一个真实案例，带你一步步分析和优化Oracle分页SQL，帮助你掌握通用的分页优化套路。

1. 问题SQL与执行计划

业务场景：页面查询停车场进出记录，按时间分页展示。原始SQL如下：

 *
FROM
 (
  SELECT
   page.*, ROWNUM AS rn
  FROM
   (
    SELECT
     A .PARKIN_INFO_ID AS parkininfoid,
     A .CAR_ID AS carid,
     A .PLACE_ID AS placeid,
     P .PLACE_NAME AS placename,
     A .PARKIN_CAR_NO AS parkincarno,
     A .PARKIN_CARD_NO AS parkincardno,
     A .PARKIN_CAR_TYPE AS parkincartype,
     A .PARKIN_ENTRY_NO AS parkinentryno,
     A .PARKIN_TIME AS parkintime,
     A .PARKIN_PIC AS parkinpic,
     b.PARKOUT_EXIT_NO AS parkoutexitno,
     b.PARKOUT_TIME AS parkouttime,
     b.PARKOUT_PIC AS parkoutpic,
     A .PARKIN_ENTRY_NO AS poindoorno,
     b.PARKOUT_EXIT_NO AS pooutdoorno,
     A .createtime AS createtime,
     A .createby AS createby,
     A .updatetime AS updatetime,
     A .updateby AS updateby
    FROM
     p_park_in A
    LEFT JOIN p_park_out b ON A .PARKIN_INFO_ID = b.PARKOUT_IN_ID
    LEFT JOIN p_place P ON A .PLACE_ID = P .place_id
    LEFT JOIN p_place_door d_in ON (
     A .PLACE_ID = d_in.PD_PLACE_ID
     AND A .PARKIN_ENTRY_NO = d_in.pd_no
     AND d_in.PD_TYPE = 0
    )
    LEFT JOIN p_place_door d_out ON (
     A .PLACE_ID = d_out.PD_PLACE_ID
     AND b.PARKOUT_EXIT_NO = d_out.pd_no
     AND d_out.PD_TYPE = 1
    )
    WHERE
     1 = 1
    AND A .PARKIN_TIME >= '1682672708000'
    AND A .PARKIN_TIME <= '1682759108000'
    AND P .PLACE_STATUS = '1'
    ORDER BY
     PARKIN_TIME DESC,
     PARKOUT_TIME DESC,
     createtime DESC
   ) page
  WHERE
   ROWNUM <= 10
 )
WHERE
 rn >= 1

执行计划和性能统计：

| infromation  from v$sqlstats		     |
+------------------------------------------------------------------------+
 
 
	    CPU(MS)  ELA(MS)	 DISK	       GET	  ROWS	    ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS)	 PLSQL	   JAVA
EXEC	   PRE EXEC PRE EXEC PRE EXEC	  PRE EXEC    PRE EXEC PRE FETCH  PER EXEC   PER EXEC	 PER EXEC    PER EXEC PER EXEC PER EXEC SQL_PROFILE
---------- -------- -------- -------- ------------ ----------- --------- --------- ---------- ----------- ----------- -------- -------- ---------------
9	      3,073    5,405	    0	   937,358	    10	       5	 0	    0	    2,573	    0	     0	      0
 
 
+------------------------------------------------------------------------+
| information from v$sql		 |
+------------------------------------------------------------------------+
 
 
		    PLAN  CHI USER	  CPU(MS)  ELA(MS)     DISK	     GET	ROWS	  ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS) FIRST_LOAD_TIME
EXEC	      HASH VALUE  NUM NAME	 PRE EXEC PRE EXEC PRE EXEC	PRE EXEC    PRE EXEC PRE FETCH	PER EXEC   PER EXEC    PER EXEC    PER EXEC LAST_LOAD_TIME
---------- ------------- ---- ---------- -------- -------- -------- ------------ ----------- --------- --------- ---------- ----------- ----------- ----------------------
9	       981367741    0 LZPARKING     3,073    5,405	  0	 937,358	  10	     5	       0	  0	  2,573 	  0 04-30/19:4.04-30/19:4
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name		   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time	   |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |			   |	   |	   |	   |   210K(100)|	   |
|*  1 |  VIEW				     |			   |	10 |  6340 |	   |   210K  (1)| 00:42:04 |
|*  2 |   COUNT STOPKEY 		     |			   |	   |	   |	   |		|	   |
|   3 |    VIEW 			     |			   | 25578 |	15M|	   |   210K  (1)| 00:42:04 |
|*  4 |     SORT ORDER BY STOPKEY	     |			   | 25578 |	15M|	16M|   210K  (1)| 00:42:04 |
|*  5 |      HASH JOIN RIGHT OUTER	     |			   | 25578 |	15M|	   |   206K  (1)| 00:41:24 |
|*  6 |       INDEX FAST FULL SCAN	     | IDX_PD_TYPE_ID	   |   810 | 30780 |	   |	10   (0)| 00:00:01 |
|   7 |       VIEW			     |			   | 25578 |	14M|	   |   206K  (1)| 00:41:24 |
|   8 |        NESTED LOOPS OUTER	     |			   | 25578 |  9042K|	   |   206K  (1)| 00:41:24 |
|*  9 | 	HASH JOIN RIGHT OUTER	     |			   | 25268 |  7032K|	   |   111K  (1)| 00:22:15 |
|* 10 | 	 INDEX FAST FULL SCAN	     | IDX_PD_TYPE_ID	   |   816 | 31008 |	   |	10   (0)| 00:00:01 |
|* 11 | 	 HASH JOIN		     |			   | 25268 |  6094K|	   |   111K  (1)| 00:22:15 |
|  12 | 	  TABLE ACCESS BY INDEX ROWID| P_PLACE		   |  1683 | 99297 |	   |   786   (0)| 00:00:10 |
|* 13 | 	   INDEX RANGE SCAN	     | IDX_P_PLACE	   |  1683 |	   |	   |	 9   (0)| 00:00:01 |
|  14 | 	  TABLE ACCESS BY INDEX ROWID| P_PARK_IN	   | 34646 |  6360K|	   |   110K  (1)| 00:22:06 |
|* 15 | 	   INDEX RANGE SCAN	     | IDX_PPI_PARKIN_TIME |   173K|	   |	   |   434   (1)| 00:00:06 |
|  16 | 	TABLE ACCESS BY INDEX ROWID  | P_PARK_OUT	   |	 1 |	77 |	   |	 4   (0)| 00:00:01 |
|* 17 | 	 INDEX RANGE SCAN	     | IDX_P_PARK_OUT	   |	 1 |	   |	   |	 3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   4 - filter(ROWNUM<=10)
   5 - access("B"."PARKOUT_EXIT_NO"="D_OUT"."PD_NO" AND "A"."PLACE_ID"="D_OUT"."PD_PLACE_ID")
   6 - filter(TO_NUMBER("D_OUT"."PD_TYPE")=1)
   9 - access("A"."PARKIN_ENTRY_NO"="D_IN"."PD_NO" AND "A"."PLACE_ID"="D_IN"."PD_PLACE_ID")
  10 - filter(TO_NUMBER("D_IN"."PD_TYPE")=0)
  11 - access("A"."PLACE_ID"="P"."PLACE_ID")
  13 - access("P"."PLACE_STATUS"=1)
  15 - access("A"."SYS_NC00027$">=HEXTORAW('38FDBAE2C7EDFF')  AND
	      "A"."SYS_NC00027$"<=HEXTORAW('38FDBAE3BAB1FF') )
       filter((SYS_OP_UNDESCEND("A"."SYS_NC00027$")>=1682768770000 AND
	      SYS_OP_UNDESCEND("A"."SYS_NC00027$")<=1682855170000))
  17 - access("A"."PARKIN_INFO_ID"="B"."PARKOUT_IN_ID")

2. 分析与优化思路

2.1 排序阻塞

据业务人员反应，该sql是一个没有绑定变量的sql，PARKIN_TIME是页面的输入条件，当选取的时间范围越大，该sql性能消耗越大，查询时间越长。每一天的数据量大概在35w，当时间范围超过1天时，基本要等很久页面才会返回数据。

关于分页查询的优化，还是有套路的，而且个人认为所有数据库的分页查询优化思路都是一样的。一个完美的分页查询sql的执行计划，应该是一套非阻塞操作，其手段就是利用排序字段列的表对应的索引(免排序)作为驱动+NL连接(合适的索引实现精确匹配)+STOPKEY。基本无论传入什么输入条件，结果都应该是秒出的。很明显本案例的执行计划不是一个优秀的分页执行计划。

首先提一下执行计划的非阻塞操作。非阻塞操作其实就是当查询一个块的数据，不用等待其操作完成就可以按执行计划继续往下走或者返回客户端。比如全表扫描、索引范围扫描、NL连接都是非阻塞操作。常见的阻塞操作比如排序、HASH连接、VIEW关键字等等。对于分页查询的优化思路其实就是避免阻塞操作，并且尽量保证每一条记录的驱动都能最终返回(少做无用功)。

回到本案例，分析分页查询的第一个阻塞操作点order by部分：

     PARKIN_TIME DESC,
     PARKOUT_TIME DESC,
     createtime DESC

PARKIN_TIME和createtime来自表p_park_in，PARKOUT_TIME来自p_park_out，当排序字段来自多个表时，是没法通过索引去消除order by的阻塞操作的。发现第一个问题之后，立刻与开发沟通。

给出的建议有两种方法：

是否能去掉PARKOUT_TIME的order by，改为
ORDER BY PARKIN_TIME DESC, createtime DESC
如果不能去掉，可以改写sql分页部分，order by ORDER BY PARKIN_TIME DESC, createtime DESC之后，rownum<=10改成rownum<=10000，再根据这10000条记录做ORDER BY PARKIN_TIME DESC, PARKOUT_TIME DESC, createtime DESC，相当于排序了两次，应该没有人去翻页1000次。

     PARKIN_TIME DESC,
     createtime DESC
   ) page
  WHERE
   ROWNUM <= 10000
 )
 ORDER BY
     PARKIN_TIME DESC,
     PARKOUT_TIME DESC,
     createtime DESC)
  WHERE
   ROWNUM <= 10
 )
WHERE
 rn >= 1

最终开发认为PARKOUT_TIME的排序是可以去掉的，那么第一个阻塞点就消除了。结合谓词信息：

     1 = 1
    AND A .PARKIN_TIME >= '1682672708000'
    AND A .PARKIN_TIME <= '1682759108000'
    AND P .PLACE_STATUS = '1'

第一个优化建议：

创建索引PARKIN_TIME、CREATE_TIME组合索引。由该索引开始驱动，同时避免了排序阻塞操作(SORT ORDER BY STOPKEY)。

2.2 视图阻塞（VIEW）

仔细分析执行计划，第二个阻塞操作点

| Id  | Operation			     | Name		   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time	   |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |			   |	   |	   |	   |   210K(100)|	   |
|*  1 |  VIEW				     |			   |	10 |  6340 |	   |   210K  (1)| 00:42:04 |
|*  2 |   COUNT STOPKEY 		     |			   |	   |	   |	   |		|	   |
|   3 |    VIEW 			     |			   | 25578 |	15M|	   |   210K  (1)| 00:42:04 |
|*  4 |     SORT ORDER BY STOPKEY	     |			   | 25578 |	15M|	16M|   210K  (1)| 00:42:04 |
|*  5 |      HASH JOIN RIGHT OUTER	     |			   | 25578 |	15M|	   |   206K  (1)| 00:41:24 |
|*  6 |       INDEX FAST FULL SCAN	     | IDX_PD_TYPE_ID	   |   810 | 30780 |	   |	10   (0)| 00:00:01 |
|   7 |       VIEW			     |			   | 25578 |	14M|	   |   206K  (1)| 00:41:24 |
|   8 |        NESTED LOOPS OUTER	     |			   | 25578 |  9042K|	   |   206K  (1)| 00:41:24 |
|*  9 | 	HASH JOIN RIGHT OUTER	     |			   | 25268 |  7032K|	   |   111K  (1)| 00:22:15 |
|* 10 | 	 INDEX FAST FULL SCAN	     | IDX_PD_TYPE_ID	   |   816 | 31008 |	   |	10   (0)| 00:00:01 |
|* 11 | 	 HASH JOIN		     |			   | 25268 |  6094K|	   |   111K  (1)| 00:22:15 |
|  12 | 	  TABLE ACCESS BY INDEX ROWID| P_PLACE		   |  1683 | 99297 |	   |   786   (0)| 00:00:10 |
|* 13 | 	   INDEX RANGE SCAN	     | IDX_P_PLACE	   |  1683 |	   |	   |	 9   (0)| 00:00:01 |
|  14 | 	  TABLE ACCESS BY INDEX ROWID| P_PARK_IN	   | 34646 |  6360K|	   |   110K  (1)| 00:22:06 |
|* 15 | 	   INDEX RANGE SCAN	     | IDX_PPI_PARKIN_TIME |   173K|	   |	   |   434   (1)| 00:00:06 |
|  16 | 	TABLE ACCESS BY INDEX ROWID  | P_PARK_OUT	   |	 1 |	77 |	   |	 4   (0)| 00:00:01 |
|* 17 | 	 INDEX RANGE SCAN	     | IDX_P_PARK_OUT	   |	 1 |	   |	   |	 3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

ID=7的view关键字，说明VIEW下的连接需要关联查询完成当作一个整体才能与ID=6做HASH外连接。

为何会出现VIEW呢？为什么oracle不能做view merge的查询转换呢？view merge相关概念参考之前的一篇文章。

仔细分析sql文本发现：

     A .PLACE_ID = d_out.PD_PLACE_ID
     AND b.PARKOUT_EXIT_NO = d_out.pd_no
     AND d_out.PD_TYPE = 1
    )

与表p_place_door关联时，关联字段有表P_PARK_IN的PLACE_ID，还有表P_PARK_OUT的PARKOUT_EXIT_NO。如果oracle做了view merge，那么会导致d表同时与a和b都外连接，这在oracle中是不允许的，会报出ORA-01417错误。所以CBO不会对这样的view做merge转换。

  2  from test.t a,test.t2 b,TEST.T1 c
  3  where a.object_id=b.object_id and a.data_object_id=c.data_object_id(+) and b.object_type=c.object_type(+);
where a.object_id=b.object_id and a.data_object_id=c.data_object_id(+) and b.object_type=c.object_type(+)
                                                  *
ERROR at line 3:
ORA-01417: a table may be outer joined to at most one other table

那么要消除VIEW必须要CBO去把这个view merge掉，解决办法只有d表要么都与P_PARK_IN的字段关联，要么都与P_PARK_OUT的字段关联。

跟开发沟通之后P_PARK_OUT表的PARKOUT_PLACEID可以替代P_PARK_IN的PLACE_ID字段。

3. 优化后SQL

最终与开发沟通后，SQL优化如下：

 *
FROM
 (
  SELECT
   page.*, ROWNUM AS rn
  FROM
   (
    SELECT
     A .PARKIN_INFO_ID AS parkininfoid,
     A .CAR_ID AS carid,
     A .PLACE_ID AS placeid,
     P .PLACE_NAME AS placename,
     A .PARKIN_CAR_NO AS parkincarno,
     A .PARKIN_CARD_NO AS parkincardno,
     A .PARKIN_CAR_TYPE AS parkincartype,
     A .PARKIN_ENTRY_NO AS parkinentryno,
     A .PARKIN_TIME AS parkintime,
     A .PARKIN_PIC AS parkinpic,
     b.PARKOUT_EXIT_NO AS parkoutexitno,
     b.PARKOUT_TIME AS parkouttime,
     b.PARKOUT_PIC AS parkoutpic,
     A .PARKIN_ENTRY_NO AS poindoorno,
     b.PARKOUT_EXIT_NO AS pooutdoorno,
     A .createtime AS createtime,
     A .createby AS createby,
     A .updatetime AS updatetime,
     A .updateby AS updateby
    FROM
     p_park_in A
    LEFT JOIN p_park_out b ON A .PARKIN_INFO_ID = b.PARKOUT_IN_ID
    LEFT JOIN p_place P ON A .PLACE_ID = P .place_id
    LEFT JOIN p_place_door d_in ON (
     A .PLACE_ID = d_in.PD_PLACE_ID
     AND A .PARKIN_ENTRY_NO = d_in.pd_no
     AND d_in.PD_TYPE = 0
    )
    LEFT JOIN p_place_door d_out ON (
      b.PARKOUT_PLACEID = d_out.PD_PLACE_ID
     AND b.PARKOUT_EXIT_NO = d_out.pd_no
     AND d_out.PD_TYPE = 1
    )
    WHERE
     1 = 1
    AND A .PARKIN_TIME >= '1682672708000'
    AND A .PARKIN_TIME <= '1682759108000'
    AND P .PLACE_STATUS = '1'
    ORDER BY
     PARKIN_TIME DESC,
     createtime DESC
   ) page
  WHERE
   ROWNUM <= 10
 )
WHERE
 rn >= 1

消除了阻塞操作点之后，需要查看后续关联的对象上是否有对应的索引，保证NL连接能使用上，这里的索引都可以不考虑其选择性，其主要目的是保证精确匹配。

INDEX INFO
****ucptdvs "UNIQUENESS COMPRESSION PARTITIONED TEMPORARY  VISIBILITY SEGMENT_CREATED"**
****************************************************************************************
 
TABLE		TABLE				    Index				   COLUMN		      Col
OWNER		NAME				    Name			   UCPTDVS NAME 		      Pos DESC
--------------- ----------------------------------- ------------------------------ ------- ------------------------- ---- ----
LZPARKING	P_PARK_IN			    IDX_INFOID			   NNNNNVY PARKIN_INFO_ID		1 ASC
										   NNNNNVY PLACE_ID			2 ASC
										   NNNNNVY PARKIN_ENTRY_NO		3 ASC
						    IDX_PARKIN_TIME		   NNNNNVY PARKIN_CAR_NO		1 ASC
										   NNNNNVY SYS_NC00028$ 		2 DESC
										   NNNNNVY SYS_NC00029$ 		3 DESC
						    IDX_PPI_PARKIN_TIME 	   NNNNNVY SYS_NC00027$ 		1 DESC
						    PK_P_PARK_IN		   UNNNNVY PARKIN_INFO_ID		1 ASC
		P_PARK_OUT			    IDX_P_PARK_OUT		   NNNNNVY PARKOUT_IN_ID		1 ASC
										   NNNNNVY PARKOUT_EXIT_NO		2 ASC
										   NNNNNVY CREATETIME			3 ASC
						    IDX_P_PARK_OUT_IN_ID	   NNNNNVY PARKOUT_IN_ID		1 ASC
						    PK_P_PARK_OUT		   UNNNNVY PARKOUT_INFO_ID		1 ASC
		P_PLACE 			    IDX_P_PLACE 		   NNNNNVY PLACE_STATUS 		1 ASC
						    IDX_P_PLACE_ID		   NNNNNVY PLACE_ID			1 ASC
										   NNNNNVY PLACE_STATUS 		2 ASC
						    SYS_C006913 		   UNNNNVY PLACE_ID			1 ASC
		P_PLACE_DOOR			    IDX_PD_TYPE_ID		   NNNNNVY PD_TYPE			1 ASC
										   NNNNNVY PD_PLACE_ID			2 ASC
										   NNNNNVY PD_NO			3 ASC
						    PK_P_PLACE_DOOR		   UNNNNVY PD_ID			1 ASC

后续NL连接关联索引都有。那么看看优化效果，输入了一个月的时间范围，直接秒出结果。

----------------------------------------------------------
Plan hash value: 3360519549
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                         |     2 |  1268 |    17   (0)| 00:00:01 |
|*  1 |  VIEW                              |                         |     2 |  1268 |    17   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                    |                         |       |       |            |          |
|   3 |    VIEW                            |                         |     2 |  1242 |    17   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER             |                         |     2 |   806 |    17   (0)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER            |                         |     2 |   730 |    16   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER           |                         |     2 |   542 |     8   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                         |     2 |   466 |     7   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| P_PARK_IN               |     3 |   522 |     4   (0)| 00:00:01 |
|*  9 |          INDEX RANGE SCAN          | IDX_PARK_IN_CREATE_TIME |     1 |       |     3   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| P_PLACE                 |     1 |    59 |     1   (0)| 00:00:01 |
|* 11 |          INDEX UNIQUE SCAN         | PK_P_PLACE              |     1 |       |     0   (0)| 00:00:01 |
|* 12 |        INDEX RANGE SCAN            | IDX_P_PLACE_DOOR_1      |     1 |    38 |     1   (0)| 00:00:01 |
|  13 |       TABLE ACCESS BY INDEX ROWID  | P_PARK_OUT              |     1 |    94 |     4   (0)| 00:00:01 |
|* 14 |        INDEX RANGE SCAN            | IDX_P_PARK_OUT_IN_ID    |     1 |       |     3   (0)| 00:00:01 |
|* 15 |      INDEX RANGE SCAN              | IDX_P_PLACE_DOOR_1      |     1 |    38 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)
   9 - access(SYS_OP_DESCEND("PARKIN_TIME")>=HEXTORAW('38FDBAE3C3F4AEFF')  AND
              SYS_OP_DESCEND("PARKIN_TIME")<=HEXTORAW('38FDBAE4B6B8AEFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("PARKIN_TIME"))>=1682672708000 AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("PARKIN_TIME"))<=1682759108000)
  10 - filter("P"."PLACE_STATUS"=1)
  11 - access("A"."PLACE_ID"="P"."PLACE_ID")
  12 - access("A"."PLACE_ID"="D_IN"."PD_PLACE_ID"(+) AND "A"."PARKIN_ENTRY_NO"="D_IN"."PD_NO"(+) AND
              TO_NUMBER("PD_TYPE"(+))=0)
  14 - access("A"."PARKIN_INFO_ID"="B"."PARKOUT_IN_ID"(+))
  15 - access("B"."PARKOUT_PLACEID"="D_OUT"."PD_PLACE_ID"(+) AND
              "B"."PARKOUT_EXIT_NO"="D_OUT"."PD_NO"(+) AND TO_NUMBER("PD_TYPE"(+))=1)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        105  consistent gets
          0  physical reads
          0  redo size
       4268  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

优化效果非常明显，无论输入多少时间范围都是秒出结果。当一个分页查询的执行计划都是只有COUNT STOPKEY+NL连接+并且谓词都是access(没有filter或者filter的过滤条件过滤性微乎其微)，就是一个性能非常好的分页查询执行计划。

------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)