大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看一下 Oracle 单表分页查询优化
近日中午一开发过来说生产有条 SQL 执行缓慢，让看一下执行计划。测试环境说也有同样的问题 SQL 且数据量一样，那么则开始在测试环境搞一搞吧，排查过程大概记录如下,对于优化也就是一知半解，故此只能抛砖引玉，如有错误还望指正。
开发发过来的分页 SQL 如下(敏感信息均已过滤转换)，由于篇幅问题，这里不放入 PLSQL 格式化展开了。
代码语言：javascript复制select "v0" "SEQUENCE_NO", "v1" "PK_DELIVER_INFO", "v2" "TRANS_NO", "v3" "AGENT_ID", "v4" "TRANS_TIME", "v5" "RST_CODE", "v6" "RST_MESS", "v7" "COUNT", "v8" "QUARTER", "v9" "QUERY_BEG_DATE", "v10" "QUERY_END_DATE", "v11" "START_INDEX", "v12" "ITEMS_COUNT", "v13" "PULL_WAY", "v14" "CREATE_TIME", "v15" "UPDATE_TIME", "v16" "DELETE_TIME" from (select "x"."v0", "x"."v1", "x"."v2", "x"."v3", "x"."v4", "x"."v5", "x"."v6", "x"."v7", "x"."v8", "x"."v9", "x"."v10", "x"."v11", "x"."v12", "x"."v13", "x"."v14", "x"."v15", "x"."v16", rownum "rn" from (select "T_ORDER_INFO"."SEQUENCE_NO" "v0", "T_ORDER_INFO"."PK_DELIVER_INFO" "v1", "T_ORDER_INFO"."TRANS_NO" "v2", "T_ORDER_INFO"."AGENT_ID" "v3", "T_ORDER_INFO"."TRANS_TIME" "v4", "T_ORDER_INFO"."RST_CODE" "v5", "T_ORDER_INFO"."RST_MESS" "v6", "T_ORDER_INFO"."COUNT" "v7", "T_ORDER_INFO"."QUARTER" "v8", "T_ORDER_INFO"."QUERY_BEG_DATE" "v9", "T_ORDER_INFO"."QUERY_END_DATE" "v10", "T_ORDER_INFO"."START_INDEX" "v11", "T_ORDER_INFO"."ITEMS_COUNT" "v12", "T_ORDER_INFO"."PULL_WAY" "v13", "T_ORDER_INFO"."CREATE_TIME" "v14", "T_ORDER_INFO"."UPDATE_TIME" "v15", "T_ORDER_INFO"."DELETE_TIME" "v16" from "T_ORDER_INFO" where (1 = 1 and "T_ORDER_INFO"."AGENT_ID" = 'C002374') order by "v10" desc, "v2" desc) "x"
where rownum <= (0 1)) where "rn" > 0 order by "rn";下面来一起看看吧。
测试数据库版本及补丁信息 Linux 6.10 11204 RAC SQL*Plus: Release 11.2.0.4.0 Database Patch Set Update : 11.2.0.4.190416
1、查看统计信息收集时间，防止统计信息过旧
代码语言：javascript复制SQL> set line 345
SQL> select TABLE_NAME,OWNER,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name='T_ORDER_INFO' and owner='PROD';
TABLE_NAME OWNER NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
T_ORDER_INFO PROD 3625092 2021-10-28 17:36:25
SQL> select count(*) from PROD.T_ORDER_INFO;
COUNT(*)
----------
36250922、查看创建索引情况
如下只有三个索引
代码语言：javascript复制SQL> select owner,index_name,table_owner,table_name,tablespace_name,last_analyzed,status from dba_indexes where table_name='T_ORDER_INFO' and table_owner='PROD';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME LAST_ANALYZED STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------- --------
PROD T_ORDER_INFO_UK1 PROD T_ORDER_INFO PROD_DATA 2021-10-28 17:36:25 VALID
PROD T_ORDER_INFO_INDEX1 PROD T_ORDER_INFO PROD_DATA 2021-10-28 17:36:25 VALID
PROD T_ORDER_INFO_PK PROD T_ORDER_INFO PROD_DATA 2021-10-28 17:36:25 VALID3、查看索引所在的列
序列为主键索引，PK_DELIVER_INFO 列为唯一索引，普通索引刚好在 AGENT_ID 我们最开始的 where 子句中。
代码语言：javascript复制SET LINE 234
COL INDEX_OWNER FOR A30
COL TABLE_OWNER FOR A15
COL TABLE_NAME FOR A25
COL INDEX_NAME FOR A28
COL COLUMN_NAME FOR A20
SELECT INDEX_OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='&tablename' ORDER BY INDEX_NAME;
SQL> SELECT INDEX_OWNER,TABLE_OWNER,TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS WHERE TABLE_NAME='T_ORDER_INFO' and table_owner='PROD';
INDEX_OWNER TABLE_OWNER TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ --------------- --------------- ------------------ --------------------
PROD PROD T_ORDER_DELIVER T_ORDER_DELIVER_IN SEQUENCE_NO
_INFO FO_PK
PROD PROD T_ORDER_DELIVER T_ORDER_DELIVER_IN AGENT_ID
_INFO FO_INDEX1
PROD PROD T_ORDER_DELIVER T_ORDER_DELIVER_IN PK_DELIVER_INFO
_INFO FO_UK14、查看表是否分区（如下未分区）
代码语言：javascript复制SQL> SELECT TABLE_NAME,PARTITIONED FROM DBA_TABLES WHERE TABLE_NAME='T_ORDER_INFO' AND OWNER='PROD';
TABLE_NAME PAR
------------------------------ ---
T_ORDER_INFO NO5、查看表的大小
代码语言：javascript复制SQL> select sum(bytes)/1024/1024 MB from dba_segments where OWNER='PROD' and SEGMENT_NAME ='T_ORDER_INFO';
MB
----------
768360多万，768M也算不上大表，对于合理的分页查询应该也没有什么问题，
下面就需要看看执行计划了。
代码语言：javascript复制SQL> select TABLE_NAME,OWNER,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name='T_ORDER_INFO' and owner='PROD';
TABLE_NAME OWNER NUM_ROWS LAST_ANALYZED
--------------- ------------------------------ ---------- -------------------
T_ORDER_INFO PROD 3625092 2021-11-05 16:32:116、首先需要拿到原 SQL 的 SQL_ID.
可以通过 awr、ash 或者 vSQL 等视图获取，这里通过最简单的 vSQL 视图获取。
代码语言：javascript复制set long 9999 line 999 pages 999
select sql_id,SQL_FULLTEXT from v$sql where sql_text not like '%like%' and sql_text like '%T_ORDER_INFO%';
SQL> @?/rdbms/admin/sqltrpt.sql
--这里顺便说一嘴 sqltrpt，也是一个很不错的工具，小伙伴们可以试试。
ORACLE 10g 以后提供了一个脚本 sqltrpt.sql 用来查询最耗费资源的 SQL 语句，也可以根据输入的 SQL_ID，生成对应执行计划和调优建议，
是一个不错的调优优化脚本，其实是 sqltrpt 是 SQL Tune Report 的缩写。这个脚本位于 $ORACLE_HOME/rdbms/admin/sqltrpt.sql这里根据 sql_text 查到的 SQL_ID 如下：
5b2zcwhm267q8
sql_id 一般通过 awr、ash 报告，监控工具等获取到，v$sql 如上查询会出现很多个SQL。下面通过墨天轮 18c 云环境简单模拟一个例子说明如何通过添加一个类似 HINT 来准确定位 SQL_ID，
[oracle@modb admin]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Nov 29 22:05:23 2021
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> select 3 2 from dual;
3 2
----------
5
SQL> select sysdate from dual;
SYSDATE
---------
29-NOV-21
SQL> select sysdate 1 from dual;
SYSDATE 1
---------
30-NOV-21
SQL> select sysdate as current_time from dual;
SQL>
CURRENT_T
---------
29-NOV-21
SQL> select sql_id,SQL_FULLTEXT from v$sql where sql_text not like '%like%' and sql_text like '%dual';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
8uukcvcags9qr select /* current_sql_ID */ sysdate from dual
caabf41shnbhg select sysdate 1 from dual
7h35uxf5uhmm1 select sysdate from dual
2xj6w1v02awg8 select 3 2 from dual
1rpdcakmvm41w select sysdate as current_time from dual
SQL> select /* current_sql_ID */ sysdate from dual;
SYSDATE
---------
29-NOV-21
SQL> select sql_id,SQL_FULLTEXT from v$sql where sql_text not like '%like%' and sql_text like '%dual';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
8uukcvcags9qr select /* current_sql_ID */ sysdate from dual
caabf41shnbhg select sysdate 1 from dual
7h35uxf5uhmm1 select sysdate from dual
2xj6w1v02awg8 select 3 2 from dual
1rpdcakmvm41w select sysdate as current_time from dual
SQL> select sql_id,SQL_FULLTEXT from v$sql where sql_text not like '%like%' and sql_text like '%current_sql_ID%';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
8uukcvcags9qr select /* current_sql_ID */ sysdate from dual
SQL>
--这里通过添加类似 hint 的方式找到 sql_id 为 "8uukcvcags9qr" 的 SQL。7、查看执行计划
知道 sql_id 后便可以根据多种办法查看执行计划。关于执行计划多种查看方法，可查看之前的文章链接 。awrsqrpt、display_awr、display_cursors 等等。
@?/rdbms/admin/awrsqrpt.sql
Select * from table(dbms_xplan.display_awr('5b2zcwhm267q8'));
此 SQL 通过 display_cursors、AUTOTRACE 和 PLSQL 使用 F5 查看的执行计划均一样。SQL> set AUTOT TRACE
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report以上错误普通用户无法使用 AUTOTRACE ，需要执行脚本 plustrce.sql 创建 PLUSTRACE 角色授予普通用户即可，方法如下：
代码语言：javascript复制cd $ORACLE_HOME/sqlplus/admin
cat plustrce.sql
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
SQL> grant PLUSTRACE to PROD;
Grant succeeded.
SQL> set autot on
SQL> conn PROD/LKKBtd7$
Connected.
SQL> set autot on
SQL>
SQL> set AUTOT TRACE
Execution Plan
----------------------------------------------------------
Plan hash value: 3374223308------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 8160 | | 4031 (1)| 00:00:49 |
| 1 | SORT ORDER BY | | 10 | 8160 | | 4031 (1)| 00:00:49 |
|* 2 | VIEW | | 10 | 8160 | | 4030 (1)| 00:00:49 |
|* 3 | COUNT STOPKEY | | | | | | |
| 4 | VIEW | | 46319 | 35M| | 4030 (1)| 00:00:49 |
|* 5 | SORT ORDER BY STOPKEY | | 46319 | 8594K| 10M| 4030 (1)| 00:00:49 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 46319 | 8594K| | 2111 (1)| 00:00:26 |
|* 7 | INDEX RANGE SCAN | T_ORDER_INFO_INDEX1 | 46319 | | | 469 (0)| 00:00:06 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("rn">0) 3 - filter(ROWNUM<=10) 5 - filter(ROWNUM<=10) 7 - access("T_ORDER_INFO"."AGENT_ID"='C002282')SQL> set line 456 pages 456
SQL> Select * from table(dbms_xplan.display_cursor('5b2zcwhm267q8'));
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1943 (100)| |
| 1 | SORT ORDER BY | | 10 | 8160 | | 1943 (1)| 00:00:24 |
|* 2 | VIEW | | 10 | 8160 | | 1942 (1)| 00:00:24 |
|* 3 | COUNT STOPKEY | | | | | | |
| 4 | VIEW | | 23300 | 17M| | 1942 (1)| 00:00:24 |
|* 5 | SORT ORDER BY STOPKEY | | 23300 | 4323K| 5336K| 1942 (1)| 00:00:24 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 23300 | 4323K| | 976 (1)| 00:00:12 |
|* 7 | INDEX RANGE SCAN | T_ORDER_INFO_INDEX1 | 23300 | | | 220 (0)| 00:00:03 |
------------------------------------------------------------------------------------------------------------------------
执行计划中有排序，而且成本 cost 也很高，autotrace 出来的达 4031。
8、优化此 SQL
AGENT_ID 建有索引，该 SQL 也是走了此索引，但是效果不佳，那么我们尝试创建一个联合索引来看看。
代码语言：javascript复制create index PROD.T_ORD_INFO_IDQUERY_TRANSNO on PROD.T_ORDER_INFO(AGENT_ID,QUERY_END_DATE desc,TRANS_NO desc) tablespace PROD_INDEX online;注意如果建立如下索引，执行计划则会出现 INDEX RANGE SCAN DESCENDING，物理读变为 3，其他基本一样，但是使用 11 节的分页 SQL 时执行计划中排序则不可避免，没有充分利用索引有序的特性，故需删除按照上面语法重新创建较好一丢丢。
代码语言：javascript复制create index PROD.T_ORD_INFO_IDQUERY_TRANSNO on PROD.T_ORDER_INFO(AGENT_ID,QUERY_END_DATE,TRANS_NO) tablespace PROD_INDEX online;
drop index PROD.T_ORD_INFO_IDQUERY_TRANSNO;9、收集表统计信息
创建完索引，最好可以收集一下统计信息，以防其他 SQL 评估错误，走错误执行计划，影响业务系统。
代码语言：javascript复制exec dbms_stats.gather_table_stats(ownname => 'PROD', tabname => 'T_ORDER_INFO');
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select TABLE_NAME,OWNER,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name like 'T_ORDER_INFO' and owner='PROD';10、查看执行计划conn xxxx/xxxx
set autot on
Execution Plan
----------------------------------------------------------
Plan hash value: 3879506888
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 8160 | 9 (12)| 00:00:01 |
| 1 | SORT ORDER BY | | 10 | 8160 | 9 (12)| 00:00:01 |
|* 2 | VIEW | | 10 | 8160 | 8 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 11 | 8833 | 8 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 37872 | 7064K| 8 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T_ORD_INFO_IDQUERY_TRANSNO | 11 | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
2 physical reads
0 redo size
2765 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed原有执行计划中有SORT ORDER BY的排序操作也已经消除了，Cost 成本值降低至 9，与原来的 4031 相比，提高了440 多倍。那么该 SQL 还有优化的空间吗？
11、根据《SQL优化核心思想》8.3 一节
分页优化思想改写的 SQL 如下：
select * from (select * from (select a.*,rownum rn from (分页SQL） a) where rownum<=10) where rn >=1;
不知道是不是作者笔误，多写了一层 select 还是怎么的，根据此分页框架改写如下 SQL 还是达不到最优，请继续往下看。
代码语言：javascript复制select * from (select *
from (select a.*,rownum rn from
(select "T_ORDER_INFO"."SEQUENCE_NO" "v0",
"T_ORDER_INFO"."PK_DELIVER_INFO" "v1",
"T_ORDER_INFO"."TRANS_NO" "v2",
"T_ORDER_INFO"."AGENT_ID" "v3",
"T_ORDER_INFO"."TRANS_TIME" "v4",
"T_ORDER_INFO"."RST_CODE" "v5",
"T_ORDER_INFO"."RST_MESS" "v6",
"T_ORDER_INFO"."COUNT" "v7",
"T_ORDER_INFO"."QUARTER" "v8",
"T_ORDER_INFO"."QUERY_BEG_DATE" "v9",
"T_ORDER_INFO"."QUERY_END_DATE" "v10",
"T_ORDER_INFO"."START_INDEX" "v11",
"T_ORDER_INFO"."ITEMS_COUNT" "v12",
"T_ORDER_INFO"."PULL_WAY" "v13",
"T_ORDER_INFO"."CREATE_TIME" "v14",
"T_ORDER_INFO"."UPDATE_TIME" "v15",
"T_ORDER_INFO"."DELETE_TIME" "v16"
from "T_ORDER_INFO"
where ("T_ORDER_INFO"."AGENT_ID" = 'C002282')
order by "v10" desc, "v2" desc
）
a) where rownum<=10
)
where rn >=1;执行计划如下：
代码语言：javascript复制Plan hash value: 2456897122
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 8160 | | 3162 (1)| 00:00:38 |
|* 1 | VIEW | | 10 | 8160 | | 3162 (1)| 00:00:38 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 37872 | 29M| | 3162 (1)| 00:00:38 |
| 4 | COUNT | | | | | | |
| 5 | VIEW | | 37872 | 29M| | 3162 (1)| 00:00:38 |
| 6 | SORT ORDER BY | | 37872 | 7064K| 8672K| 3162 (1)| 00:00:38 |
| 7 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 37872 | 7064K| | 1579 (1)| 00:00:19 |
|* 8 | INDEX RANGE SCAN | T_ORDER_INFO_INDEX1 | 37872 | | | 348 (0)| 00:00:05 |
-------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1173 consistent gets
0 physical reads
0 redo size
2729 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed使用原有索引 T_ORDER_INFO_INDEX1，并没有用到新的联合索引，原有执行计划中有 SORT ORDER BY 的排序操作也没有消除了，Cost 成本值有原来的 4031 降低至 3162，效果不是很明显.使用 Statistics_level 看一眼真实执行计划也是一样使用旧索引，还有排序操作。
代码语言：javascript复制grant select any dictionary to PROD;
conn PROD/PROD1245
alter session set Statistics_level=all;
执行上述分页 SQL
select * from table(dbms_xplan.display_cursor(null,null,‘allstats last’));
那么，我们强制使用 hint 走联合索引在看看，结果 cost 值达 13303，还是没有达到最优。
代码语言：javascript复制select /* index(T_ORDER_INFO T_ORD_INFO_IDQUERY_TRANSNO) */ "T_ORDER_INFO"
Execution Plan
----------------------------------------------------------
Plan hash value: 4173602263
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 8160 | 13303 (1)| 00:02:40 |
|* 1 | VIEW | | 10 | 8160 | 13303 (1)| 00:02:40 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 41284 | 32M| 13303 (1)| 00:02:40 |
| 4 | COUNT | | | | | |
| 5 | VIEW | | 41284 | 31M| 13303 (1)| 00:02:40 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 41284 | 7700K| 13303 (1)| 00:02:40 |
|* 7 | INDEX RANGE SCAN | T_ORD_INFO_IDQUERY_TRANSNO | 41284 | | 469 (0)| 00:00:06 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
7 - access("T_ORDER_INFO"."AGENT_ID"='C002282')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
2 physical reads
0 redo size
2729 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 processed12、最高效的单表分页查询写法
看过老虎刘老师的最高效分页查询语句架构只有三层，我们来套用看看。
select column_lists from (select rownum as rn,A.* from (select column_lists from table_name where col_1=:b0 order by col_2) A where rownum<=:b2 ) where rn>:b1;
改写完 SQL 格式化如下：
代码语言：javascript复制select "v0",
"v1",
"v2",
"v3",
"v4",
"v5",
"v6",
"v7",
"v8",
"v9",
"v10",
"v11",
"v12",
"v13",
"v14",
"v15",
"v16"
from (select rownum as rn, A.*
from (select "T_ORDER_INFO"."SEQUENCE_NO" "v0",
"T_ORDER_INFO"."PK_DELIVER_INFO" "v1",
"T_ORDER_INFO"."TRANS_NO" "v2",
"T_ORDER_INFO"."AGENT_ID" "v3",
"T_ORDER_INFO"."TRANS_TIME" "v4",
"T_ORDER_INFO"."RST_CODE" "v5",
"T_ORDER_INFO"."RST_MESS" "v6",
"T_ORDER_INFO"."COUNT" "v7",
"T_ORDER_INFO"."QUARTER" "v8",
"T_ORDER_INFO"."QUERY_BEG_DATE" "v9",
"T_ORDER_INFO"."QUERY_END_DATE" "v10",
"T_ORDER_INFO"."START_INDEX" "v11",
"T_ORDER_INFO"."ITEMS_COUNT" "v12",
"T_ORDER_INFO"."PULL_WAY" "v13",
"T_ORDER_INFO"."CREATE_TIME" "v14",
"T_ORDER_INFO"."UPDATE_TIME" "v15",
"T_ORDER_INFO"."DELETE_TIME" "v16"
from "T_ORDER_INFO"
where "T_ORDER_INFO"."AGENT_ID" = 'C002282'
order by "v10" desc, "v2" desc) A
where rownum <= 10)
where rn > 0;我们来看一看执行计划
代码语言：javascript复制Execution Plan
----------------------------------------------------------
Plan hash value: 2845846103
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 8160 | 8 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 8160 | 8 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 11 | 8833 | 8 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 37872 | 7064K| 8 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_ORD_INFO_IDQUERY_TRANSNO | 11 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
2635 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
Plan hash value: 2845846103
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 10 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 10 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 10 |
| 3 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 10 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_ORDER_INFO | 1 | 41284 | 10 |00:00:00.01 | 10 |
|* 5 | INDEX RANGE SCAN | T_ORD_INFO_IDQUERY_TRANSNO | 1 | 10 | 10 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------------------------------------执行计划中没有排序，没有物理读，cost 只有 8，查询结果秒出，这才是最优的结果。
最后来一起看看落落大神总结的分页优化思路：
单表分页语句优化思路：如果分页语句中有排序（order by），要利用索引已经排序特性，将order by的列按照排序的先后顺序包含在索引中，同时要注意排序是升序还是降序。如果分页语句中有过滤条件，我们要注意过滤条件是否有等值过滤条件，如果有等值过滤条件，要将等值过滤条件优先组合在一起，然后将排序列放在等值过滤条件后面，最后将非等值过滤列放排序列后面。如果分页语句中没有等值过滤条件，我们应该先将排序列放在索引前面，将非等值过滤列放后面，最后利用rownum的COUNT STOPKEY特性来优化分页SQL。如果分页中没有过滤条件，可以将排序列和常量组合(object_name,0)创建索引。如果分页中没有排序，可以直接利用rownum的COUNT STOPKEY特性来优化分页SQL。
如果我们想一眼看出分页语句执行计划是正确还是错误的，先看分页语句有没有ORDER BY，再看执行计划有没有SORT ORDER BY，如果执行计划中有SORTORDER BY，执行计划一般都是错误的分页语句中也不能有distinct、group by、max、min、avg、union、union all等关键字。因为当分页语句中有这些关键字，我们需要等表关联完或者数据都跑完之后再来分页，这样性能很差。
多表关联分页优化思路:多表关联分页语句，如果有排序，只能对其中一个表进行排序，让参与排序的表作为嵌套循环的驱动表，并且要控制驱动表返回的数据顺序与排序的顺序一致，其余表的连接列要创建好索引。 如果有外连接，我们只能选择主表的列作为排序列，语句中不能有distinct、group by、max、min、avg、union、union all，执行计划中不能出现SORT ORDER BY。
