[20250530]关于分页查询的问题.txt
[20250530]关于分页查询的问题.txt
--//昨天看链接https://jonathanlewis.wordpress.com/2025/05/27/pagination-cost-2/
--//提到分页查询使用绑定变量的问题自己重复测试看看。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.建立测试表：
SCOTT@book01p> create table t1 as select * from all_objects where rownum <= 50000 order by dbms_random.value;
Table created.
SCOTT@book01p> create index t1_i1 on t1(object_name);
Index created.
--//分析表略。
3.测试1：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> select owner, object_type, object_name from t1 order by object_name offset 10 rows fetch next 20 rows only;
OWNER OBJECT_TYPE OBJECT_NAME
---------------------- --------------- --------------------
SYS TABLE ACTIVITY_CONFIG$
SYS TABLE PARTITION ACTIVITY_MVIEW$
SYS TABLE ACTIVITY_MVIEW$
SYS TABLE ACTIVITY_SNAP_META$
SYS TABLE PARTITION ACTIVITY_TABLE$
SYS TABLE ACTIVITY_TABLE$
SYS VIEW ADBTASK_CLIENT
PUBLIC SYNONYM ADBTASK_CLIENT
SYS VIEW ADBTASK_SCHEDULE
PUBLIC SYNONYM ADBTASK_SCHEDULE
PUBLIC SYNONYM ADBTASK_SETTINGS
SYS VIEW ADBTASK_SETTINGS
REMOTE_SCHEDULER_AGENT PROCEDURE ADD_AGENT_CERTIFICATE
SYS TABLE ADMINAUTH$
SYS TABLE ADO_IMPARAM$
SYS TABLE ADO_IMSEGSTAT$
SYS TABLE ADO_IMSEGTASKDETAILS$
SYS TABLE ADO_IMSTAT$
SYS TABLE ADO_IMTASKS$
SYS TYPE ADR_HOME_T
20 rows selected.
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3uvg4dgwxp5q9, child number 0
-------------------------------------
select owner, object_type, object_name from t1 order by object_name
offset 10 rows fetch next 20 rows only
Plan hash value: 3254925009
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 33 (100)| | 20 |00:00:00.01 | 34 |
|* 1 | VIEW | | 1 | 30 | 6720 | 33 (0)| 00:00:01 | 20 |00:00:00.01 | 34 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 30 | 1530 | 33 (0)| 00:00:01 | 30 |00:00:00.01 | 34 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 50000 | 2490K| 33 (0)| 00:00:01 | 30 |00:00:00.01 | 34 |
| 4 | INDEX FULL SCAN | T1_I1 | 1 | 30 | | 3 (0)| 00:00:01 | 30 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "from$_subquery$_002"@"SEL$2"
2 - SEL$1
3 - SEL$1 / "T1"@"SEL$1"
4 - SEL$1 / "T1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=30 AND "from$_subquery$_002"."rowlimit_$$_rownumber">10))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=30)
--//可以发现在指定文字变量的情况下，id=4走的是索引，实际上仅仅访问30条，直接回表查询。
--//讲一句话也访问靠前的索引优势越明显。
4.测试2：
--//如果使用绑定变量
variable offset_size number
variable fetch_size number
begin
:offset_size := 10; :fetch_size := 20;
end;
/
SCOTT@book01p> select owner, object_type, object_name from t1 order by object_name offset :offset_size rows fetch next :fetch_size rows only;
OWNER OBJECT_TYPE OBJECT_NAME
----- ------------ ------------------------------
SYS TABLE ACTIVITY_CONFIG$
...
SYS TYPE ADR_HOME_T
20 rows selected.
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6156ygkf7n77g, child number 0
-------------------------------------
select owner, object_type, object_name from t1 order by object_name
offset :offset_size rows fetch next :fetch_size rows only
Plan hash value: 1024497473
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 937 (100)| | 20 |00:00:00.01 | 1069 | | | |
|* 1 | VIEW | | 1 | 50000 | 10M| | 937 (1)| 00:00:01 | 20 |00:00:00.01 | 1069 | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 50000 | 2490K| 3160K| 937 (1)| 00:00:01 | 30 |00:00:00.01 | 1069 | 11264 | 11264 |10240 (0)|
|* 3 | FILTER | | 1 | | | | | | 50000 |00:00:00.01 | 1069 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 50000 | 2490K| | 298 (1)| 00:00:01 | 50000 |00:00:00.01 | 1069 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "from$_subquery$_002"@"SEL$2"
2 - SEL$1
4 - SEL$1 / "T1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE AND
"from$_subquery$_002"."rowlimit_$$_rownumber">:OFFSET_SIZE))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE)
3 - filter(:OFFSET_SIZE<GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE)
--//执行计划变成全表扫描，看看10053跟踪：
SCOTT@book01p> @ 10053x 6156ygkf7n77g 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4852_a6156ygkf7n77g.trc
SCOTT@book01p> @ 10053y
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."OWNER" "OWNER","from$_subquery$_002"."OBJECT_TYPE" "OBJECT_TYPE","from$_subquery$_002"."OB
JECT_NAME" "OBJECT_NAME" FROM (SELECT "T1"."OWNER" "OWNER","T1"."OBJECT_TYPE" "OBJECT_TYPE","T1"."OBJECT_NAME" "OBJECT_
NAME","T1"."OBJECT_NAME" "rowlimit_$_0",ROW_NUMBER() OVER ( ORDER BY "T1"."OBJECT_NAME") "rowlimit_$$_rownumber" FROM "S
COTT"."T1" "T1" WHERE :B1<GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:B2))),0)+:B3) "from$_subquery$_002" WHERE "from$_subquery$_0
02"."rowlimit_$$_rownumber"<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:B4))),0)+:B5 AND "from$_subquery$_002"."rowlimit_$$_rownu
mber">:B6 ORDER BY "from$_subquery$_002"."rowlimit_$_0"
--//格式化后如下：
/* Formatted on 2025-05-30 15:56:04 (QP5 v5.277) */
SELECT "from$_subquery$_002"."OWNER" "OWNER"
,"from$_subquery$_002"."OBJECT_TYPE" "OBJECT_TYPE"
,"from$_subquery$_002"."OBJECT_NAME" "OBJECT_NAME"
FROM (SELECT "T1"."OWNER" "OWNER"
,"T1"."OBJECT_TYPE" "OBJECT_TYPE"
,"T1"."OBJECT_NAME" "OBJECT_NAME"
,"T1"."OBJECT_NAME" "rowlimit_$_0"
,ROW_NUMBER () OVER (ORDER BY "T1"."OBJECT_NAME") "rowlimit_$$_rownumber"
FROM "SCOTT"."T1" "T1"
WHERE :B1 < GREATEST (FLOOR (TO_NUMBER (TO_CHAR ( :B2))), 0) + :B3) "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber" <= GREATEST (FLOOR (TO_NUMBER (TO_CHAR ( :B4))), 0) + :B5
AND "from$_subquery$_002"."rowlimit_$$_rownumber" > :B6
ORDER BY "from$_subquery$_002"."rowlimit_$_0"
--//https://jonathanlewis.wordpress.com/2025/05/27/pagination-cost-2/
This looks like bad news – we haven't taken advantage of an index to avoid visiting and sorting all the rows in the
table, operation 4 shows us a table scan passing 50,000 rows through a filter up to the window sort at operation 2 which
discards the 49,970 rows we definitely don't want before passing the remaining 30 rows to the view operation that
discards the first 10 that we needed to skip. Why don't we see the far more efficient index scan?
这看起来像坏消息，我们没有利用索引来避免访问和排序的所有行，操作4向我们展示一个表扫描通过50000行通过过滤器窗口排序操作2
丢弃49970行我们绝对不希望通过剩下的30行视图操作丢弃第一个10，我们需要跳过。为什么我们看不到更有效的索引扫描呢？
You may have noticed a couple of oddities in the Predicate Information.
您可能已经注意到了谓词信息中的一些奇怪之处。
Wherever you see the :offset_size bind variable the optimizer has wrapped it in to_number(to_char()) – why?! My first
thought about this was that the double conversion made it impossible for the optimizer to peek at the value and use it
to get a better estimate of cost, but that's (probably) not why the index full scan disappeared.
无论您看到：偏移_sizebind变量，优化器已经将它包装到_nuber(to_char())-为什么？!我的第一个想法是，双重转换使优化器不可能查
看值并使用它来更好地获得成本估计，但这(可能)不是索引全扫描消失的原因。
The offset and fetch first are both supposed to be numeric (according to the tram-tracks in the manual) so it seems a
little strange that Oracle treats just one of them to a double conversion.
偏移和先取都应该是数字的(根据手册中的有轨电车轨道)，所以Oracle只对待其中一个进行双重转换似乎有点奇怪。
What is that filter() in operation 3 actually trying to achieve? If you tidy up the messy bits it's just checking two
bind variables to make sure that the offset is less than the offset plus fetch size. This is just an example of
"conditional SQL". In this case it's following the pattern for "columnX between :bind1 and :bind2" – allowing Oracle to
short-circuit the sub-plan if the value of bind2 is less than that of bind1. (It wasn't needed for the example where we
used literals because Oracle could do the artithmetic at parse time and see that 10 was – and always would be – less
than 30.)
操作3中的filter()实际上试图实现什么？如果你整理了混乱的位，它只需要检查两个绑定变量，以确保偏移量小于偏移量加上获取大小
。这只是"有条件的SQL"的一个例子。在这种情况下，它遵循绑定1之间的模式-如果绑定2的值小于绑定1的值，允许Oracle短路子计划
。(在我们使用文字的例子中不需要这样做，因为Oracle可以在解析时做算术，并看到10是——而且总是是——小于30。)
What are the checks actually saying about the optimizer's (or developer's) expectation for the way you might use the
feature? The generated SQL actually allows for negative, non-integer values here. Negative offsets are replaced by zero,
negative fetch sizes result in the query short-circuiting and returning no data (in fact any fetech size strictly less
than 1 will return no rows).
关于优化器(或开发人员)对您可能使用该特性的方式的期望的检查是什么？生成的SQL实际上允许负的非整数值。负偏移量被零取代，负
读取大小导致查询短路并不返回任何数据(实际上任何严格小于1的fetech大小将不返回任何行)。
Hoping to find further clues about the poor choice of plan, I took a look at the "UNPARSED QUERY" from the CBO (10053)
trace, and cross-checked against the result from using the dbms_utility.expand_sql() procedure; the results were
(logically, though not cosmetically) the same. Here, with a little extra cosmetic tidying is the SQL the optimizer
actually works with:
为了找到更多关于计划选择不佳的线索，我查看了CBO(10053的)查询，并与使用dbms_utility.expand_sql()程序的结果进行了交叉核对
；结果(逻辑上，虽然不是外观上是相同的)。在这里，一些额外的美容整理是SQL实际工作的SQL：
--//作者给出的建议使用提示index(t1)，或者设置optimizer_mode=first_rows_10 or first_rows_100。
--//实际上分页的本质是减少回表的查询。上面的提示或者写法如果查询很后面的数据，回表记录很多依旧很慢。
exec :offset_size := 49000
exec :fetch_size := 20;
SCOTT@book01p> select /*+ index(t1) */ owner, object_type, object_name from t1 order by object_name offset :offset_size rows fetch next :fetch_size rows only;
...
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7hc7x9x64mvq3, child number 1
-------------------------------------
select /*+ index(t1) */ owner, object_type, object_name from t1 order
by object_name offset :offset_size rows fetch next :fetch_size rows only
Plan hash value: 2428354701
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 50297 (100)| | 20 |00:00:00.16 | 49293 |
|* 1 | VIEW | | 1 | 50000 | 10M| 50297 (1)| 00:00:02 | 20 |00:00:00.16 | 49293 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 50000 | 2490K| 50297 (1)| 00:00:02 | 49020 |00:00:00.15 | 49293 |
|* 3 | FILTER | | 1 | | | | | 49020 |00:00:00.11 | 49293 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 50000 | 2490K| 50297 (1)| 00:00:02 | 49020 |00:00:00.09 | 49293 |
| 5 | INDEX FULL SCAN | T1_I1 | 1 | 50000 | | 326 (1)| 00:00:01 | 49020 |00:00:00.01 | 319 |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "from$_subquery$_002"@"SEL$2"
2 - SEL$1
4 - SEL$1 / "T1"@"SEL$1"
5 - SEL$1 / "T1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_S
IZE AND "from$_subquery$_002"."rowlimit_$$_rownumber">:OFFSET_SIZE))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE
3 - filter(:OFFSET_SIZE<GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
4 - SEL$1 / "T1"@"SEL$1"
- index(t1)
--//实际扫描索引到49020。而实际前面的查询实际上是多余的。实际上这类语句的写法最佳就是改写减少回表造成的逻辑读。
SELECT owner, object_type, object_name
FROM t1
WHERE ROWID IN ( SELECT ROWID x
FROM t1
ORDER BY object_name
OFFSET :offset_size ROWS
FETCH NEXT :fetch_size ROWS ONLY)
order by object_name
;
--//这样可以大大减少回表的逻辑读，缺点是使用rowid，有一些应用和开发限制rowid的在sql语句中的使用。
SCOTT@book01p> exec :offset_size := 49000
PL/SQL procedure successfully completed.
Plan hash value: 977015668
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 560 (100)| | 20 |00:00:00.08 | 338 | | | |
| 1 | SORT ORDER BY | | 1 | 1 | 75 | 560 (1)| 00:00:01 | 20 |00:00:00.08 | 338 | 4096 | 4096 | 4096 (0)|
| 2 | NESTED LOOPS | | 1 | 1 | 75 | 559 (1)| 00:00:01 | 20 |00:00:00.08 | 338 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 50000 | 585K| 326 (1)| 00:00:01 | 20 |00:00:00.08 | 318 | | | |
| 4 | HASH UNIQUE | | 1 | 1 | 732K| | | 20 |00:00:00.08 | 318 | 1292K| 899K| |
|* 5 | VIEW | | 1 | 50000 | 732K| 326 (1)| 00:00:01 | 20 |00:00:00.08 | 318 | | | |
|* 6 | WINDOW NOSORT STOPKEY | | 1 | 50000 | 2343K| 326 (1)| 00:00:01 | 49020 |00:00:00.07 | 318 | 2635K| 736K| |
|* 7 | FILTER | | 1 | | | | | 49020 |00:00:00.04 | 318 | | | |
| 8 | INDEX FULL SCAN | T1_I1 | 1 | 50000 | 2343K| 326 (1)| 00:00:01 | 49020 |00:00:00.02 | 318 | | | |
| 9 | TABLE ACCESS BY USER ROWID| T1 | 20 | 1 | 63 | 1 (0)| 00:00:01 | 20 |00:00:00.01 | 20 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$BE5C8E5F
3 - SEL$291F8F59 / "VW_NSO_1"@"SEL$BE5C8E5F"
4 - SEL$291F8F59
5 - SEL$1 / "from$_subquery$_003"@"SEL$3"
6 - SEL$1
8 - SEL$1 / "T1"@"SEL$1"
9 - SEL$BE5C8E5F / "T1"@"SEL$2"
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("from$_subquery$_003"."rowlimit_$$_rownumber"<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE AND
"from$_subquery$_003"."rowlimit_$$_rownumber">:OFFSET_SIZE))
6 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE)
7 - filter(:OFFSET_SIZE<GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE)
--//这样最坏的可能是扫描整个索引。
SCOTT@book01p> exec :offset_size := 100
Plan hash value: 977015668
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 560 (100)| | 20 |00:00:00.01 | 23 | | | |
| 1 | SORT ORDER BY | | 1 | 1 | 75 | 560 (1)| 00:00:01 | 20 |00:00:00.01 | 23 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS | | 1 | 1 | 75 | 559 (1)| 00:00:01 | 20 |00:00:00.01 | 23 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 50000 | 585K| 326 (1)| 00:00:01 | 20 |00:00:00.01 | 3 | | | |
| 4 | HASH UNIQUE | | 1 | 1 | 732K| | | 20 |00:00:00.01 | 3 | 1292K| 899K| |
|* 5 | VIEW | | 1 | 50000 | 732K| 326 (1)| 00:00:01 | 20 |00:00:00.01 | 3 | | | |
|* 6 | WINDOW NOSORT STOPKEY | | 1 | 50000 | 2343K| 326 (1)| 00:00:01 | 120 |00:00:00.01 | 3 | 2635K| 736K| |
|* 7 | FILTER | | 1 | | | | | 120 |00:00:00.01 | 3 | | | |
| 8 | INDEX FULL SCAN | T1_I1 | 1 | 50000 | 2343K| 326 (1)| 00:00:01 | 120 |00:00:00.01 | 3 | | | |
| 9 | TABLE ACCESS BY USER ROWID| T1 | 20 | 1 | 63 | 1 (0)| 00:00:01 | 20 |00:00:00.01 | 20 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--//可以发现扫描索引仅仅120行。
--//昨天看链接https://jonathanlewis.wordpress.com/2025/05/27/pagination-cost-2/
--//提到分页查询使用绑定变量的问题自己重复测试看看。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.建立测试表：
SCOTT@book01p> create table t1 as select * from all_objects where rownum <= 50000 order by dbms_random.value;
Table created.
SCOTT@book01p> create index t1_i1 on t1(object_name);
Index created.
--//分析表略。
3.测试1：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> select owner, object_type, object_name from t1 order by object_name offset 10 rows fetch next 20 rows only;
OWNER OBJECT_TYPE OBJECT_NAME
---------------------- --------------- --------------------
SYS TABLE ACTIVITY_CONFIG$
SYS TABLE PARTITION ACTIVITY_MVIEW$
SYS TABLE ACTIVITY_MVIEW$
SYS TABLE ACTIVITY_SNAP_META$
SYS TABLE PARTITION ACTIVITY_TABLE$
SYS TABLE ACTIVITY_TABLE$
SYS VIEW ADBTASK_CLIENT
PUBLIC SYNONYM ADBTASK_CLIENT
SYS VIEW ADBTASK_SCHEDULE
PUBLIC SYNONYM ADBTASK_SCHEDULE
PUBLIC SYNONYM ADBTASK_SETTINGS
SYS VIEW ADBTASK_SETTINGS
REMOTE_SCHEDULER_AGENT PROCEDURE ADD_AGENT_CERTIFICATE
SYS TABLE ADMINAUTH$
SYS TABLE ADO_IMPARAM$
SYS TABLE ADO_IMSEGSTAT$
SYS TABLE ADO_IMSEGTASKDETAILS$
SYS TABLE ADO_IMSTAT$
SYS TABLE ADO_IMTASKS$
SYS TYPE ADR_HOME_T
20 rows selected.
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3uvg4dgwxp5q9, child number 0
-------------------------------------
select owner, object_type, object_name from t1 order by object_name
offset 10 rows fetch next 20 rows only
Plan hash value: 3254925009
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 33 (100)| | 20 |00:00:00.01 | 34 |
|* 1 | VIEW | | 1 | 30 | 6720 | 33 (0)| 00:00:01 | 20 |00:00:00.01 | 34 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 30 | 1530 | 33 (0)| 00:00:01 | 30 |00:00:00.01 | 34 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 50000 | 2490K| 33 (0)| 00:00:01 | 30 |00:00:00.01 | 34 |
| 4 | INDEX FULL SCAN | T1_I1 | 1 | 30 | | 3 (0)| 00:00:01 | 30 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "from$_subquery$_002"@"SEL$2"
2 - SEL$1
3 - SEL$1 / "T1"@"SEL$1"
4 - SEL$1 / "T1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=30 AND "from$_subquery$_002"."rowlimit_$$_rownumber">10))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=30)
--//可以发现在指定文字变量的情况下，id=4走的是索引，实际上仅仅访问30条，直接回表查询。
--//讲一句话也访问靠前的索引优势越明显。
4.测试2：
--//如果使用绑定变量
variable offset_size number
variable fetch_size number
begin
:offset_size := 10; :fetch_size := 20;
end;
/
SCOTT@book01p> select owner, object_type, object_name from t1 order by object_name offset :offset_size rows fetch next :fetch_size rows only;
OWNER OBJECT_TYPE OBJECT_NAME
----- ------------ ------------------------------
SYS TABLE ACTIVITY_CONFIG$
...
SYS TYPE ADR_HOME_T
20 rows selected.
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6156ygkf7n77g, child number 0
-------------------------------------
select owner, object_type, object_name from t1 order by object_name
offset :offset_size rows fetch next :fetch_size rows only
Plan hash value: 1024497473
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 937 (100)| | 20 |00:00:00.01 | 1069 | | | |
|* 1 | VIEW | | 1 | 50000 | 10M| | 937 (1)| 00:00:01 | 20 |00:00:00.01 | 1069 | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 50000 | 2490K| 3160K| 937 (1)| 00:00:01 | 30 |00:00:00.01 | 1069 | 11264 | 11264 |10240 (0)|
|* 3 | FILTER | | 1 | | | | | | 50000 |00:00:00.01 | 1069 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 50000 | 2490K| | 298 (1)| 00:00:01 | 50000 |00:00:00.01 | 1069 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "from$_subquery$_002"@"SEL$2"
2 - SEL$1
4 - SEL$1 / "T1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE AND
"from$_subquery$_002"."rowlimit_$$_rownumber">:OFFSET_SIZE))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE)
3 - filter(:OFFSET_SIZE<GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE)
--//执行计划变成全表扫描，看看10053跟踪：
SCOTT@book01p> @ 10053x 6156ygkf7n77g 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4852_a6156ygkf7n77g.trc
SCOTT@book01p> @ 10053y
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."OWNER" "OWNER","from$_subquery$_002"."OBJECT_TYPE" "OBJECT_TYPE","from$_subquery$_002"."OB
JECT_NAME" "OBJECT_NAME" FROM (SELECT "T1"."OWNER" "OWNER","T1"."OBJECT_TYPE" "OBJECT_TYPE","T1"."OBJECT_NAME" "OBJECT_
NAME","T1"."OBJECT_NAME" "rowlimit_$_0",ROW_NUMBER() OVER ( ORDER BY "T1"."OBJECT_NAME") "rowlimit_$$_rownumber" FROM "S
COTT"."T1" "T1" WHERE :B1<GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:B2))),0)+:B3) "from$_subquery$_002" WHERE "from$_subquery$_0
02"."rowlimit_$$_rownumber"<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:B4))),0)+:B5 AND "from$_subquery$_002"."rowlimit_$$_rownu
mber">:B6 ORDER BY "from$_subquery$_002"."rowlimit_$_0"
--//格式化后如下：
/* Formatted on 2025-05-30 15:56:04 (QP5 v5.277) */
SELECT "from$_subquery$_002"."OWNER" "OWNER"
,"from$_subquery$_002"."OBJECT_TYPE" "OBJECT_TYPE"
,"from$_subquery$_002"."OBJECT_NAME" "OBJECT_NAME"
FROM (SELECT "T1"."OWNER" "OWNER"
,"T1"."OBJECT_TYPE" "OBJECT_TYPE"
,"T1"."OBJECT_NAME" "OBJECT_NAME"
,"T1"."OBJECT_NAME" "rowlimit_$_0"
,ROW_NUMBER () OVER (ORDER BY "T1"."OBJECT_NAME") "rowlimit_$$_rownumber"
FROM "SCOTT"."T1" "T1"
WHERE :B1 < GREATEST (FLOOR (TO_NUMBER (TO_CHAR ( :B2))), 0) + :B3) "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber" <= GREATEST (FLOOR (TO_NUMBER (TO_CHAR ( :B4))), 0) + :B5
AND "from$_subquery$_002"."rowlimit_$$_rownumber" > :B6
ORDER BY "from$_subquery$_002"."rowlimit_$_0"
--//https://jonathanlewis.wordpress.com/2025/05/27/pagination-cost-2/
This looks like bad news – we haven't taken advantage of an index to avoid visiting and sorting all the rows in the
table, operation 4 shows us a table scan passing 50,000 rows through a filter up to the window sort at operation 2 which
discards the 49,970 rows we definitely don't want before passing the remaining 30 rows to the view operation that
discards the first 10 that we needed to skip. Why don't we see the far more efficient index scan?
这看起来像坏消息，我们没有利用索引来避免访问和排序的所有行，操作4向我们展示一个表扫描通过50000行通过过滤器窗口排序操作2
丢弃49970行我们绝对不希望通过剩下的30行视图操作丢弃第一个10，我们需要跳过。为什么我们看不到更有效的索引扫描呢？
You may have noticed a couple of oddities in the Predicate Information.
您可能已经注意到了谓词信息中的一些奇怪之处。
Wherever you see the :offset_size bind variable the optimizer has wrapped it in to_number(to_char()) – why?! My first
thought about this was that the double conversion made it impossible for the optimizer to peek at the value and use it
to get a better estimate of cost, but that's (probably) not why the index full scan disappeared.
无论您看到：偏移_sizebind变量，优化器已经将它包装到_nuber(to_char())-为什么？!我的第一个想法是，双重转换使优化器不可能查
看值并使用它来更好地获得成本估计，但这(可能)不是索引全扫描消失的原因。
The offset and fetch first are both supposed to be numeric (according to the tram-tracks in the manual) so it seems a
little strange that Oracle treats just one of them to a double conversion.
偏移和先取都应该是数字的(根据手册中的有轨电车轨道)，所以Oracle只对待其中一个进行双重转换似乎有点奇怪。
What is that filter() in operation 3 actually trying to achieve? If you tidy up the messy bits it's just checking two
bind variables to make sure that the offset is less than the offset plus fetch size. This is just an example of
"conditional SQL". In this case it's following the pattern for "columnX between :bind1 and :bind2" – allowing Oracle to
short-circuit the sub-plan if the value of bind2 is less than that of bind1. (It wasn't needed for the example where we
used literals because Oracle could do the artithmetic at parse time and see that 10 was – and always would be – less
than 30.)
操作3中的filter()实际上试图实现什么？如果你整理了混乱的位，它只需要检查两个绑定变量，以确保偏移量小于偏移量加上获取大小
。这只是"有条件的SQL"的一个例子。在这种情况下，它遵循绑定1之间的模式-如果绑定2的值小于绑定1的值，允许Oracle短路子计划
。(在我们使用文字的例子中不需要这样做，因为Oracle可以在解析时做算术，并看到10是——而且总是是——小于30。)
What are the checks actually saying about the optimizer's (or developer's) expectation for the way you might use the
feature? The generated SQL actually allows for negative, non-integer values here. Negative offsets are replaced by zero,
negative fetch sizes result in the query short-circuiting and returning no data (in fact any fetech size strictly less
than 1 will return no rows).
关于优化器(或开发人员)对您可能使用该特性的方式的期望的检查是什么？生成的SQL实际上允许负的非整数值。负偏移量被零取代，负
读取大小导致查询短路并不返回任何数据(实际上任何严格小于1的fetech大小将不返回任何行)。
Hoping to find further clues about the poor choice of plan, I took a look at the "UNPARSED QUERY" from the CBO (10053)
trace, and cross-checked against the result from using the dbms_utility.expand_sql() procedure; the results were
(logically, though not cosmetically) the same. Here, with a little extra cosmetic tidying is the SQL the optimizer
actually works with:
为了找到更多关于计划选择不佳的线索，我查看了CBO(10053的)查询，并与使用dbms_utility.expand_sql()程序的结果进行了交叉核对
；结果(逻辑上，虽然不是外观上是相同的)。在这里，一些额外的美容整理是SQL实际工作的SQL：
--//作者给出的建议使用提示index(t1)，或者设置optimizer_mode=first_rows_10 or first_rows_100。
--//实际上分页的本质是减少回表的查询。上面的提示或者写法如果查询很后面的数据，回表记录很多依旧很慢。
exec :offset_size := 49000
exec :fetch_size := 20;
SCOTT@book01p> select /*+ index(t1) */ owner, object_type, object_name from t1 order by object_name offset :offset_size rows fetch next :fetch_size rows only;
...
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7hc7x9x64mvq3, child number 1
-------------------------------------
select /*+ index(t1) */ owner, object_type, object_name from t1 order
by object_name offset :offset_size rows fetch next :fetch_size rows only
Plan hash value: 2428354701
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 50297 (100)| | 20 |00:00:00.16 | 49293 |
|* 1 | VIEW | | 1 | 50000 | 10M| 50297 (1)| 00:00:02 | 20 |00:00:00.16 | 49293 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 50000 | 2490K| 50297 (1)| 00:00:02 | 49020 |00:00:00.15 | 49293 |
|* 3 | FILTER | | 1 | | | | | 49020 |00:00:00.11 | 49293 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 50000 | 2490K| 50297 (1)| 00:00:02 | 49020 |00:00:00.09 | 49293 |
| 5 | INDEX FULL SCAN | T1_I1 | 1 | 50000 | | 326 (1)| 00:00:01 | 49020 |00:00:00.01 | 319 |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "from$_subquery$_002"@"SEL$2"
2 - SEL$1
4 - SEL$1 / "T1"@"SEL$1"
5 - SEL$1 / "T1"@"SEL$1"
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_S
IZE AND "from$_subquery$_002"."rowlimit_$$_rownumber">:OFFSET_SIZE))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE
3 - filter(:OFFSET_SIZE<GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
4 - SEL$1 / "T1"@"SEL$1"
- index(t1)
--//实际扫描索引到49020。而实际前面的查询实际上是多余的。实际上这类语句的写法最佳就是改写减少回表造成的逻辑读。
SELECT owner, object_type, object_name
FROM t1
WHERE ROWID IN ( SELECT ROWID x
FROM t1
ORDER BY object_name
OFFSET :offset_size ROWS
FETCH NEXT :fetch_size ROWS ONLY)
order by object_name
;
--//这样可以大大减少回表的逻辑读，缺点是使用rowid，有一些应用和开发限制rowid的在sql语句中的使用。
SCOTT@book01p> exec :offset_size := 49000
PL/SQL procedure successfully completed.
Plan hash value: 977015668
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 560 (100)| | 20 |00:00:00.08 | 338 | | | |
| 1 | SORT ORDER BY | | 1 | 1 | 75 | 560 (1)| 00:00:01 | 20 |00:00:00.08 | 338 | 4096 | 4096 | 4096 (0)|
| 2 | NESTED LOOPS | | 1 | 1 | 75 | 559 (1)| 00:00:01 | 20 |00:00:00.08 | 338 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 50000 | 585K| 326 (1)| 00:00:01 | 20 |00:00:00.08 | 318 | | | |
| 4 | HASH UNIQUE | | 1 | 1 | 732K| | | 20 |00:00:00.08 | 318 | 1292K| 899K| |
|* 5 | VIEW | | 1 | 50000 | 732K| 326 (1)| 00:00:01 | 20 |00:00:00.08 | 318 | | | |
|* 6 | WINDOW NOSORT STOPKEY | | 1 | 50000 | 2343K| 326 (1)| 00:00:01 | 49020 |00:00:00.07 | 318 | 2635K| 736K| |
|* 7 | FILTER | | 1 | | | | | 49020 |00:00:00.04 | 318 | | | |
| 8 | INDEX FULL SCAN | T1_I1 | 1 | 50000 | 2343K| 326 (1)| 00:00:01 | 49020 |00:00:00.02 | 318 | | | |
| 9 | TABLE ACCESS BY USER ROWID| T1 | 20 | 1 | 63 | 1 (0)| 00:00:01 | 20 |00:00:00.01 | 20 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$BE5C8E5F
3 - SEL$291F8F59 / "VW_NSO_1"@"SEL$BE5C8E5F"
4 - SEL$291F8F59
5 - SEL$1 / "from$_subquery$_003"@"SEL$3"
6 - SEL$1
8 - SEL$1 / "T1"@"SEL$1"
9 - SEL$BE5C8E5F / "T1"@"SEL$2"
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("from$_subquery$_003"."rowlimit_$$_rownumber"<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE AND
"from$_subquery$_003"."rowlimit_$$_rownumber">:OFFSET_SIZE))
6 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_NAME")<=GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE)
7 - filter(:OFFSET_SIZE<GREATEST(FLOOR(TO_NUMBER(TO_CHAR(:OFFSET_SIZE))),0)+:FETCH_SIZE)
--//这样最坏的可能是扫描整个索引。
SCOTT@book01p> exec :offset_size := 100
Plan hash value: 977015668
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 560 (100)| | 20 |00:00:00.01 | 23 | | | |
| 1 | SORT ORDER BY | | 1 | 1 | 75 | 560 (1)| 00:00:01 | 20 |00:00:00.01 | 23 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS | | 1 | 1 | 75 | 559 (1)| 00:00:01 | 20 |00:00:00.01 | 23 | | | |
| 3 | VIEW | VW_NSO_1 | 1 | 50000 | 585K| 326 (1)| 00:00:01 | 20 |00:00:00.01 | 3 | | | |
| 4 | HASH UNIQUE | | 1 | 1 | 732K| | | 20 |00:00:00.01 | 3 | 1292K| 899K| |
|* 5 | VIEW | | 1 | 50000 | 732K| 326 (1)| 00:00:01 | 20 |00:00:00.01 | 3 | | | |
|* 6 | WINDOW NOSORT STOPKEY | | 1 | 50000 | 2343K| 326 (1)| 00:00:01 | 120 |00:00:00.01 | 3 | 2635K| 736K| |
|* 7 | FILTER | | 1 | | | | | 120 |00:00:00.01 | 3 | | | |
| 8 | INDEX FULL SCAN | T1_I1 | 1 | 50000 | 2343K| 326 (1)| 00:00:01 | 120 |00:00:00.01 | 3 | | | |
| 9 | TABLE ACCESS BY USER ROWID| T1 | 20 | 1 | 63 | 1 (0)| 00:00:01 | 20 |00:00:00.01 | 20 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--//可以发现扫描索引仅仅120行。