一次简单的分页优化

今天对一个排序分页查询进行了一次优化，优化后性能得到明显改观。下面用例子说明优化步骤。

1、首先创建测试数据

SQL> create table t as select * from dba_objects;

Table created

SQL> insert into t select * from t;

12704 rows inserted

SQL> commit;

Commit complete

SQL> insert into t select * from t;

25408 rows inserted

SQL> insert into t select * from t;

50816 rows inserted

SQL> commit;

SQL> create index idx_t_object_name on t(object_name);

Index created

SQL> ANALYZE TABLE T COMPUTE STATISTICS;

Table analyzed

2、取第一页数据
suk@ORA10G> SELECT *
  2  FROM   (SELECT A.*, ROWNUM RN FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A WHERE ROWNUM <
 16)
  3  WHERE  RN >=1;

已选择15行。

已用时间:  00: 00: 00.21

执行计划
----------------------------------------------------------
Plan hash value: 882605040

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    15 |  2850 |   308   (1)| 00:00:04 |
|*  1 |  VIEW                    |      |    15 |  2850 |   308   (1)| 00:00:04 |
|*  2 |   COUNT STOPKEY          |      |       |       |            |          |
|   3 |    VIEW                  |      |   101K|    17M|   308   (1)| 00:00:04 |
|*  4 |     SORT ORDER BY STOPKEY|      |   101K|  7940K|   308   (1)| 00:00:04 |
|   5 |      TABLE ACCESS FULL   | T    |   101K|  7940K|   308   (1)| 00:00:04 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">0)
   2 - filter(ROWNUM<16)
   4 - filter(ROWNUM<16)

统计信息
----------------------------------------------------------
        189  recursive calls
          0  db block gets
       1363  consistent gets
          0  physical reads
          0  redo size
       1440  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         15  rows processed

从执行计划可以看出，oracle选择了全表扫描，虽然有stopkey，但是需要有大数据量的排序操作，效率还是很低。
我们在object_name上已经创建了索引，如果走索引，避免了排序操作，并且只返回了少量数据，效率肯定很高。现在问题的关键是分析为什么没有用索引。

先看看表结构：

SQL> desc t
Name           Type          Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER          VARCHAR2(30)  Y                        
OBJECT_NAME    VARCHAR2(128) Y                        
SUBOBJECT_NAME VARCHAR2(30)  Y                        
OBJECT_ID      NUMBER        Y                        
DATA_OBJECT_ID NUMBER        Y                        
OBJECT_TYPE    VARCHAR2(19)  Y                        
CREATED        DATE          Y                        
LAST_DDL_TIME  DATE          Y                        
TIMESTAMP      VARCHAR2(19)  Y                        
STATUS         VARCHAR2(7)   Y                        
TEMPORARY      VARCHAR2(1)   Y                        
GENERATED      VARCHAR2(1)   Y                        
SECONDARY      VARCHAR2(1)   Y                        

原因出来了，OBJECT_NAME字段定义为可以为空的，因为btree索引不会索引空值，而默认情况下，oracle排序时会把空值认为无穷大。
在这里例子中，oracle无法知道OBJECT_NAME是否存在空值，所以，oracle会选择全表扫描。

如果确认数据当前及以后不会存在空值，把字段属性改成非空就可以使oracle使用索引了。

SQL> alter table t modify object_name not null;

Table altered

