12c分页查询特性FETCH FIRST ROWS,OFFSET ROWS FETCH NEXT ROW LIMIT Clause子句
Database 12c的FETCH FIRST ROWS特性可以简化老版本中ROW_NUM()或ROWNUM的分页排序写法, 大幅节约开发花在分页查询语句上的时间。
row-limiting子句用以限制某个查询返回的行数
我们这里来对比老的ROWNUM写法等价的FETCH ROWS写法的实际性能对比:
- 可以通过FETCH FIRST/NEXT关键字指定返回结果的行数
- 可以通过PERCENT关键字指定返回结果的行数比例
- 可以通过OFFSET关键字指定返回从结果集中的某一行之后行数
![SQL Row-Limiting Clause Examples](http://farm9.staticflickr.com/8498/8368557211_5c4db04217.jpg)
create table larget tablespace users as select rownum t1, rpad('M',99,'A') t2, rpad('M',99,'A') t3, rpad('M',99,'A') t4 from dual connect by level<=99999; SQL> create index pk_ind on larget(t1) tablespace users; Index created. select llv.* from ( select rownum rn, ll.* from (select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll where rownum<=20) llv where llv.rn>=1; 20 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3843929721 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 3580 | 3 (0)| 00:00:01 | |* 1 | VIEW | | 20 | 3580 | 3 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 20 | 3320 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| LARGET | 99999 | 29M| 3 (0)| 00:00:01 | |* 5 | INDEX FULL SCAN | PK_IND | 20 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("LLV"."RN">=1) 2 - filter(ROWNUM<=20) 5 - filter("T1" IS NOT NULL) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1841 bytes sent via SQL*Net to client 554 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed SQL> select * from larget where t1 is not null order by t1 fetch first 20 rows only; 20 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3254405084 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99999 | 18M| 4573 (1)| 00:00:01 | |* 1 | VIEW | | 99999 | 18M| 4573 (1)| 00:00:01 | |* 2 | WINDOW NOSORT STOPKEY | | 99999 | 29M| 4573 (1)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| LARGET | 99999 | 29M| 4573 (1)| 00:00:01 | |* 4 | INDEX FULL SCAN | PK_IND | 99999 | | 224 (1)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=20) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "LARGET"."T1")<=20) 4 - filter("T1" IS NOT NULL) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1717 bytes sent via SQL*Net to client 554 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed select llv.* from ( select rownum rn, ll.* from (select /*+ index( larget pk_ind */ * from larget where t1 is not null order by t1 ) ll where rownum<=20000) llv where llv.rn>=18000; 2001 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3843929721 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20000 | 3496K| 916 (0)| 00:00:01 | |* 1 | VIEW | | 20000 | 3496K| 916 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 20000 | 3242K| 916 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| LARGET | 99999 | 29M| 916 (0)| 00:00:01 | |* 5 | INDEX FULL SCAN | PK_IND | 20000 | | 46 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("LLV"."RN">=18000) 2 - filter(ROWNUM<=20000) 5 - filter("T1" IS NOT NULL) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1177 consistent gets 904 physical reads 0 redo size 56804 bytes sent via SQL*Net to client 2006 bytes received via SQL*Net from client 135 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2001 rows processed select * from larget where t1 is not null order by t1 OFFSET 17999 ROWS FETCH NEXT 2001 ROWS ONLY; T1 ---------- 18001 Execution Plan ---------------------------------------------------------- Plan hash value: 3254405084 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99999 | 18M| 4573 (1)| 00:00:01 | |* 1 | VIEW | | 99999 | 18M| 4573 (1)| 00:00:01 | |* 2 | WINDOW NOSORT STOPKEY | | 99999 | 29M| 4573 (1)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| LARGET | 99999 | 29M| 4573 (1)| 00:00:01 | |* 4 | INDEX FULL SCAN | PK_IND | 99999 | | 224 (1)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE WHEN (17999>=0) THEN 17999 ELSE 0 END +2001 AND "from$_subquery$_003"."rowlimit_$$_rownumber">17999) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "LARGET"."T1")<=CASE WHEN (17999>=0) THEN 17999 ELSE 0 END +2001) 4 - filter("T1" IS NOT NULL) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1177 consistent gets 0 physical reads 0 redo size 46757 bytes sent via SQL*Net to client 2006 bytes received via SQL*Net from client 135 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2001 rows processed 不仅可以指定fetch的rows数目,还可以指定返回的行比例,但是这一般会引起真正的INDEX FULL SCAN SQL> select * from larget where t1 is not null order by t1 fetch first 1 percent rows only; 1000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 978863371 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99999 | 19M| 4573 (1)| 00:00:01 | |* 1 | VIEW | | 99999 | 19M| 4573 (1)| 00:00:01 | | 2 | WINDOW BUFFER | | 99999 | 29M| 4573 (1)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| LARGET | 99999 | 29M| 4573 (1)| 00:00:01 | |* 4 | INDEX FULL SCAN | PK_IND | 99999 | | 224 (1)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CEIL("from$_subquer y$_003"."rowlimit_$$_total"*1/100)) 4 - filter("T1" IS NOT NULL) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4571 consistent gets 3656 physical reads 0 redo size 22863 bytes sent via SQL*Net to client 1269 bytes received via SQL*Net from client 68 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1000 rows processed SQL>从以上的2个演示对比,可以看到2种写法实际消耗逻辑读数目是类似的;即12c 中FETCH FIRST ROWS,OFFSET ROWS FETCH NEXT的写法在实际性能上并不比传统的rownum写法来的效率低。 但是可以看到CBO在评估fetch rows执行计划的成本时其 TABLE ACCESS BY INDEX ROWID成本要比rownum写法高出不少,这似乎是由于不同的基数计算导致的。
posted on 2013-03-19 00:52 Oracle和MySQL 阅读(1326) 评论(0) 编辑 收藏 举报