其他
雏鹰展翅|Oracle 单表分页查询优化
作者 | 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 VALIDPROD T_ORDER_INFO_INDEX1 PROD T_ORDER_INFO PROD_DATA 2021-10-28 17:36:25 VALIDPROD 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 999select 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 2021Version 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 - ProductionVersion 18.4.0.0.0SQL> 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 dualcaabf41shnbhg select sysdate+1 from dual7h35uxf5uhmm1 select sysdate from dual2xj6w1v02awg8 select 3+2 from dual1rpdcakmvm41w 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 dualcaabf41shnbhg select sysdate+1 from dual7h35uxf5uhmm1 select sysdate from dual2xj6w1v02awg8 select 3+2 from dual1rpdcakmvm41w 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 TRACESP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabledSP2-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';conn xxxx/xxxxset autot onExecution 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/PROD1245alter session set Statistics_level=all;执行上述分页 SQLselect * 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
————————————————————————----———