休假了，有时间测试下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的分页查询，执行计划更简单，个人感觉很像使用分组函数的执行计划，据说效率要比传统的分页效率要好很多。

本文固定链接: http://www.dbdream.com.cn/2014/01/oracle-12c%e6%96%b0%e7%89%b9%e6%80%a7-%e5%88%86%e9%a1%b5%e6%9f%a5%e8%af%a2top-n-sql/ | 信春哥，系统稳，闭眼上线不回滚！