oracle12c分页,[转]oracle 12c 中的分页子句

转自：http://blog.itpub.net/271063/viewspace-1061279/

-- 连接数据库 创建测试用户

-- Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.1.0

-- Connected as [email protected]_192.168.56.120

create user scott identified by tiger default tablespace users;

grant connect,resource to scott;

grant unlimited tablespace to scott;

-- 创建测试表

create table big_table

as

select rownum id, a.*

from all_objects a

where 1=0

/

alter table big_table nologging;

-- 添加200万测试记录

declare

l_cnt number;

l_rows number := &1;

begin

insert /*+ append */

into big_table

select rownum, a.*

from all_objects a

where rownum <= &1;

l_cnt := sql%rowcount;

commit;

while (l_cnt < l_rows)

loop

insert /*+ APPEND */ into big_table

select rownum+l_cnt,

OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,

OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,

TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME,

SHARING,EDITIONABLE,ORACLE_MAINTAINED

from big_table

where rownum <= l_rows-l_cnt;

l_cnt := l_cnt + sql%rowcount;

commit;

end loop;

end;

/

alter table big_table add constraint big_table_pk primary key(id);

exec dbms_stats.gather_table_stats( user, ‘BIG_TABLE‘, estimate_percent=> 1);

-- 查看占用磁盘空间大约280兆

SQL> select 35862*8 from dual;

35862*8

----------

286896

-- 开始使用oracle自带的分页子句，去前三条记录。

SQL> set timing on

SQL>

SQL> select id,object_name from big_table

2  order by id fetch first 3 rows only ;

ID OBJECT_NAME

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

1 ORA$BASE

2 DUAL

3 DUAL

Executed in 0.531 seconds

SQL> select id,object_name from big_table

2  order by id

3  offset 3 rows fetch next 3 rows only;

ID OBJECT_NAME

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

4 MAP_OBJECT

5 SYSTEM_PRIVILEGE_MAP

6 SYSTEM_PRIVILEGE_MAP

Executed in 0.407 seconds

SQL> select id,object_name from big_table

2  order by id

3  offset 6 rows fetch next 3 rows only;

ID OBJECT_NAME

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

7 TABLE_PRIVILEGE_MAP

8 TABLE_PRIVILEGE_MAP

9 USER_PRIVILEGE_MAP

Executed in 0.406 seconds

SQL> select id,object_name from big_table order by id

2  offset 0 rows fetch next 3 rows only;

ID OBJECT_NAME

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

1 ORA$BASE

2 DUAL

3 DUAL

Executed in 0.406 seconds

SQL>

在省厅查人时，可以使用这项技术。原来离不了rownum. IBATIS是否有适应oracle12c的设置呢？

原文：http://www.cnblogs.com/ymy124/p/4694356.html