Oracle Database 12c の OFFSET / FETCH 句を試す

SELECT文の途中だけ引き出す

SELECT文の結果を一定の単位ごとに切り出したい場合があります。ホームページで一覧を表示する場合に「次ページ」「前ページ」と表示される画面などに使われます。
ここでは単一のSELECT文の結果を一部分だけ抜き出す処理について検証しています。

Oracle Database 11gの構文

Oracle Database 11gでは一般的にROW_NUMBER関数を使います。ROW_NUMBER関数は、SELECT文の出力結果に対して番号を出力してくれます。
たとえば以下はemployeesテーブルからの検索結果から、salary列の値の上位3～5番目の値を取得する例です。

 SELECT first_name, salary FROM (
      SELECT first_name, salary, 
        ROW_NUMBER() OVER (ORDER BY salary DESC)
               ranking FROM employees
　)
 WHERE ranking BETWEEN 3 AND 5

Oracle Database 12c以降の構文

Oracle Database 12c (12.1) では、より洗練された構文であるOFFSET/FETCH句を利用することができます。SELECT文の先頭からレコードを読み飛ばす場合はOFFSET句を、出力されるレコードを指定するにはFETCH句を使います。

• OFFSET n ROWS
  　ROWSはROWでもかまいません。OFFSET句を省略すると全レコードが対象になります。

• FETCH FIRST n ROWS ONLY
  出力されるレコード数を制限します。ROWSはROWでもかまいません。またFIRSTはNEXTと書いても同じ動作になります。

前述の例をOracle Database 12cの構文で書き直すと下記のようになります。ずいぶんシンプルになります。

SELECT first_name, salary FROM employees ORDER BY salary DESC
OFFSET 2 ROWS FETCH FIRST 3 ROWS ONLY ;

PERCENT ROWSとWITH TIES

FETCH FIRST n ROWS構文は、出力するレコード数を厳密に指定しますが、FETCH FIRST n PERCENT ROWS ONLY と書くこともできます。PERCENTを追加指定すると、全体から指定した割合のレコードを返します。
FETCH FIRST n ROWS WITH TIESと記述すると、同一値のレコードも出力されるようになります。

FETCH句の内部

FETCH句を使ったSELECT文の実行計画を確認します。

SQL> SELECT first_name, salary FROM employees ORDER BY salary DESC
        OFFSET 2 ROWS FETCH FIRST 3 ROWS ONLY ;

<<出力省略>>

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor()) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  g735dd6u6c05q, child number 0
-------------------------------------
SELECT first_name, salary FROM employees ORDER BY salary DESC OFFSET 2
ROWS FETCH FIRST 3 ROWS ONLY

Plan hash value: 2698234872

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |       |       |     4 (100)|          |
|*  1 |  VIEW                    |           |    11 |   561 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |    11 |   275 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMPLOYEES |    11 |   275 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
   1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN
              (2>=0) THEN 2 ELSE 0 END +3 AND "from$_subquery$_002"."rowlimit_$$_rownumber">2))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC
              )<=CASE  WHEN (2>=0) THEN 2 ELSE 0 END +3)

Note
   - dynamic statistics used: dynamic sampling (level=2)

29 rows selected.

OFFSET / FETCH 構文は内部的にはサブクエリーが生成されて ROW_NUMBER 関数を実行して出力レコードを決定していることが分かります。