Oracle新版本支持top,ORACLE 12C新特性-分页查询(TOP N SQL) | 信春哥，系统稳，闭眼上线不回滚！...

休假了，有时间测试下12C的新功能了，今天测试下分页查询。在12C之前，要想分页查询，我们通常会用下面的SQL。

SQL> select *

2 from (select a.*, rownum rn

3 from (select id,

4 username

5 from top_test) a

6 where rn <= 10)

7 where rn > 0;

在12C版本，推出了TOP N SQL新特性，只需要向下面这样简单的SQL就可以实现。

SQL> SELECT * FROM top_test order by id fetch first 5 rows only;

下面测试下这个功能，测试环境OEL 5.7，ORACLE 12.1.0.1。首先创建测试表。

[oracle@dbdream ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jan 18 08:37:43 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 413372416 bytes

Fixed Size 2289016 bytes

Variable Size 322962056 bytes

Database Buffers 79691776 bytes

Redo Buffers 8429568 bytes

Database mounted.

Database opened.

SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE

---------- ---------- ---------- --------------------

2 4063594514 PDB$SEED READ ONLY

3 1587020587 STREAM MOUNTED

4 676818932 WIND MOUNTED

SQL> ALTER SESSION SET CONTAINER=stream;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

STREAM

SQL> startup

Pluggable Database opened.

SQL> create user stream identified by stream default tablespace users;

User created.

SQL> grant dba to stream;

Grant succeeded.

SQL> conn stream/stream@localhost/stream

Connected.

SQL> show con_name

CON_NAME

------------------------------

STREAM

SQL> show user

USER is "STREAM"

SQL> create table top_test(id number,username varchar2(30));

Table created.

SQL> insert into top_test select rownum,username from dba_users;

40 rows created.

SQL> commit;

Commit complete.

创建测试表后，测试分页查询功能。

SQL> SELECT * FROM top_test order by id fetch first 5 rows only;

ID USERNAME

---------- ------------------------------------------------------------

1 C##DBDREAM

2 STREAM

3 PDBADMIN

4 AUDSYS

5 GSMUSER

SQL> SELECT * FROM top_test order by id offset 5 rows fetch next 5 rows only;

ID USERNAME

---------- ------------------------------------------------------------

6 WIND

7 SPATIAL_WFS_ADMIN_USR

8 SPATIAL_CSW_ADMIN_USR

9 APEX_PUBLIC_USER

10 SYSDG

很方便，在对比下传统分页查询和12C的分页查询执行计划的变化。

–传统SQL：

SQL> select *

2 from (select a.*, rownum rn

3 from (select id,

4 username

5 from top_test

6 order by id) a

7 where rownum <= 5)

8 where rn > 0;

ID USERNAME RN

---------- ------------------------------------------------------------ ----------

1 C##DBDREAM 1

2 STREAM 2

3 PDBADMIN 3

4 AUDSYS 4

5 GSMUSER 5

Execution Plan

----------------------------------------------------------

Plan hash value: 1572730458

-------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5 | 215 | 3 (0)| 00:00:01 |

|* 1 | VIEW | | 5 | 215 | 3 (0)| 00:00:01 |

|* 2 | COUNT STOPKEY | | | | | |

| 3 | VIEW | | 40 | 1200 | 3 (0)| 00:00:01 |

|* 4 | SORT ORDER BY STOPKEY| | 40 | 1200 | 3 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL | TOP_TEST | 40 | 1200 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

–12C分页查询：

SQL> SELECT * FROM top_test order by id fetch first 5 rows only;

ID USERNAME

---------- ------------------------------------------------------------

1 C##DBDREAM

2 STREAM

3 PDBADMIN

4 AUDSYS

5 GSMUSER

Execution Plan

----------------------------------------------------------

Plan hash value: 763208110

-------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40 | 2240 | 3 (0)| 00:00:01 |

|* 1 | VIEW | | 40 | 2240 | 3 (0)| 00:00:01 |

|* 2 | WINDOW SORT PUSHED RANK| | 40 | 1200 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL | TOP_TEST | 40 | 1200 | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

12C的分页查询，执行计划更简单，个人感觉很像使用分组函数的执行计划，据说效率要比传统的分页效率要好很多。