[Turn]oracle in 12c for page clauses

Transferred from: http://blog.itpub.net/271063/viewspace-1061279/

--Connect database Create test user
--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 a test table
CREATE TABLE Big_table
As
Select RowNum ID, a.*
From All_objects A
where 1=0
/
ALTER TABLE big_table nologging;

--Add 2 million test records
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);

--View disk space consumption of approximately 280 trillion
Sql> select 35862*8 from dual;
35862*8
----------
286896

--Start using Oracle's own paging clause to go to the top three records.
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>

You can use this technique when you are in the provincial office to check the person. The original can't leave rownum. Does Ibatis have a setting that adapts to oracle12c?

[Turn]oracle in 12c for page clauses