【SQL】ページング処理用のSQL

# はじめに

Googleの検索結果などで「○○件中××件」という表記が見られますが、このように複数ページに結果を表示してページを遷移させることを「ページング」と呼びます。

今回はページング処理のベースとなる、データをDBから取り出す際のSQLを少し考えてみました。

# 使用した環境

## MySQL

* OS
 * CentOS7.7（1908）
* RDBMS
 * MySQL8.0.19

## Oracle

* Oracle社が提供している[Oracle Live SQL](https://livesql.oracle.com/)でOracle19cを利用しました。

# 作成したSQL

* [【MySQL】SQLで基本統計量を求める](https://qiita.com/nkojima/items/2c483d4ddbdb29439c87)で作成した「八ヶ岳の山々のデータ」を使用して、「標高の低い順に10件ずつデータを取得する」というSQLを作りました。

## MySQLの場合

* 以下のSQLでは、「山データを標高の昇順に並べて、先頭（OFFSET=0）から10件取得する」という処理が行われます。
* ページを遷移させたい時は、OFFSET値だけ変更します。
 * 2ページ目を表示させる場合、`LIMIT 10 OFFSET 10`となります。
* ページに表示させたい件数を調節する時は、LIMIT値を変更します。
 * 5件ずつ表示させる場合、`LIMIT 5 OFFSET 0`となります。

```sql:MySQLの場合
SELECT
  *
FROM
  mountain
ORDER BY
  altitude ASC
LIMIT 10
OFFSET 0;
```

```text:実行結果
+----+--------------+----------+--------------+
| id | name         | altitude | note         |
+----+--------------+----------+--------------+
| 23 | 八子ヶ峰     |     1833 | 北八ヶ岳     |
| 21 | 双子山       |     2224 | 北八ヶ岳     |
| 16 | 丸山         |     2330 | 北八ヶ岳     |
| 20 | 大岳         |     2381 | 北八ヶ岳     |
| 17 | 茶臼山       |     2384 | 北八ヶ岳     |
|  2 | 西岳         |     2398 | 南八ヶ岳     |
| 18 | 縞枯山       |     2403 | 北八ヶ岳     |
| 19 | 北横岳       |     2480 | 北八ヶ岳     |
| 15 | 中山         |     2496 | 北八ヶ岳     |
|  1 | 編笠山       |     2524 | 南八ヶ岳     |
+----+--------------+----------+--------------+
```

## Oracleの場合

### Oracle12c以降の場合

* Oracle12c以降であれば、`OFFSET n ROWS`と`FETCH FIRST n ROWS ONLY`を組み合わせて、「山データを標高の昇順に並べて、先頭（OFFSET=0）から10件取得する」という処理を実現できます。
 * @nakaie さん、コメントありがとうございました。
* MySQLの`OFFSET`と`LIMIT`の組み合わせよりもわずかにSQLが長くなりますが、`OFFSET n ROWS`と`FETCH FIRST n ROWS ONLY`の組み合わせの方が、SQLを見た時に処理内容を素直に理解しやすいと感じました。


```sql:Oracle12c以降の場合
SELECT
  *
FROM
  mountain
ORDER BY
  altitude ASC
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY;
```

```text:実行結果
+----+----------+------+----------+
| ID | 山名     | 標高 | 備考     |
+----+----------+------+----------+
| 23 | 八子ヶ峰 | 1833 | 北八ヶ岳 |
| 21 | 双子山   | 2224 | 北八ヶ岳 |
| 16 | 丸山     | 2330 | 北八ヶ岳 |
| 20 | 大岳     | 2381 | 北八ヶ岳 |
| 17 | 茶臼山   | 2384 | 北八ヶ岳 |
| 2  | 西岳     | 2398 | 南八ヶ岳 |
| 18 | 縞枯山   | 2403 | 北八ヶ岳 |
| 19 | 北横岳   | 2480 | 北八ヶ岳 |
| 15 | 中山     | 2496 | 北八ヶ岳 |
| 1  | 編笠山   | 2524 | 南八ヶ岳 |
+----+----------+------+----------+
```

### Oracle11g以前の場合

* 以下のSQLでは、「インラインビューで標高の昇順に並べた時のROW_NUMBERを取得して、標高の昇順に付けられた通し番号の範囲を指定して先頭（通し番号=1）から10件取得する」という処理が行われます。
* MySQLやOracle12c以降と比べて、かなりSQLが長く複雑になってしまいます...

```sql:Oracle11g以前の場合
SELECT
  ID,
  山名,
  標高,
  備考
FROM
  (
    SELECT
      id AS ID,
      name AS 山名,
      altitude AS 標高,
      note AS 備考,
      ROW_NUMBER() OVER(ORDER BY altitude ASC) AS 通し番号
    FROM
      mountain
  )
WHERE
  通し番号 >= 1
  AND 通し番号 <= 10
ORDER BY
  標高 ASC;
```

# 備忘録

* Oracle12c以降ではMySQLの`AUTO INCREMENT`に相当する`GENERATED ALWAYS AS IDENTITY`および`GENERATED BY DEFAULT AS IDENTITY`が登場しました。
 * Oracle11gまでは、`AUTO INCREMENT`に近いものとして`SEQUENCE`や`列の最大値＋1`を使うケースが多かったようです。
* 今回は自動採番した後に`id`列の値を書き換える必要が無かったため、`GENERATED ALWAYS AS IDENTITY`を使いました。

```sql:Oracle用のCREATE文
CREATE TABLE mountain ( 
  id NUMBER(2) GENERATED ALWAYS AS IDENTITY,
  name VARCHAR2(50) NOT NULL,  
  altitude NUMBER(4) NOT NULL, 
  note VARCHAR2(200), 
  CONSTRAINT PK_id PRIMARY KEY (id) 
);
```

# 参考URL

* [Oracle Database 12c の OFFSET / FETCH 句を試す](https://qiita.com/plusultra/items/add5d997a0ec02a08955)
* [Oracle Database 12c の自動採番列を試す](https://qiita.com/plusultra/items/ff1725d5e4f96ad60e3d)