3、再次查询第一页
suk@ORA10G> SELECT *
  2  FROM   (SELECT A.*, ROWNUM RN FROM (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A WHERE ROWNUM <
 16)
  3  WHERE  RN >=1;

已选择15行。

已用时间:  00: 00: 00.06

执行计划
----------------------------------------------------------
Plan hash value: 3229612337

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |    15 |  2850 |    14   (0)| 00:00:01 |
|*  1 |  VIEW                          |                   |    15 |  2850 |    14   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                |                   |       |       |            |          |
|   3 |    VIEW                        |                   |    16 |  2832 |    14   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T                 |   101K|  7940K|    14   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN DESCENDING| IDX_T_OBJECT_NAME |    16 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">0)
   2 - filter(ROWNUM<16)

统计信息
----------------------------------------------------------
        201  recursive calls
          0  db block gets
         49  consistent gets
          0  physical reads
          0  redo size
       1440  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         15  rows processed
        

可以看到，oracle正确使用了索引，效率也提升了很多。

4、取靠后的记录

我们知道，用以上方法在返回较靠前的记录时效率很高，但是如果返回靠后的数据，效率就有很大问题了：

suk@ORA10G> SELECT *
  2  FROM   (SELECT A.*, ROWNUM RN
  3          FROM   (SELECT * FROM T ORDER BY OBJECT_NAME DESC) A
  4          WHERE  ROWNUM < 30000)
  5  WHERE  RN >= 30000 - 15;

已选择14行。

已用时间:  00: 00: 00.29

执行计划
----------------------------------------------------------
Plan hash value: 882605040

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      | 29999 |  5566K|   308   (1)| 00:00:04 |
|*  1 |  VIEW                    |      | 29999 |  5566K|   308   (1)| 00:00:04 |
|*  2 |   COUNT STOPKEY          |      |       |       |            |          |
|   3 |    VIEW                  |      |   101K|    17M|   308   (1)| 00:00:04 |
|*  4 |     SORT ORDER BY STOPKEY|      |   101K|  7940K|   308   (1)| 00:00:04 |
|   5 |      TABLE ACCESS FULL   | T    |   101K|  7940K|   308   (1)| 00:00:04 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">29985)
   2 - filter(ROWNUM<30000)
   4 - filter(ROWNUM<30000)

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

从执行计划可以看到，如果取靠后的记录，oracle甚至放弃了索引扫描，因为成本太高了。
像这种情况，还有一种解决方法：

suk@ORA10G> SELECT T.*
  2  FROM   T
  3  WHERE  ROWID IN (SELECT RID
  4                   FROM   (SELECT A.RID, ROWNUM RN
  5                           FROM   (SELECT ROWID RID FROM T ORDER BY OBJECT_NAME DESC) A
  6                           WHERE  ROWNUM < 30000)
  7                   WHERE  RN >= 30000 - 15);

已选择15行。

已用时间:  00: 00: 00.07

执行计划
----------------------------------------------------------
Plan hash value: 695960827

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |     1 |   103 |   550   (1)| 00:00:07 |
|   1 |  NESTED LOOPS                    |                   |     1 |   103 |   550   (1)| 00:00:07 |
|   2 |   VIEW                           | VW_NSO_1          | 29999 |   351K|   408   (1)| 00:00:05 |
|   3 |    HASH UNIQUE                   |                   |     1 |   732K|            |          |
|*  4 |     VIEW                         |                   | 29999 |   732K|   408   (1)| 00:00:05 |
|*  5 |      COUNT STOPKEY               |                   |       |       |            |          |
|   6 |       VIEW                       |                   |   101K|  1191K|   408   (1)| 00:00:05 |
|   7 |        INDEX FULL SCAN DESCENDING| IDX_T_OBJECT_NAME |   101K|  2878K|   408   (1)| 00:00:05 |
|   8 |   TABLE ACCESS BY USER ROWID     | T                 |     1 |    91 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("RN">=29985)
   5 - filter(ROWNUM<30000)

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

这种方法是先取到当前页需要的rowid，然后再回表扫描。它和上面的方法区别在于：原来的方法是先取数据，再分页；这种方法是先分页，再取数据。
最主要的差别在于排序原来的方法需要排序；后一种方法不需要排序。        

用最后一种介绍的方法，无论是取靠前的页数还是靠后的页数，性能都比原来的高。但是最后一种方法有一个前提：
1) 只返回单个表的字段
2) 排序字段和返回的字段属于同一张表