一步一脚印

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

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) 排序字段和返回的字段属于同一张表