两种分页查询语句的区别

Question

1)select * from (select e.*,rownum rn from (select * from emp) e where rownum <=12) where rn >=2;2)select * from (select e.*,rownum rn from emp e) where rn between 2 and 12;我试过了，感觉两者效率差不多，不知两者有没有什么区别？

Answer 1

1效率高于2是肯定的，只是越查询后面的数据差距越小。
你的测试表emp数据量太小，反映不出差距。
看看一下面50000条数据的test表的执行计划中的一致读就知道了，第一种6，第二种244
另外看到执行计划中的COUNT STOPKEY，这是oracle针对分页的优化。
SQL> select count(*) from test;  COUNT(*)
----------
     50413SQL> 
SQL> SELECT *
  2    FROM (SELECT rownum rn, t.*
  3            FROM (SELECT * FROM test) t
  4           WHERE rownum <= 50)
  5   WHERE rn >= 30;已选择21行。
执行计划
----------------------------------------------------------
Plan hash value: 2447203903----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    50 |  4600 |     2   (0)| 00:00:01 |
|*  1 |  VIEW               |      |    50 |  4600 |     2   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST |    50 |  1450 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("RN">=30)
   2 - filter(ROWNUM<=50)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1025  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         21  rows processedSQL> SELECT *
  2    FROM (SELECT rownum rn, t.* FROM (SELECT * FROM test) t)
  3   WHERE rn BETWEEN 30 AND 50;已选择21行。
执行计划
----------------------------------------------------------
Plan hash value: 1984214786----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 50413 |  4529K|    58   (4)| 00:00:01 |
|*  1 |  VIEW               |      | 50413 |  4529K|    58   (4)| 00:00:01 |
|   2 |   COUNT             |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST | 50413 |  1427K|    58   (4)| 00:00:01 |
----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("RN"<=50 AND "RN">=30)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        244  consistent gets
          0  physical reads
          0  redo size
       1025  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         21  rows processed

Answer 2

--来看看兵哥给的例子：
SQL> SELECT *
  2    FROM (SELECT rownum rn, t.*
  3            FROM (SELECT * FROM test) t
  4           WHERE rownum <= 50)
  5   WHERE rn >= 30;
--第一个查询，先取得t表中的前50行，在从第30行取到第50行
--我们可以看到IO为6
SQL> SELECT *
  2    FROM (SELECT rownum rn, t.* FROM (SELECT * FROM test) t)
  3   WHERE rn BETWEEN 30 AND 50;
--第二个查询，先获取t表中的所有行数，再取第30行到第50行，
--IO为244
--只有大表才能明显地看到效率上的差别

Answer 3

第一句应该是1)select * from (select e.*,rownum rn  from emp where rownum <=12) where rn >=2; 吧？