（这个分页语句没有过滤条件，因此会扫描表中的所有分区。因为排序列恰好是范围分区列，范围分区每个分区的数据也是递增的，这时我们创建索引可以创建为local索引。但是如果将范围分区改为list分区或者hash分区，这时我们就必须创建global索引，因为list分区和hash分区是无序的。）
SQL> create index idx_test_id on p_test(object_id,0) local; ---创建local索引
select * from (select * from (select a.*,rownum rn from (select /*+ index(p_test idx_test_id ) */ * from p_test order by object_id) a ) where rownum <=10) where rn >=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_ID bxw1059jmgxvx, child number 0
-------------------------------------
select * from (select * from (select a.*,rownum rn from (select /*+
index(p_test idx_test_id ) */ * from p_test order by object_id) a )
where rownum <=10) where rn >=1
Plan hash value: 1291390031
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 | 1 |
|* 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 5 | 1 |
|* 2 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 5 | 1 |
| 3 | VIEW | | 1 | 63696 | 10 |00:00:00.01 | 5 | 1 |
| 4 | COUNT | | 1 | | 10 |00:00:00.01 | 5 | 1 |
| 5 | VIEW | | 1 | 63696 | 10 |00:00:00.01 | 5 | 1 |
| 6 | PARTITION RANGE ALL | | 1 | 63696 | 10 |00:00:00.01 | 5 | 1 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| P_TEST | 1 | 63696 | 10 |00:00:00.01 | 5 | 1 |
| 8 | INDEX FULL SCAN | IDX_TEST_ID | 1 | 63696 | 10 |00:00:00.01 | 3 | 1 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
32 rows selected.
例子2：分页语句（根据object_name排序）
select * from (selct * from (select a.*,rownum rn from (select * from p_test order by object_name) a ) where rownum <=10) where rn >=1;
这时候我们需要创建global索引，因为如果是本地索引就会产生 sort order by