关于分页的高效写法

creat table test as select * from dba_objects;

SQL> create index ind_test on test(owner,object_type,created);

SQL> set autot traceonly;
SQL> select * from (select rownum rn,object_id,object_name,subobject_name,status from (select object_id,object_name,subobject_name,status from test where wner='SYS' and object_type='TABLE' order by created desc) where rownum<=50) t where rn>=1;

50 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1950179327

--------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |    50 |  5700 |     6   (0)| 00:00:01 |
|*  1 |  VIEW                           |          |    50 |  5700 |     6   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                 |          |       |       |            |          |
|   3 |    VIEW                         |          |   724 | 73124 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | TEST     |   724 | 99912 |     6   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IND_TEST |    51 |       |     3   (0)| 00:00:01 |                    
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=50)
   5 - access("OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')
       filter("OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         26  consistent gets
          0  physical reads
          0  redo size
       2934  bytes sent via SQL*Net to client
        502  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

从上面执行计划来看，是正确地利用了索引。这里是第1页的数据，那么取中间页数据来看呢？
SQL> select * from (select rownum rn,object_id,object_name,subobject_name,status from (select object_id,object_name,subobject_name,status from test where wner='SYS' and object_type='TABLE' order by created desc) where rownum<=49000) t where rn>=48951;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1950179327

--------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |   724 | 82536 |    41   (0)| 00:00:01 |
|*  1 |  VIEW                           |          |   724 | 82536 |    41   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                 |          |       |       |            |          |
|   3 |    VIEW                         |          |   724 | 73124 |    41   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | TEST     |   724 | 99912 |    41   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IND_TEST |   724 |       |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=48951)
   2 - filter(ROWNUM<=49000)
   5 - access("OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')
       filter("OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
        212  consistent gets
          0  physical reads
          0  redo size
        591  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

这里的一致读取达到了212，翻了近十倍，这里48950有条回表了，没有返回给客户，这样就造成了浪费。那么我们怎么避免这样的浪费呢？我们可以利用相同的索引，但是在索引上面先排好序，再分页，选择完ROWID，再回表查询。

SQL> select /*+ ordered use_nl(t,test)*/ object_id,object_name,subobject_name,status from (select rid from (select rownum rn,rid from (select rowid rid from test where wner='SYS' and object_type='TABLE' order by created desc) where rownum<=49000 ) where rn >=48951 ) t,test where t.rid=test.rowid;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1867647190

--------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |   724 | 99912 |   731   (1)| 00:00:09 |
|   1 |  NESTED LOOPS                   |          |   724 | 99912 |   731   (1)| 00:00:09 |
|*  2 |   VIEW                          |          |   724 | 18100 |     6   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY                |          |       |       |            |          |
|   4 |     VIEW                        |          |   724 |  8688 |     6   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IND_TEST |   724 | 35476 |     6   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY USER ROWID    | TEST     |     1 |   113 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RN">=48951)
   3 - filter(ROWNUM<=49000)
   5 - access("OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')
       filter("OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

先分页再回表的,看吧，这就是效果。COOL

下面用我现在项目中用到的ROW_NUMBER()来做一下实验对比

SQL> select object_id,object_name,subobject_name,status from (select object_id,object_name,subobject_name,status,row_number() over(order by created desc)rn  from test  where wner='SYS' and object_type='TABLE' ) where rn between 48951 and 49000;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 835048009

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |   724 | 82536 |    42   (3)| 00:00:01 |
|*  1 |  VIEW                          |          |   724 | 82536 |    42   (3)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY        |          |   724 | 99912 |    42   (3)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | TEST     |   724 | 99912 |    41   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IND_TEST |   724 |       |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=48951 AND "RN"<=49000)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("CREATED") DESC
              )<=49000)
   4 - access("OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')
       filter("OWNER"='SYS' AND "OBJECT_TYPE"='TABLE')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        140  consistent gets
          0  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
效果还好，不过还是用先索引排序分页，再回表的效果最后

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-669948/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-669948/