内容概述
数据库分页查询功能是处理大量数据时常用的技术，将结果集分成多个“页”返回，提高系统性能和用户体验。以下是关键要点： 1. 性能优化：避免一次性加载全部数据，减少内存消耗和网络传输 2. 提升响应速度：快速返回首屏数据 3. 改善用户体验：支持逐页浏览大数据集 4. 未分页的结果集一般比较大 5. 页码小的查询快,随着页码的增大,会越来越慢 YashanDb 23.5对于分页查询场景的优化 Rownum/StopKey的下推，本文使用Oracle 19C进行对比测试。
测试结果总结
测试场景一：无谓词分页查询(yashandb性能较优) 1. 场景一、无谓词分页查询性能对比： Oracle使用 TABLE ACCESS FULL, IO：172694 consistent gets，耗时：3.05秒 yashandb使用 INDEX FULL SCAN DESCENDING IO：2 db block gets ， 耗时：0.020, 2. 由于Oracle数据库中索引不存储NULL值，而yashan数据库中索引包含表的索引列的所有数据(包括NULL)，所以Oracle使用TABLE ACCESS FULL而yashan使用INDEX FULL SCAN DESCENDING。 说明：无谓词的分布查询，yashandb性能较好，但由于Oracle索引不存储NULL值，所以该场景对Oracle显不公平，咱们添加谓词后，在场景二有谓词分页查询，继续测试。 测试场景二：有谓词分页查询(yashandb性能较优) 1. 场景二：有谓词分页查询性能对比： Oracle 使用 INDEX FULL SCAN DESCENDING, IO：20913 consistent gets， 耗时：3.16秒 yashandb使用 INDEX FULL SCAN DESCENDING, IO：2 db block gets ， 耗时：0.003, 2. YashanDb 23.5对于分页查询场景的优化 Rownum/StopKey的下推，yashandb的性能更优 说明：场景二、有谓词分页查询中yashan表现较优，但这个SQL写法存在性能问题，我们优化该SQL后，场景三、三层分页查询，继续测试。 测试场景三：三层分页查询(yashandb/oracle性能相当) 1. 场景三：三层分页查询性能对比： Oracle 使用 INDEX FULL SCAN DESCENDING, IO：4 consistent gets， 耗时：0.01秒 yashandb使用 INDEX FULL SCAN DESCENDING, IO：2 db block gets ， 耗时：0.006秒, 2. 场景三、三层分页查询,oracle和yashandb 执行计划中都使用COUNT STOPKEY即Rownum/StopKey的下推，所以性能都不错。 说明：所以场景一和场景二中，Oracle性能表现不佳，是由于sql编写不规范造成的，下面我们使用分析函数实现分布查询。 测试场景四：分析函数分页查询(oracle性能较优) 1. 场景四、分析函数分页查询性能对比： Oracle 使用 WINDOW NOSORT STOPKEY, IO：4 consistent gets， 耗时：0.01秒 yashandb使用 WINDOW NOSORT(StopKey), IO：17576 db block gets， 耗时：3.182秒, 2. yashandb 第三步 3 - StopKey: 19， 有Operation Information，但这个步骤前面没有"*"号 3. yashandb 比Oracle多了步骤二 |* 2 | RESULT 测试场景五：offset 分页查询(oracle性能较优) 1. 场景五性能对比： Oracle使用 index full scan descending，IO：4 consistent gets，耗时：0.01秒 yashandb使用 index range scan ， IO：17576 db block gets，耗时：7.065 2. 相同的sql语句，yashandb与Oracle 结果显示不一致 3. 怀疑yashandb的执行计划受rownum影响 测试场景六：offset 分页查询2(yashandb/oracle性能相当) 1. 场景六性能对比： Oracle使用 index full scan descending， IO：4 consistent gets，耗时：0.01秒 yashandb使用 INDEX RANGE SCAN DESCENDING，IO：2 db block gets，耗时：0.002秒 2. 通过场景六验证了，场景五中 yashandb与oracle 的rownum实现方法存在差异 测试场景七：offset 分页查询3(yashandb/oracle性能相当) 1. 场景七性能对比： Oracle使用 INDEX FULL SCAN DESCENDING， IO：4 consistent gets，耗时：0.01秒 yashandb使用 INDEX RANGE SCAN DESCENDING，IO：2 db block gets， 耗时：0.004秒 2. yashandb使用场景五、offset分页查询时,多一层记录rownum可以实现与Oracle相同的结果及性能。
测试场景一、无谓词分页查询
测试语句
ALTER SESSION SET statistics_level=ALL; set timing on SET autotrace ON; select * from( select a.*,rownum as rn from ( select object_id from t1 order by object_id desc ) a) where rn > 10 and rn < 20;
Oracle测试结果
SQL>
select * from(
select a.*,rownum as rn from (
select object_id from t1 order by object_id desc
)
a) where rn > 10 and rn < 20;
OBJECT_ID RN
---------- ----------
73112 11
73111 12
73110 13
73109 14
73108 15
73107 16
73106 17
73105 18
73104 19
9 rows selected.
Elapsed: 00:00:03.05
Execution Plan
----------------------------------------------------------
Plan hash value: 2211185287
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 247M| | 77342 (1)| 00:00:04 |
|* 1 | VIEW | | 10M| 247M| | 77342 (1)| 00:00:04 |
| 2 | COUNT | | | | | | |
| 3 | VIEW | | 10M| 123M| | 77342 (1)| 00:00:04 |
| 4 | SORT ORDER BY | | 10M| 38M| 114M| 77342 (1)| 00:00:04 |
| 5 | TABLE ACCESS FULL| T1 | 10M| 38M| | 48733 (1)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<20 AND "RN">10)
Statistics
----------------------------------------------------------
115 recursive calls
4 db block gets
172694 consistent gets
14654 physical reads
0 redo size
778 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
9 rows processed
SQL>
yashandb测试结果
SQL>
select * from(
select a.*,rownum as rn from (
select object_id from t1 order by object_id desc
)
a) where rn > 10 and rn < 20;
OBJECT_ID RN
----------- ---------------------
73112 11
73111 12
73110 13
73109 14
73108 15
73107 16
73106 17
73105 18
73104 19
Execution Plan
----------------------------------------------------------------
SQL hash value: 2900712025
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 9| | 23| 10| 0| 0| |
|* 1 | VIEW | | | 10000000| 9| 2803( 0)| 22| 10| 0| 0| |
|* 2 | COUNT STOPKEY | | | 10000000| 19| 2803( 0)| 14| 20| 0| 0| |
| 3 | VIEW | | | 10000000| 19| 1919( 0)| 12| 19| 0| 0| |
| 4 | INDEX FULL SCAN DESCENDING | IDX_T1_OBJECTID | YASTEST | 10000000| 19| 1919( 0)| 10| 19| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : filter(ROWNUM[OPTMZ-2] > 10[OPTMZ-0])
2 - Predicate : filter(ROWNUM[OPTMZ-0] < 20[OPTMZ-0])
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
2 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
0 bytes sent via PX
0 block received
40 rows fetched.
Elapsed: 00:00:00.020
SQL>
小结
测试场景一：无谓词分页查询(yashandb性能较优) 1. 场景一、无谓词分页查询性能对比： Oracle使用 TABLE ACCESS FULL, IO：172694 consistent gets，耗时：3.05秒 yashandb使用 INDEX FULL SCAN DESCENDING IO：2 db block gets ， 耗时：0.020秒, 2. 由于Oracle数据库中索引不存储NULL值，而yashan数据库中索引包含表的索引列的所有数据(包括NULL)，所以Oracle使用TABLE ACCESS FULL而yashan使用INDEX FULL SCAN DESCENDING。 说明：无谓词的分布查询，yashandb性能较好，但由于Oracle索引不存储NULL值，所以该场景对Oracle显不公平，咱们添加谓词后，在场景二有谓词分页查询，继续测试。
测试场景二、有谓词分页查询
测试语句
ALTER SESSION SET statistics_level=ALL; set timing on SET autotrace ON; select * from( select a.*,rownum as rn from ( select object_id from t1 where object_id is not null order by object_id desc ) a) where rn > 10 and rn < 20;
Oracle测试结果
SQL>
select * from(
select a.*,rownum as rn from (
select object_id from t1 where object_id is not null order by object_id desc
)
5 a) where rn > 10 and rn < 20;
OBJECT_ID RN
---------- ----------
73111 11
73110 12
73109 13
73108 14
73107 15
73106 16
73105 17
73104 18
73103 19
9 rows selected.
Elapsed: 00:00:03.16
Execution Plan
----------------------------------------------------------
Plan hash value: 462672863
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999K| 247M| 20970 (1)| 00:00:01 |
|* 1 | VIEW | | 9999K| 247M| 20970 (1)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | VIEW | | 9999K| 123M| 20970 (1)| 00:00:01 |
|* 4 | INDEX FULL SCAN DESCENDING| IDX_T1_OBJECTID | 9999K| 38M| 20970 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<20 AND "RN">10)
4 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
20913 consistent gets
0 physical reads
0 redo size
778 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)
9 rows processed
SQL>
yashandb测试结果
SQL>
select * from(
select a.*,rownum as rn from (
select object_id from t1 where object_id is not null order by object_id desc
)
5 a) where rn > 10 and rn < 20;
OBJECT_ID RN
----------- ---------------------
73111 11
73110 12
73109 13
73108 14
73107 15
73106 16
73105 17
73104 18
73103 19
Execution Plan
----------------------------------------------------------------
SQL hash value: 460267372
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 9| | 65| 10| 0| 0| |
|* 1 | VIEW | | | 9999999| 9| 2803( 0)| 64| 10| 0| 0| |
|* 2 | COUNT STOPKEY | | | 9999999| 19| 2803( 0)| 58| 20| 0| 0| |
| 3 | VIEW | | | 9999999| 19| 1919( 0)| 53| 19| 0| 0| |
|* 4 | INDEX RANGE SCAN DESCENDING| IDX_T1_OBJECTID | YASTEST | 9999999| 19| 1919( 0)| 37| 19| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : filter(ROWNUM[OPTMZ-2] > 10[OPTMZ-0])
2 - Predicate : filter(ROWNUM[OPTMZ-0] < 20[OPTMZ-0])
4 - Predicate : access("T1"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
2 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
0 bytes sent via PX
0 block received
41 rows fetched.
Elapsed: 00:00:00.003
SQL>
小结
测试场景二：有谓词分页查询(yashandb性能较优) 1. 场景一、无谓词分页查询性能对比： Oracle 使用 INDEX FULL SCAN DESCENDING, IO：20913 consistent gets，耗时：3.16秒 yashandb使用 INDEX FULL SCAN DESCENDING, IO：2 db block gets ， 耗时：0.003, 2. YashanDb 23.5对于分页查询场景的优化 Rownum/StopKey的下推，yashandb的性能更优 说明：场景二、有谓词分页查询中yashan表现较优，但这个SQL写法存在性能问题，我们优化该SQL后，场景三、三层分页查询，继续测试。
测试场景三、三层分页查询
测试语句
select * from( select a.*,rownum as rn from ( select object_id from t1 where object_id is not null order by object_id desc )a where rownum < 20 ) where rn > 10 ;
Oracle测试结果
SQL>
select * from(
select a.*,rownum as rn from (
select object_id from t1 where object_id is not null order by object_id desc
)a where rownum < 20
5 ) where rn > 10 ;
OBJECT_ID RN
---------- ----------
73111 11
73110 12
73109 13
73108 14
73107 15
73106 16
73105 17
73104 18
73103 19
9 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1566820405
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 494 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 19 | 494 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 19 | 247 | 3 (0)| 00:00:01 |
|* 4 | INDEX FULL SCAN DESCENDING| IDX_T1_OBJECTID | 9999K| 38M| 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10)
2 - filter(ROWNUM<20)
4 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
778 bytes sent via SQL*Net to client
527 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL>
yashandb测试结果
SQL>
select * from(
select a.*,rownum as rn from (
select object_id from t1 where object_id is not null order by object_id desc
)a where rownum < 20
5 ) where rn > 10 ;
OBJECT_ID RN
----------- ---------------------
73111 11
73110 12
73109 13
73108 14
73107 15
73106 16
73105 17
73104 18
73103 19
Execution Plan
----------------------------------------------------------------
SQL hash value: 904852421
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 9| | 27| 10| 0| 0| |
|* 1 | VIEW | | | 19| 9| 884( 0)| 27| 10| 0| 0| |
|* 2 | COUNT STOPKEY | | | 19| 19| 884( 0)| 23| 20| 0| 0| |
| 3 | VIEW | | | 19| 19| 1( 0)| 21| 19| 0| 0| |
|* 4 | INDEX RANGE SCAN DESCENDING| IDX_T1_OBJECTID | YASTEST | 19| 19| 1( 0)| 17| 19| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Predicate : filter(ROWNUM[OPTMZ-2] > 10[OPTMZ-0])
2 - Predicate : filter(ROWNUM[OPTMZ-0] < 20[OPTMZ-0])
4 - Predicate : access("T1"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
2 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
0 bytes sent via PX
0 block received
41 rows fetched.
Elapsed: 00:00:00.006
SQL>
小结
测试场景三：三层分页查询(yashandb/oracle性能相当) 1. 场景一、无谓词分页查询性能对比： Oracle 使用 INDEX FULL SCAN DESCENDING, IO：4 consistent gets， 耗时：0.01秒 yashandb使用 INDEX FULL SCAN DESCENDING, IO：2 db block gets ， 耗时：0.006秒, 2. 场景三、三层分页查询,oracle和yashandb 执行计划中都使用COUNT STOPKEY即Rownum/StopKey的下推，所以性能都不错。 说明：所以场景一和场景二中，Oracle性能表现不佳，是由于sql编写不规范造成的，下面我们使用分析函数实现分布查询。
测试场景四、分析函数分页查询
测试语句
select * from( select object_id,row_number() over (order by object_id desc) as rn from t1 where object_id is not null ) where rn > 10 and rn < 20;
Oracle测试结果
SQL>
select * from(
select object_id,row_number() over (order by object_id desc) as rn
from t1 where object_id is not null
4 ) where rn > 10 and rn < 20;
OBJECT_ID RN
---------- ----------
73111 11
73110 12
73109 13
73108 14
73107 15
73106 16
73105 17
73104 18
73103 19
9 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 617580278
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 520 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 20 | 520 | 3 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 19 | 76 | 3 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN DESCENDING| IDX_T1_OBJECTID | 9999K| 38M| 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10 AND "RN"<20)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<20)
3 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
778 bytes sent via SQL*Net to client
510 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL>
yashandb测试结果
SQL>
select * from(
select object_id,row_number() over (order by object_id desc) as rn
from t1 where object_id is not null
4 ) where rn > 10 and rn < 20;
OBJECT_ID RN
----------- ---------------------
73111 11
73110 12
73109 13
73108 14
73107 15
73106 16
73105 17
73104 18
73103 19
Execution Plan
----------------------------------------------------------------
SQL hash value: 852340868
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 9| | 3163398| 10| 0| 0| |
| 1 | VIEW | | | 1895706| 9| 5445( 0)| 3163396| 10| 0| 0| |
|* 2 | RESULT | | | 1895706| 9| 5445( 0)| 3163259| 10| 0| 0| |
| 3 | WINDOW NOSORT | | | 9999999| 9999999| 3584( 0)| 2472263| 10000000| 0| 0| |
|* 4 | INDEX RANGE SCAN DESCENDING| IDX_T1_OBJECTID | YASTEST | 9999999| 9999999| 1919( 0)| 678732| 10000000| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
2 - Predicate : filter(ROW_NUMBER()[OPTMZ-2] < 20[OPTMZ-0] AND ROW_NUMBER()[OPTMZ-2] > 10[OPTMZ-0])
3 - StopKey: 19
4 - Predicate : access("T1"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
17576 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
0 bytes sent via PX
0 block received
41 rows fetched.
Elapsed: 00:00:03.182
SQL>
小结
测试场景四：分析函数分页查询(yashandb/oracle性能相当) 1. 场景一、分析函数分页查询性能对比： Oracle 使用 WINDOW NOSORT STOPKEY, IO：4 consistent gets， 耗时：0.01秒 yashandb使用 WINDOW NOSORT(StopKey), IO：17576 db block gets， 耗时：3.182秒, 2. yashandb 第三步 3 - StopKey: 19， 有Operation Information，但这个步骤前面没有"*"号 3. yashandb 比Oracle多了步骤二 |* 2 | RESULT
测试场景五、offset 分页查询
测试语句
select object_id,rownum as rn from t1 where object_id is not null order by object_id desc offset 10 rows fetch next 9 rows only;
Oracle测试结果
SQL>
select object_id,rownum as rn
from t1 where object_id is not null order by object_id desc
3 offset 10 rows fetch next 9 rows only;
OBJECT_ID RN
---------- ----------
73111 11
73110 12
73109 13
73108 14
73107 15
73106 16
73105 17
73104 18
73103 19
9 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2445356698
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 988 | 20970 (1)| 00:00:01 |
|* 1 | VIEW | | 19 | 988 | 20970 (1)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 9999K| 38M| 20970 (1)| 00:00:01 |
| 3 | COUNT | | | | | |
|* 4 | INDEX FULL SCAN DESCENDING| IDX_T1_OBJECTID | 9999K| 38M| 20970 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=19 AND
"from$_subquery$_002"."rowlimit_$$_rownumber">10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=19)
4 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
778 bytes sent via SQL*Net to client
489 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL>
yashandb测试结果
SQL>
select object_id,rownum as rn
from t1 where object_id is not null order by object_id desc
3 offset 10 rows fetch next 9 rows only;
OBJECT_ID RN
----------- ---------------------
73111 9999989
73110 9999988
73109 9999987
73108 9999986
73107 9999985
73106 9999984
73105 9999983
73104 9999982
73103 9999981
Execution Plan
----------------------------------------------------------------
SQL hash value: 2612163272
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 9| | 7062241| 10| 0| 0| |
| 1 | WINDOW | | | 9| 9| 7007( 0)| 7062033| 10| 0| 0| |
| 2 | ORDER BY STOPKEY | | | 9999999| 20| 7007( 0)| 7062032| 20| 0| 0| |
| 3 | COUNT | | | 9999999| 9999999| 2803( 0)| 1142157| 10000000| 0| 0| |
|* 4 | INDEX RANGE SCAN | IDX_T1_OBJECTID | YASTEST | 9999999| 9999999| 1919( 0)| 612901| 10000000| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Limit Expression: (LIMIT: 9[OPTMZ-0] OFFSET: 10[OPTMZ-0])
2 - StopKey: 19[OPTMZ-0]
4 - Predicate : access("T1"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
17576 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
0 bytes sent via PX
0 block received
41 rows fetched.
Elapsed: 00:00:07.065
SQL>
小结
测试场景五：offset 分页查询(oracle性能较优) 1. 场景五性能对比： Oracle使用 index full scan descending，IO：4 consistent gets，耗时：0.01秒 yashandb使用 index range scan ， IO：17576 db block gets，耗时：7.065 2. 相同的sql语句，yashandb与Oracle 结果显示不一致 3. 怀疑yashandb的执行计划受rownum影响
测试场景六、offset 分页查询2
测试语句
select object_id from t1 where object_id is not null order by object_id desc offset 10 rows fetch next 9 rows only;
Oracle测试结果
SQL>
select object_id
from t1 where object_id is not null order by object_id desc
3 offset 10 rows fetch next 9 rows only;
OBJECT_ID
----------
73111
73110
73109
73108
73107
73106
73105
73104
73103
9 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 617580278
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 741 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 19 | 741 | 3 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 19 | 76 | 3 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN DESCENDING| IDX_T1_OBJECTID | 9999K| 38M| 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=19 AND
"from$_subquery$_002"."rowlimit_$$_rownumber">10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=19)
3 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
679 bytes sent via SQL*Net to client
476 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL>
yashandb测试结果
SQL>
select object_id
from t1 where object_id is not null order by object_id desc
3 offset 10 rows fetch next 9 rows only;
OBJECT_ID
-----------
73111
73110
73109
73108
73107
73106
73105
73104
73103
Execution Plan
----------------------------------------------------------------
SQL hash value: 1144927577
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 9| | 17| 10| 0| 0| |
| 1 | WINDOW | | | 9| 9| 1( 0)| 17| 10| 0| 0| |
|* 2 | INDEX RANGE SCAN DESCENDING | IDX_T1_OBJECTID | YASTEST | 9| 20| 1( 0)| 14| 20| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Limit Expression: (LIMIT: 9[OPTMZ-0] OFFSET: 10[OPTMZ-0])
2 - Predicate : access("T1"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
2 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
0 bytes sent via PX
0 block received
38 rows fetched.
Elapsed: 00:00:00.002
SQL>
小结
测试场景六：offset 分页查询2(yashandb/oracle性能相当) 1. 场景六性能对比： Oracle使用 index full scan descending， IO：4 consistent gets，耗时：0.01秒 yashandb使用 INDEX RANGE SCAN DESCENDING，IO：2 db block gets，耗时：0.002秒 2. 通过场景六验证了，场景五中 yashandb与oracle 的rownum实现方法存在差异
测试场景七、offset 分页查询3
测试语句
select a.*,rownum rn from ( select object_id from t1 where object_id is not null order by object_id desc) a offset 10 rows fetch next 9 rows only;
Oracle测试结果
SQL>
select a.*,rownum rn from (
select object_id
from t1 where object_id is not null order by object_id desc) a
4 offset 10 rows fetch next 9 rows only;
OBJECT_ID RN
---------- ----------
73111 11
73110 12
73109 13
73108 14
73107 15
73106 16
73105 17
73104 18
73103 19
9 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3274483559
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 741 | 20970 (1)| 00:00:01 |
|* 1 | VIEW | | 19 | 741 | 20970 (1)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 9999K| 123M| 20970 (1)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | VIEW | | 9999K| 123M| 20970 (1)| 00:00:01 |
|* 5 | INDEX FULL SCAN DESCENDING| IDX_T1_OBJECTID | 9999K| 38M| 20970 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=19 AND
"from$_subquery$_003"."rowlimit_$$_rownumber">10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=19)
5 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
778 bytes sent via SQL*Net to client
507 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL>
yashandb测试结果
SQL>
select a.*,rownum rn from (
select object_id
from t1 where object_id is not null order by object_id desc) a
4 offset 10 rows fetch next 9 rows only;
OBJECT_ID RN
----------- ---------------------
73111 11
73110 12
73109 13
73108 14
73107 15
73106 16
73105 17
73104 18
73103 19
Execution Plan
----------------------------------------------------------------
SQL hash value: 79960360
Optimizer: ADOPT_C
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | 9| | 22| 10| 0| 0| |
| 1 | WINDOW | | | 9| 9| 884( 0)| 18| 10| 0| 0| |
| 2 | COUNT | | | 9| 20| 884( 0)| 16| 20| 0| 0| |
| 3 | VIEW | | | 9| 20| 1( 0)| 14| 20| 0| 0| |
|* 4 | INDEX RANGE SCAN DESCENDING| IDX_T1_OBJECTID | YASTEST | 9| 20| 1( 0)| 14| 20| 0| 0| |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
1 - Limit Expression: (LIMIT: 9[OPTMZ-0] OFFSET: 10[OPTMZ-0])
4 - Predicate : access("T1"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------------------------------------------------
0 physical reads
2 db block gets
0 consistent gets
0 redo size
0 recursive calls
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
0 bytes sent via PX
0 block received
40 rows fetched.
Elapsed: 00:00:00.004
SQL>
小结
测试场景七：offset 分页查询3(yashandb/oracle性能相当) 1. 场景七性能对比： Oracle使用 INDEX FULL SCAN DESCENDING， IO：4 consistent gets，耗时：0.01秒 yashandb使用 INDEX RANGE SCAN DESCENDING，IO：2 db block gets， 耗时：0.004秒 2. yashandb使用场景五、offset分页查询时,多一层记录rownum可以实现与Oracle相同的结果及性能。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
