12C 支持 TOP N 查询

官方文档：
http://docs.oracle.com/cd/E16655_01/server.121/e17749/analysis.htm#BCFBIIAE

12.1终于支持top n查询了，不要rownum嵌套查询了，且分页也可以通过offset来实现

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]

{ ROW | ROWS } { ONLY | WITH TIES } ]

创建测试环境，
[code]SQL> create table n_obj as select * from all_objects;

SQL> desc n_obj;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)

SQL> select count(*) from n_obj;

COUNT(*)
----------
90808

SQL> select max(OBJECT_ID) from n_obj;

MAX(OBJECT_ID)
--------------
92167[/code]
1、查询top 5
[code]SQL> select object_id,object_name from n_obj order by object_id desc FETCH FIRST 5 ROWS ONLY;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
92167 OBJ
92166 WRP$_REPORTS_DETAILS_IDX02
92165 WRP$_REPORTS_DETAILS_IDX01
92164 SYS_IL0000005854C00009$$
92163 SYS_LOB0000005854C00009$$[/code]
2、去掉前3查询top 5
[code]SQL> select object_id,object_name from n_obj order by object_id desc offset 3 rows FETCH next 5 ROWS ONLY;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
92164 SYS_IL0000005854C00009$$
92163 SYS_LOB0000005854C00009$$
92162 WRP$_REPORTS_DETAILS
92161 WRP$_REPORTS_IDX02
92160 WRP$_REPORTS_IDX01

SQL> select object_id,object_name from n_obj order by object_id desc offset 3 rows FETCH FIRST 5 ROWS ONLY;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
92164 SYS_IL0000005854C00009$$
92163 SYS_LOB0000005854C00009$$
92162 WRP$_REPORTS_DETAILS
92161 WRP$_REPORTS_IDX02
92160 WRP$_REPORTS_IDX01[/code]
这里 FETCH FIRST和FETCH next效果一样，不过FETCH next只能跟在offset后面，分页的话就可以用offset 20，offset 40来实现。

3、查询出top 全部0.01%
[code]SQL> select object_id,object_name from n_obj order by object_id desc FETCH FIRST 0.01 PERCENT ROWS ONLY;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
92167 OBJ
92166 WRP$_REPORTS_DETAILS_IDX02
92165 WRP$_REPORTS_DETAILS_IDX01
92164 SYS_IL0000005854C00009$$
92163 SYS_LOB0000005854C00009$$
92162 WRP$_REPORTS_DETAILS
92161 WRP$_REPORTS_IDX02
92160 WRP$_REPORTS_IDX01
92159 WRP$_REPORTS
92158 WRP$_REPORTS_TIME_BANDS

10 rows selected.[/code]
4、有时候top n时，存在第n行和第n+1行的数据相同，加上WITH TIES可以将N+1行的数据也显示出来
[code]SQL> insert into n_obj select * from n_obj where OBJECT_ID=92160;

1 row created.

SQL> commit;

Commit complete.

SQL> select object_id,object_name from n_obj order by object_id desc offset 3 rows FETCH next 5 ROWS ONLY;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
92164 SYS_IL0000005854C00009$$
92163 SYS_LOB0000005854C00009$$
92162 WRP$_REPORTS_DETAILS
92161 WRP$_REPORTS_IDX02
92160 WRP$_REPORTS_IDX01

SQL> select object_id,object_name from n_obj order by object_id desc offset 3 rows FETCH next 5 ROWS WITH TIES;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
92164 SYS_IL0000005854C00009$$
92163 SYS_LOB0000005854C00009$$
92162 WRP$_REPORTS_DETAILS
92161 WRP$_REPORTS_IDX02
92160 WRP$_REPORTS_IDX01
92160 WRP$_REPORTS_IDX01

6 rows selected.[/code]