两种分页语句的写法的优劣

SELECT OBJECT_ID, OBJECT_NAME
FROM
(
SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
FROM
(
SELECT /*+ index_rs(t T_I) */OBJECT_ID, OBJECT_NAME FROM T ORDER BY OBJECT_NAME
)
WHERE ROWNUM <= 10
 )
 WHERE RN >= 1;
 
---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |    10 |   920 | 70538   (1)| 00:14:07 |
|*  1 |  VIEW                          |      |    10 |   920 | 70538   (1)| 00:14:07 |
|*  2 |   COUNT STOPKEY                |      |       |       |            |          |
|   3 |    VIEW                        |      | 81332 |  6274K| 70538   (1)| 00:14:07 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T    | 81332 |  1906K| 70538   (1)| 00:14:07 |
|   5 |      INDEX FULL SCAN           | T_I  | 81332 |       |   367   (1)| 00:00:05 |
---------------------------------------------------------------------------------------

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

   1 - filter("RN">=1)
   2 - filter(ROWNUM<=10)

 

SELECT /*+ first_rows */
 t.*
  FROM (SELECT rid, rn
          from (SELECT rowid as rid, rownum as rn
                  FROM t
                 ORDER BY object_name)
         WHERE rownum <= :2) a,
     t
 WHERE a.rn >= :3
   AND a.rid = t.rowid ;

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      | 81332 |  9372K| 81733   (1)| 00:16:21 |
|   1 |  NESTED LOOPS               |      | 81332 |  9372K| 81733   (1)| 00:16:21 |
|*  2 |   VIEW                      |      | 81332 |  1985K|   367   (1)| 00:00:05 |
|*  3 |    COUNT STOPKEY            |      |       |       |            |          |
|   4 |     VIEW                    |      | 81332 |  1985K|   367   (1)| 00:00:05 |
|   5 |      COUNT                  |      |       |       |            |          |
|   6 |       INDEX FULL SCAN       | T_I  | 81332 |  2462K|   367   (1)| 00:00:05 |
|   7 |   TABLE ACCESS BY USER ROWID| T    |     1 |    93 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

第二种比第一种好，减少了回表的成本。直接在索引层就运用到了第二个谓词RN>1.无论如何索引层的成本没减少。

 

我们把第二种查询，再次改写如下方式

SELECT /*+ first_rows */
 t.*
  FROM (SELECT rid, rn
          from (SELECT rowid as rid, rownum as rn,object_type
                  FROM t
                 ORDER BY object_name)
         WHERE rownum <= :2) a,
     t
 WHERE a.rn >= :3
   AND a.rid = t.rowid

 

最内层的查询增加了字段object_type字段，由于这个字段不在索引里，因此在9I，ORACLE选择回表获取这个字段。导致上面所说的优化失效。但是在10G，11G，这种优化却依然有效，ORACLE知道字段对查询是没有用的。因此就没有回表。

 

 

9I下：

 

--------------------------------------------------------------------------
| Id  | Operation                        |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             | 11517 |  2294K| 12343 |
|   1 |  NESTED LOOPS                    |             | 11517 |  2294K| 12343 |
|*  2 |   VIEW                           |             | 11517 |   224K|   826 |
|*  3 |    COUNT STOPKEY                 |             |       |       |       |
|   4 |     VIEW                         |             | 11517 |   224K|   826 |
|   5 |      COUNT                       |             |       |       |       |
|   6 |       TABLE ACCESS BY INDEX ROWID| T           | 11517 |   944K|   826 |
|   7 |        INDEX FULL SCAN           | T_I         | 11517 |       |    26 |
|   8 |   TABLE ACCESS BY USER ROWID     | T           |     1 |   184 |     1 |
--------------------------------------------------------------------------------

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

转载于:http://blog.itpub.net/22034023/viewspace-717382/