作者 | JiekeXu
来源 | JiekeXu DBA之路（ID: JiekeXu_IT）
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看一下 Oracle 单表分页查询优化，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！
近日中午一开发过来说生产有条 SQL 执行缓慢，让看一下执行计划。测试环境说也有同样的问题 SQL 且数据量一样，那么则开始在测试环境搞一搞吧，排查过程大概记录如下,对于优化也就是一知半解，故此只能抛砖引玉，如有错误还望指正。
开发发过来的分页 SQL 如下(敏感信息均已过滤转换)，由于篇幅问题，这里不放入 PLSQL 格式化展开了。
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、查看统计信息收集时间，防止统计信息过旧
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、查看创建索引情况
如下只有三个索引
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 子句中。
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、查看表是否分区（如下未分区）
SQL> SELECT TABLE_NAME,PARTITIONED FROM DBA_TABLES WHERE TABLE_NAME='T_ORDER_INFO' AND OWNER='PROD';
TABLE_NAME PAR
------------------------------ ---
T_ORDER_INFO NO5、查看表的大小
SQL> select sum(bytes)/1024/1024 MB from dba_segments where OWNER='PROD' and SEGMENT_NAME ='T_ORDER_INFO';
MB
----------
768360多万，768M也算不上大表，对于合理的分页查询应该也没有什么问题，
下面就需要看看执行计划了。
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 或者 v$SQL 等视图获取，这里通过最简单的 v$SQL 视图获取。
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 角色授予普通用户即可，方法如下：
cd $ORACLE_HOME/sqlplus/admincat plustrce.sqlSQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sqlSQL> SQL> drop role plustrace;drop role plustrace *ERROR at line 1:ORA-01919: role 'PLUSTRACE' does not existSQL> 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 offSQL> grant PLUSTRACE to PROD;Grant succeeded.SQL> set autot onSQL> conn PROD/LKKBtd7$Connected.SQL> set autot onSQL> SQL> set AUTOT TRACEExecution 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 也是走了此索引，但是效果不佳，那么我们尝试创建一个联合索引来看看。
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 时执行计划中排序则不可避免，没有充分利用索引有序的特性，故需删除按照上面语法重新创建较好一丢丢。
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 评估错误，走错误执行计划，影响业务系统。
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 还是达不到最优，请继续往下看。
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;执行计划如下：
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 看一眼真实执行计划也是一样使用旧索引，还有排序操作。
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，还是没有达到最优。
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 格式化如下：
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;我们来看一看执行计划
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。
~本次分享到此结束啦~
❤️ 欢迎关注我的公众号，来一起玩耍吧！！！
——————————————————————--—--————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————----———
VMWARE16 Oracle Linux7.9 安装 Oracle19c RAC 详细配置方案
使用 VMware 16 RHEL7.7 虚拟机静默安装 Oracle 19c RAC
爆肝一万字终于把 Oracle Data Guard 核心参数搞明白了
Oracle 12c 及以上版本补丁更新说明及下载方法（收藏版）
Oracle 19c 19.10DBRU 最新补丁升级看这一篇就够了
Redhat 7.7 安装最新版 MongoDB 5.0.1 手册
ASM 管理的内部工具：KFED、KFOD、AMDU
性能优化|关于数据库历史性能问题的一道面试题
一线运维 DBA 五年经验常用 SQL 大全（二）
ORA-00349|激活 ADG 备库时遇到的问题
OGG-01004|OGG 初始化数据问题处理
Oracle 轻量级实时监控工具 oratop
Linux 7.7 源码安装 MySQL 8.0.26
MySQL OCP 认证考试你知道吗？
Oracle 19C RAC 安装遇到的坑
国产数据库|TiDB 5.0 快速体验
Oracle 参数文件三两事儿
Oracle 每日一题系列合集
2992
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
