今天又有人在问分页语句的问题:为什么最后的分页会慢。
这儿我们通过执行计划来分析一下,首先建立测试用表。
drop table test1 purge;
create table test1 as select * from dba_objects;
SQL> select count(*) from test1;
COUNT(*)
----------
87028
SQL> set autotrace traceonly
SELECT object_id, object_name
FROM (SELECT rownum AS sn, a.*
FROM (SELECT object_id, object_name FROM test1 ORDER BY object_id) a
WHERE rownum <= 100) a
WHERE sn >= 90;
Elapsed: 00:00:00.34
Execution Plan
----------------------------------------------------------
Plan hash value: 3077351352
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 9200 | 4 (0)| 00:00:01 |
|* 1 | VIEW | | 100 | 9200 | 4 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 90705 | 6997K| 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST1 | 90705 | 6997K| 4 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_TEST1 | 101 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
SQL> set autotrace off
SQL> /
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
91 I_NTAB3
92 REFCON$
93 I_REFCON1
94 I_REFCON2
95 OPQTYPE$
96 I_OPQTYPE1
97 UGROUP_SEQUENCE
98 PROPS$
99 EDITION$
100 ORA$BASE
101 FIXED_OBJ$
Elapsed: 00:00:00.04
SQL> select * from table(dbms_xplan.display_cursor(sql_id =>null,cursor_child_no =>0,format =>'ALL ALLSTATS LAST NOTE ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5tjrjx4kzqbzz, child number 1
-------------------------------------
SELECT object_id, object_name FROM (SELECT rownum AS sn, a.*
FROM (SELECT object_id, object_name FROM test1 ORDER BY object_id) a
WHERE rownum <= 100) a WHERE sn >= 90
Plan hash value: 3077351352
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 11 |00:00:00.02 | 6 |
|* 1 | VIEW | | 1 | 100 | 9200 | 4 (0)| 00:00:01 | 11 |00:00:00.02 | 6 |
|* 2 | COUNT STOPKEY | | 1 | | | | | 100 |00:00:00.02 | 6 |
| 3 | VIEW | | 1 | 90705 | 6997K| 4 (0)| 00:00:01 | 100 |00:00:00.01 | 6 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 90705 | 6997K| 4 (0)| 00:00:01 | 100 |00:00:00.01 | 6 |
| 5 | INDEX FULL SCAN | PK_TEST1 | 1 | 101 | | 2 (0)| 00:00:01 | 100 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------------------------------
有细心的读者或许会发现两个plan的不同。
第二个里面A-Rows只有100,这说明两个问题:
1、分页语句不要用autotrace看plan。
2、分页语句读取了100行后便返回了,所以前面几页一般都比较快。
那么后面的几页呢?
看下面的语句及plan
SELECT object_id, object_name
FROM (SELECT rownum AS sn, a.*
FROM (SELECT object_id, object_name FROM test1 ORDER BY object_id) a
WHERE rownum <= 80100) a
5 WHERE sn >= 80090;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
80938 STATUS_REC
80939 STATUS_REC_ARR
80940 AUDIT_OBJ
80941 AUDIT_ARRAY
80942 MGMT_AUDIT_CUSTOM_ATTRIB_OBJ
80943 MGMT_AUDIT_CUSTOM_ATTRIB_ARRAY
80944 MGMTCHRNSREGIONMEMBERS
80945 MGMTCHRNSEVENTURLINFO
80946 MGMTCHRNSEVENTURLINFOTABLE
80947 MGMTCHRNSTGTGUID
80948 MGMTCHRNSTGTGUIDS
Elapsed: 00:00:10.43
SQL> select * from table(dbms_xplan.display_cursor(sql_id =>null,cursor_child_no =>0,format =>'ALL ALLSTATS LAST NOTE ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9gpabx9hxwpc0, child number 0
-------------------------------------
SELECT object_id, object_name FROM (SELECT rownum AS sn, a.*
FROM (SELECT object_id, object_name FROM test1 ORDER BY object_id) a
WHERE rownum <= 80100) a WHERE sn >= 80090
Plan hash value: 3077351352
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1357 (100)| | 11 |00:00:10.08 | 1368 |
|* 1 | VIEW | | 1 | 80100 | 7196K| 1357 (1)| 00:00:17 | 11 |00:00:10.08 | 1368 |
|* 2 | COUNT STOPKEY | | 1 | | | | | 80100 |00:00:08.73 | 1368 |
| 3 | VIEW | | 1 | 90705 | 6997K| 1357 (1)| 00:00:17 | 80100 |00:00:06.32 | 1368 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 90705 | 6997K| 1357 (1)| 00:00:17 | 80100 |00:00:03.86 | 1368 |
| 5 | INDEX FULL SCAN | PK_TEST1 | 1 | 80101 | | 173 (0)| 00:00:03 | 80100 |00:00:01.23 | 168 |
-------------------------------------------------------------------------------------------------------------------------------------
可以看到A-Rows为80100,几乎访问该表所有的行。
访问的行数差异大,速度差异大也就相当正常了。