Oracle:如何仅返回部分结果？

我正在使用Oracle数据库.

在我的查询中,提取了100行.如果我想在rownum 50和60之间过滤行,那么查询是什么？

SELECT EMPLID, EFFDT, ACTION, ACTION_REASON 
from JOB where emplid ='12345'

Nick.. 8

大多数人通常会告诉你使用ROWNUM来做到这一点,但更简洁的方法是使用row_number()分析函数.

select EMPLID, EFFDT, ACTION, ACTION_REASON
from
(
SELECT EMPLID, EFFDT, ACTION, ACTION_REASON, row_number() over (order by emplid) rn
from JOB where emplid ='12345'
)
where rn between 50 and 60;

使用row_number函数可以在单个查询中对结果进行排序和编号,然后只需要一个包装器查询来获取所需的行.

大多数人通常会告诉你使用ROWNUM来做到这一点,但更简洁的方法是使用row_number()分析函数.

select EMPLID, EFFDT, ACTION, ACTION_REASON
from
(
SELECT EMPLID, EFFDT, ACTION, ACTION_REASON, row_number() over (order by emplid) rn
from JOB where emplid ='12345'
)
where rn between 50 and 60;

使用row_number函数可以在单个查询中对结果进行排序和编号,然后只需要一个包装器查询来获取所需的行.

由于我对乍得和尼克的方法进行了比较,以便对尼克的回答发表评论,我想我会在这里发表我的发现.我使用Tom Kyte的runstats包将它们与这个脚本进行比较:

begin
   runstats_pkg.rs_start('Chad');
   for i in 1..10000 loop
     for r in (
      SELECT EMPLID,EFFDT,ACTION,ACTION_REASON
      FROM (SELECT ROWNUM rnum, EMPLID,EFFDT,ACTION,ACTION_REASON
            FROM (SELECT EMPLID,EFFDT,ACTION,ACTION_REASON
                  FROM JOB
                  WHERE emplid = '12345')
            WHERE rownum <= 60
      )
      WHERE rnum >= 50
      ) loop
        null;
      end loop;
   end loop;
   runstats_pkg.rs_middle('Nick');
   for i in 1..10000 loop
     for r in (
      select EMPLID, EFFDT, ACTION, ACTION_REASON
      from
      (
      SELECT EMPLID, EFFDT, ACTION, ACTION_REASON, row_number() over (order by emplid) rn
      from JOB where emplid ='12345'
      )
      where rn between 50 and 60
      ) loop
        null;
      end loop;
    end loop;
   runstats_pkg.rs_stop(0,false,false,false,false,false,false,false,false);
end;
/

结果:

Run1 = Chad
Run2 = Nick

*** Comparative Time Report ***
Run                                   Time (hsecs)
--------------------------------------------------
Run1                                            69
Run2                                            77

Run1 ran in 89.61% of the time of Run2
Run2 ran in 111.59% of the time of Run1

PL/SQL procedure successfully completed.

使用autotrace可以看出计划非常相似:

SQL>           SELECT EMPLID,EFFDT,ACTION,ACTION_REASON
  2            FROM (SELECT ROWNUM rnum, EMPLID,EFFDT,ACTION,ACTION_REASON
  3                  FROM (SELECT EMPLID,EFFDT,ACTION,ACTION_REASON
  4                        FROM JOB
  5                        WHERE emplid = '12345')
  6                  WHERE rownum <= 60
  7            )
  8            WHERE rnum >= 50
  9  /

no rows selected


Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    41 |     3   (0)|
|*  1 |  VIEW                         |         |     1 |    41 |     3   (0)|
|*  2 |   COUNT STOPKEY               |         |       |       |            |
|   3 |    TABLE ACCESS BY INDEX ROWID| JOB     |     1 |    13 |     3   (0)|
|*  4 |     INDEX RANGE SCAN          | JOB2_PK |     1 |       |     2   (0)|
------------------------------------------------------------------------------

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

   1 - filter("RNUM">=50)
   2 - filter(ROWNUM<=60)
   4 - access("EMPLID"=12345)


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

SQL>           select EMPLID, EFFDT, ACTION, ACTION_REASON
  2            from
  3            (
  4            SELECT EMPLID, EFFDT, ACTION, ACTION_REASON, row_number() over (order by emplid) rn
  5            from JOB where emplid ='12345'
  6            )
  7            where rn between 50 and 60
  8  /

no rows selected


Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    41 |     3   (0)|
|*  1 |  VIEW                         |         |     1 |    41 |     3   (0)|
|*  2 |   WINDOW NOSORT STOPKEY       |         |     1 |    17 |     3   (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| JOB     |     1 |    17 |     3   (0)|
|*  4 |     INDEX RANGE SCAN          | JOB2_PK |     1 |       |     2   (0)|
------------------------------------------------------------------------------

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

   1 - filter("RN">=50 AND "RN"<=60)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMPLID")<=60)
   4 - access("EMPLID"=12345)
       filter("EMPLID"=12345)


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

似乎在两种方法之间没有太多选择,尽管Chad's在我的数据库上的速度一直比较快,这是:

Oracle数据库10g 10.2.0.3.0版 - 64位生产

在Oracle中有点棘手,我认为你必须做这样的事情:

SELECT EMPLID,EFFDT,ACTION,ACTION_REASON
FROM (SELECT ROWNUM rnum, EMPLID,EFFDT,ACTION,ACTION_REASON
      FROM (SELECT EMPLID,EFFDT,ACTION,ACTION_REASON
            FROM JOB
            WHERE emplid = '12345')
      WHERE rownum <= 60
)
WHERE rnum >= 50;