で、オフセット法に比べてシーク法のページネーションはどれだけ早いの？RDB毎に。

# 動機
オフセット法とシーク法についてはこちらで紹介していますが([offsetでページネーションは遅い。これからはシーク法だ！](https://qiita.com/madilloar/items/b4e786a932ef9d4551b9 "https://qiita.com/madilloar/items/b4e786a932ef9d4551b9"))、主要なリレーショナルデータベース毎に、オフセット法によるページネーションsqlと比較してどれだけ早いのか確認してみました。


100万件のデータから最後の10行を取得するオフセット法sqlとシーク法sqlを比較してます。
前提合わせしてなかったり、計測回数も1回なので、真面目に計測していないですが、
オフセット法とシーク法の違いは感じられると思います。

| 試したデータベース | オフセット法 | シーク法 |
|:-------------|:-------|:-------|
| MySQL | 4.40秒 | 0.00秒 |
| PostgreSQL | 15.146秒 | 2.425秒 |
| Oracle | 8.95秒 | 0.04秒 |
| SQLServer | 6.557秒 | 0秒 |
| DB2 | 8.287903秒 | 0.009107秒 |
| sqlite | 12.043秒 | 0.000秒 |


また、上記を調べる中で、各種リレーショナルデータベースでテーブル作成やインデックスの作成、大量件数のランダムなデータ生成、統計情報の取得や実行計画の取得までの一連の作業を後で見返すよう用に書いています。
<font color="red">途中黒三角▼の行がありますが、ここはsqlなど縦に長いので折りたたんでいます。参照する場合は、その行をクリックしてください。</font>

# 何をやるか？

他えば、受注管理システムの受注明細画面のバックエンドで実行するページネーション用のsqlを作っていることをイメージしてください。
ざっくりした要件では、最近受注した明細を見たいというものです。
最終的に決まった外部設計は、受注日付の降順、製品IDの昇順、受注明細IDの昇順でソートした受注明細を10件毎にページネーションするというものです。

1. ページネーションを試すテーブルの作成(受注明細テーブルの作成)。
2. これに100万件のランダムなテストデータを登録。
3. この受注明細を、受注日付の降順、製品IDの昇順、受注明細IDの昇順でソートしたものをページネーションで10件毎切り出すsql(オフセット法とシーク法)を用意。
4. それぞれのsqlの実行計画の比較と処理時間を比較。
5. またインデックスの有無で実行計画の比較と処理時間を比較。

# ページネーションを試すテーブルの作成(受注明細テーブルの作成)

<details><summary>受注明細テーブルの作成</summary><div>

何故そうなのか的な違いの発見。create table文を流そうとしたのですが、

SQLServerの場合:sql文を流した後に``go``と入力して``[Enter]``キーを押下しないと実行されません。

DB2の場合:Oracleで言うところのCUIツールのsqlplusに該当する``db2``CUIツール。コピペでsql文を貼り付けようとすると、コマンドラインのオプションに``-t``をつけずに実行すると、改行文字がsql文実行になってしまい、意図した挙動をしてくれません。


```sql:
  drop table t_order_item cascade constraint;

  create table t_order_item (
    order_item_id decimal(7,0) not null
    , product_id decimal(7,0)
    , order_qty decimal(7,0)
    , unit_prc decimal(7,3)
    , shipping_address_desc char(10)
    , order_date date
    , constraint pk_t_order_item primary key ( order_item_id )
  )
  ;
```

</div></details>

<details><summary>シーク法sqlに効果のあるインデックスの作成</summary><div>

``order by句``に指定されているカラムでインデックスを作成します。

ここで発見。インデックスの削除の仕方がリレーショナルデータベースで違いがありました。

## インデックスの削除

```sql:MySQL,SQLServer
  drop index idx1_t_order_item on t_order_item;
```

```sql:DB2,Oracle,PostgreSQL,sqlite3
  drop index idx1_t_order_item;
```

## インデックスの作成

```sql:
  create index idx1_t_order_item on t_order_item (order_date, product_id, order_item_id);
```

</div></details>


# 100万件のランダムなテストデータを登録

0-9の数字を格納した``t_digits``テーブルを作り、これを直積で件数を増やして100万件にする``v_million``ビューを作ります。
``v_million``ビューをもとに乱数でカラムの値を作って、受注明細テーブル``t_order_item``に登録します。

<details><summary>0から9の数字テーブルの作成と初期データ登録</summary><div>

```sql:
  drop table t_digits cascade constraint;

  create table t_digits (
    digit_num decimal(1,0) not null
    , constraint pk_t_digits primary key (digit_num)
  );

  insert into t_digits (digit_num) values (0);
  insert into t_digits (digit_num) values (1);
  insert into t_digits (digit_num) values (2);
  insert into t_digits (digit_num) values (3);
  insert into t_digits (digit_num) values (4);
  insert into t_digits (digit_num) values (5);
  insert into t_digits (digit_num) values (6);
  insert into t_digits (digit_num) values (7);
  insert into t_digits (digit_num) values (8);
  insert into t_digits (digit_num) values (9);

  commit;
```

</div></details>

<details><summary>0から99万9,999の数字(100万件)ビューの作成</summary><div>

```sql:
  drop view v_million;

  create view v_million (
    digit_num
  ) as
  select
    d1.digit_num
    + ( d2.digit_num * 10 )
    + ( d3.digit_num * 100 )
    + ( d4.digit_num * 1000 )
    + ( d5.digit_num * 10000 )
    + ( d6.digit_num * 100000 ) as seq
  from
    t_digits d1
    , t_digits d2
    , t_digits d3
    , t_digits d4
    , t_digits d5
    , t_digits d6
    ;
```

</div></details>


上記の受注明細テーブル``t_order_item``にテストデータとして、100万件のデータを登録します。
カラムにセットする値は乱数により生成します。
下記insert文で、特定の日付範囲をランダムに設定している箇所がありますが(2019-01-01から2019-05-31)、
分かりにくいので、その考え方を記します。

考え方： ``from_日付 + mod(ランダムな正の整数, (to_日付 - from_日付))``
``mod``は剰余を求めています。

以下にデータベース毎のテストデータ登録と統計情報取得について記します。

<details><summary>MySQLのテストデータ登録と統計情報の取得</summary><div>

テスト用データベースを作成して、そこに接続します。

```sql:
create database testdb;
use testdb;
```

覚書:ダミー表の使用例。というか、特に指定しなくてよい。

```sql:ダミー表の使用例
  select 
  substring(md5(cast(rand() as char)), 1, 10) as shipping_address_desc;
```

100万件のテストデータinsert。

```sql:100万件のテストデータinsert
  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , floor(1 + rand() * 5) as product_id
    , floor(1 + rand() * 1000) as order_qty
    , round(cast( (rand() * 1000000) as decimal(10,3) ) % 10000, 3) as unit_prc
    , substring(md5(cast(rand() as char)), 1, 10) as shipping_address_desc
    , date_add(
        cast('2019-01-01' as date)
        , interval (
          cast(floor(1 + rand() * 365) as decimal(3,0))
          % cast(datediff(cast('2019-05-31' as date), cast('2019-01-01' as date)) as decimal(3,0))
        )
        day
      ) as order_date
  from
    v_million
  ;
  commit;
```

初期データを登録した後に、統計情報を取得します。

```sql:統計情報の取得
  analyze table t_order_item;
```

</div></details>

<details><summary>PostgreSQLのテストデータ登録と統計情報の取得</summary><div>

テスト用データベースを作成して、そこに接続します。

```sql:
create database testdb;
\c testdb;
```

覚書:ダミー表の使用例。

```sql:ダミー表の使用例
  select 
  substring(md5(cast(random() as varchar)), 1, 10) as shipping_address_desc;
```

100万件のテストデータinsert。

```sql:100万件のテストデータinsert
  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , floor(1 + random() * 5) as product_id
    , floor(1 + random() * 1000) as order_qty
    , round(cast( (random() * 1000000) as decimal(10,3) ) % 10000, 3) as unit_prc
    , substring(md5(cast(random() as varchar)), 1, 10) as shipping_address_desc
    , cast('2019-01-01' as date)
      + cast(floor(1 + random() * 365) as integer) % cast(cast('2019-05-31' as date) - cast('2019-01-01' as date) as integer)
  from
    v_million
  ;
  commit;
```

初期データを登録した後に、統計情報を取得します。

```sql:統計情報の取得
  analyze t_order_item;
```

</div></details>

<details><summary>Oracleのテストデータ登録と統計情報の取得</summary><div>

SYSTEMユーザでテストを実施しますが、SYSTEMユーザには、乱数生成に使用する``DBMS_CRYPTO``パッケージの権限が付与されていないので付与します。

```sql:権限付与
  sqlplus /nolog
  connect / as sysdba
  grant execute on dbms_crypto to system;
```

日付文字列を``date``に``cast``する度に日付書式を指定するのが面倒なので、この後で使うsql文のデフォルト書式を設定します。

```sql:日付書式の設定
  alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
```

覚書:ダミー表の使用例。

```sql:ダミー表の使用例
  select 
  substr(dbms_crypto.hash(utl_i18n.string_to_raw (dbms_crypto.randomnumber, 'AL32UTF8'),2 ), 1, 10) as shipping_address_desc
  from dual;
```

100万件のテストデータinsert。

```sql:100万件のテストデータinsert
  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , mod( dbms_crypto.randomnumber, 5) + 1 as product_id
    , mod( dbms_crypto.randomnumber, 1000) + 1 as order_qty
    , (mod( dbms_crypto.randomnumber, 1000000) + 1) / 1000 as unit_prc
    , substr(dbms_crypto.hash(utl_i18n.string_to_raw (dbms_crypto.randomnumber, 'AL32UTF8'),2 ), 1, 10) as shipping_address_desc
    , cast('2019-01-01' as date)
      + mod(cast(mod( dbms_crypto.randomnumber, 365) as number) , cast(cast('2019-05-31' as date) - cast('2019-01-01' as date) as number))
  from
    v_million
  ;
  commit;
```

初期データを登録した後に、統計情報を取得します。

```sql:統計情報の取得
  begin
    dbms_stats.gather_table_stats(
      ownname       => 'system'
      ,tabname      => 't_order_item'
      ,method_opt   => 'for all indexed'
      ,cascade      => true
    );
  end;
  /
```

</div></details>

<details><summary>SQLServerのテストデータ登録と統計情報の取得</summary><div>

テスト用データベースを作成して、そこに接続します。

```sql:
create database testdb;
go
use testdb;
go
```

覚書:ダミー表の使用例。

```sql:ダミー表の使用例
  select 
  convert(char(10), hashbytes('MD5', cast(rand(checksum(newid())) as char)), 2) as shipping_address_desc;
```

SQLServerの場合、``rand()関数``の``seed``値に``newid()関数``でユニークな値を設定しないと乱数にならず100万件同じ値になってしまいます。

100万件のテストデータinsert。

```sql:100万件のテストデータinsert
  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , floor(1 + rand(checksum(newid())) * 5) as product_id
    , floor(1 + rand(checksum(newid())) * 1000) as order_qty
    , round(cast((rand(checksum(newid())) * 1000000) as decimal(10,3)) % 10000, 3) as unit_prc
    , convert(char(10), hashbytes('MD5', cast(rand(checksum(newid())) as char)), 2) as shipping_address_desc
    , dateadd(day
      , cast( floor(1 + rand( checksum( newid() ) ) * 365) as decimal(3,0)) % cast(datediff(day , '2019-01-01', '2019-05-31') as decimal(3,0))
      , cast('2019-01-01' as date) ) as shipping_address_desc
  from
    v_million
  ;
  commit;
```

初期データを登録した後に、統計情報を取得します。

```sql:統計情報の取得
  analyze t_order_item;
```

</div></details>

<details><summary>DB2のテストデータ登録と統計情報の取得</summary><div>

dockerにDB2コンテナを作った直後の状態で、下記の100万件insertをすると、トランザクション・ログがフルとなってしまい、更新ができなくなります。
そのため、DB2コンテナ内部で、ログのサイズを増やす必要があります。

参考URL:

[[DB2 LUW] トランザクション・ログがフル (SQL0964C) になったときの対応 (IM-10-00W)](https://www.ibm.com/support/pages/db2-luw-%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E3%83%BB%E3%83%AD%E3%82%B0%E3%81%8C%E3%83%95%E3%83%AB-sql0964c-%E3%81%AB%E3%81%AA%E3%81%A3%E3%81%9F%E3%81%A8%E3%81%8D%E3%81%AE%E5%AF%BE%E5%BF%9C-im-10-00w "(https://www.ibm.com/support/pages/db2-luw-%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E3%83%BB%E3%83%AD%E3%82%B0%E3%81%8C%E3%83%95%E3%83%AB-sql0964c-%E3%81%AB%E3%81%AA%E3%81%A3%E3%81%9F%E3%81%A8%E3%81%8D%E3%81%AE%E5%AF%BE%E5%BF%9C-im-10-00w")

DB2の設定情報の内、トランザクションログに関する部分をgrepしています。

```bash:コンテナ内部
db2 get db cfg for testdb|grep LOG

Log file size (4KB)                         (LOGFILSIZ) = 1024
Number of primary log files                (LOGPRIMARY) = 16
Number of secondary log files               (LOGSECOND) = 22
```

セカンダリサイズは即時反映されるので、取りあえずMAXの4096を設定します。

```bash:コンテナ内部
db2 update db cfg for testdb using LOGSECOND 4096 IMMEDIATE
```

覚書:ダミー表の使用例。

```sql:ダミー表の使用例
  select 
  substring(hex(hash(cast(rand() as varchar), 2)), 1, 10) as shipping_address_desc
  from sysibm.sysdummy1;
```

100万件のテストデータinsert。

```sql:100万件のテストデータinsert。
  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , floor(1 + rand() * 5) as product_id
    , floor(1 + rand() * 1000) as order_qty
    , round(cast( (rand() * 1000000) as decimal(10,3) ) % 10000, 3) as unit_prc
    , substring(hex(hash(cast(rand() as varchar), 2)), 1, 10) as shipping_address_desc
    , cast('2019-01-01' as date)
      + cast(floor(1 + rand() * 365) as integer)
      % cast(days(cast('2019-05-31' as date)) - days(cast('2019-01-01' as date)) as integer)
  from
    v_million
  ;
  commit;
```

初期データを登録した後に、統計情報を取得します。

```sql:統計情報の取得
  runstats on table t_order_item with distribution and detailed indexes all;
```

</div></details>

<details><summary>sqliteのテストデータ登録と統計情報の取得</summary><div>

覚書:ダミー表の使用例。

```sql:ダミー表の使用例
  select 
  abs(random()) % 5 + 1  as product_id;
```

```sql:
  insert into t_order_item (order_item_id,product_id,order_qty,unit_prc,shipping_address_desc,order_date)
  select
    digit_num as order_item_id
    , abs(random()) % 5 + 1  as product_id
    , abs(random()) % 1000 + 1 as order_qty
    , (abs(random()) % 10000000.0 + 1.0) / 1000.0 as unit_prc
    , substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1)
      || substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',(abs(random()) % 36 + 1), 1) as shipping_address_desc
    , date((strftime('%s', '2019-01-01') + ((abs(random()) % 365)*86400) % (strftime('%s', '2019-05-31') - strftime('%s', '2019-01-01'))), 'unixepoch') as order_date
  from
    v_million
  ;
  commit;
```

初期データを登録した後に、統計情報を取得します。

```sql:統計情報の取得
  analyze;
```

</div></details>

# MySQL

## 処理時間をコンソール表示
他のデータベースでは、コマンドを入力する必要がありますが、MySQLでは特に入力せずとも処理時間が表示されます。

## 最初の10行を取得

実行計画を取得します。

```sql:最初の10行を取得
  explain
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
    limit 10 offset 0
  ;
```

index有無に関係なく、type=ALLのテーブルフルスキャンです。

```sql:index有り
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | t_order_item | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 856180 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
```

```sql:index無し
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | t_order_item | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1002885 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
```

処理時間を計測します。上記sqlの``explain``を外して実行します。
1回しか計測していませんが、index有無は処理時間の差は出ませんでした。
(4回くらい計測して平均すればよいのでしょうが面倒だったので端折りました)

```
index有り:10 rows in set (2.95 sec)
index無し:10 rows in set (2.82 sec)
```

## オフセット法による最後10行を取得

実行計画を取得します。

```sql:オフセット法による最後10行
  explain
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
    limit 10 offset 999990
  ;
```

index有無に関係なく、type=ALLのテーブルフルスキャンです。

```sql:オフセット法による最後の10行(index有り)
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | t_order_item | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 856180 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
```

```sql:オフセット法による最後の10行(index無し)
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | t_order_item | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1002885 |   100.00 | Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+----------------+
```

index有無は処理時間の差はわずかです。最初の10行取得と比べると約2倍弱の処理時間がかかっています。

```
index有り:10 rows in set (4.40 sec)
index無し:10 rows in set (4.50 sec)
```

## オフセット法による最後11行

この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。

```sql:オフセット法による最後11行
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 11 offset 999989
  ;
+---------------+------------+-----------+----------+-----------------------+------------+
| order_item_id | product_id | order_qty | unit_prc | shipping_address_desc | order_date |
+---------------+------------+-----------+----------+-----------------------+------------+
|        990154 |          5 |       584 |  267.004 | bb2fafd1b2            | 2019-01-01 |＜＝ここをキーにする
|        990952 |          5 |        94 |  414.368 | 9120ac3d9e            | 2019-01-01 |
|        992846 |          5 |       431 | 2061.600 | 2a35c06ab9            | 2019-01-01 |
|        993338 |          5 |       759 | 8705.299 | 9dd01ddc97            | 2019-01-01 |
|        993348 |          5 |       762 | 3606.435 | 87934dd6b0            | 2019-01-01 |
|        994258 |          5 |       491 | 5982.529 | 78dbe3f5a8            | 2019-01-01 |
|        994789 |          5 |       730 | 4800.993 | 0b5b210492            | 2019-01-01 |
|        995875 |          5 |       230 | 2739.531 | 8bcd9f4035            | 2019-01-01 |
|        996055 |          5 |       922 | 7283.062 | a099aad579            | 2019-01-01 |
|        997247 |          5 |       926 | 2229.296 | 71eb6c9324            | 2019-01-01 |
|        999160 |          5 |        98 | 8096.207 | 9c6236cd50            | 2019-01-01 |
+---------------+------------+-----------+----------+-----------------------+------------+
11 rows in set (4.36 sec)
```

# シーク法による最後の10行を取得

実行計画を取得します。

```sql:シーク法による最後の10行
  explain
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( cast('2019-01-01' as date) > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 = product_id and 990154 < order_item_id )
  order by
    order_date desc,
    product_id,
    order_item_id
  limit 10
  ;
```

index有り:type=rangeでインデックスの効果が出ています。``idx1_t_order_item``インデックスです。
index無し:type=ALLのテーブルフルスキャンのように見えますが、``Using where``となっているので``where句``で抽出対象が絞られる効果が出ています。

```sql:シーク法による最後の10行(index有り)
+----+-------------+--------------+------------+-------+---------------------------+-------------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys             | key               | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+--------------+------------+-------+---------------------------+-------------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t_order_item | NULL       | range | PRIMARY,idx1_t_order_item | idx1_t_order_item | 13      | NULL |   12 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------------+------------+-------+---------------------------+-------------------+---------+------+------+----------+---------------------------------------+
```

```sql:シーク法による最後の10行(index無し)
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t_order_item | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 882635 |    35.77 | Using where; Using filesort |
+----+-------------+--------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
```

オフセット法では最後の10行を取得するのに``4.40 sec``の処理時間がかかっていますが、シーク法のindex有りは``0.00 sec``です。index無しでも``2.47 sec``です。

```
index有り:10 rows in set (0.00 sec)
index無し:10 rows in set (2.47 sec)
```

# シーク法でのページジャンプ

各ページの最後の行がわかれば、それをキーにシーク法が仕掛けられます。下記SQLは10行でページングする場合の調べ方です。
考え方は、行数でMODするイメージ。 WINDOW関数が使える前提。

## 全てのページの境界を求める

実行計画を取得します。

```sql:全てのページの境界を求める
  explain
  select
    x.*,
    row_number() over(
      order by
        order_date desc, product_id, order_item_id
    ) + 1 page_number
  from
    ( select
        case mod(row_number() over(
          order by
            order_date desc, product_id, order_item_id
        ), 10)  -- 10行でページング
          when 0
          then 1
          else 0
        end page_boundary,
        t.*
      from
        t_order_item t
      order by
        order_date desc,
        product_id,
        order_item_id
    ) x
  where
    x.page_boundary = 1
  ;
```

index有無に関係なく、type=ALLのテーブルフルスキャンです。

```sql:index有り
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const |     10 |   100.00 | Using filesort |
|  2 | DERIVED     | t          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 856180 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+
```

```sql:index無し
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+---------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows    | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+---------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const |      10 |   100.00 | Using filesort |
|  2 | DERIVED     | t          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 1147185 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+---------+----------+----------------+
```

index有無は処理時間の差はわずかです。実際に10万行表示するのに20秒くらいかかりました。

```
index有り:100000 rows in set (21.17 sec)
index無し:100000 rows in set (20.77 sec)
```

# PostgreSQL

## 処理時間をコンソールに表示

```sql:
\timing
```

## 最初の10行を取得

実行計画を取得します。
本体のsql文は``MySQL``と同じです。``limit``と``offset``の組み合わせです。

<details><summary>最初の10行を取得</summary><div>

```sql:最初の10行を取得
  explain
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 0
  ;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

実行計画の見方がわからないです。index有無は関係ないようです。全件をソートして、``Parallel Seq Scan``なので、ワーカが2つあって、パラレルに検索しているのかもしれないですが、``Seq Scan``となっているので、結局フルスキャンなんだと思います。

```sql:index有り
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=22504.71..22505.88 rows=10 width=37)
   ->  Gather Merge  (cost=22504.71..119733.80 rows=833334 width=37)
         Workers Planned: 2
         ->  Sort  (cost=21504.69..22546.36 rows=416667 width=37)
               Sort Key: order_date DESC, product_id, order_item_id
               ->  Parallel Seq Scan on t_order_item  (cost=0.00..12500.67 rows=416667 width=37)
```

```sql:index無し
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=22504.71..22505.88 rows=10 width=37)
   ->  Gather Merge  (cost=22504.71..119733.80 rows=833334 width=37)
         Workers Planned: 2
         ->  Sort  (cost=21504.69..22546.36 rows=416667 width=37)
               Sort Key: order_date DESC, product_id, order_item_id
               ->  Parallel Seq Scan on t_order_item  (cost=0.00..12500.67 rows=416667 width=37)
```

処理時間を計測します。上記sqlの``explain``を外して実行します。
index有無で若干差が出ていますが、ざっくり1秒くらいです。

```
index有り:Time: 847.375 ms
idnex無し:Time: 1011.590 ms (00:01.012)
```

</div></details>

## オフセット法による最後10行を取得

実行計画を取得します。
本体のsql文は``MySQL``と同じです。``limit``と``offset``の組み合わせです。

<details><summary>オフセット法による最後10行を取得</summary><div>

```sql:オフセット法による最後10行
  explain
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 999990
  ;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

実行計画の見方がわからないです。index有無は関係ないようです。最初の10件と同じような実行計画ですが、さらに``JIT``なるものが登場しています。


```sql:オフセット法による最後の10行(index有り)
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=161018.59..161018.71 rows=1 width=37)
   ->  Gather Merge  (cost=63789.50..161018.59 rows=833334 width=37)
         Workers Planned: 2
         ->  Sort  (cost=62789.48..63831.15 rows=416667 width=37)
               Sort Key: order_date DESC, product_id, order_item_id
               ->  Parallel Seq Scan on t_order_item  (cost=0.00..12500.67 rows=416667 width=37)
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true
```

```sql:index無し
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Limit  (cost=161018.59..161018.71 rows=1 width=37)
   ->  Gather Merge  (cost=63789.50..161018.59 rows=833334 width=37)
         Workers Planned: 2
         ->  Sort  (cost=62789.48..63831.15 rows=416667 width=37)
               Sort Key: order_date DESC, product_id, order_item_id
               ->  Parallel Seq Scan on t_order_item  (cost=0.00..12500.67 rows=416667 width=37)
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true
```

処理時間を計測します。上記sqlの``explain``を外して実行します。
index有無で若干差が出ていますが、ざっくり15秒くらいです。

```
index有り:Time: 15145.981 ms (00:15.146)
index無し:Time: 15016.168 ms (00:15.016)
```

</div></details>

## オフセット法による最後11行

この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文は``MySQL``と同じです。``limit``と``offset``の組み合わせです。

<details><summary>オフセット法による最後11行</summary><div>

```sql:オフセット法による最後11行
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 11 offset 999989
  ;

 order_item_id | product_id | order_qty | unit_prc | shipping_address_desc | order_date
---------------+------------+-----------+----------+-----------------------+------------
        992619 |          5 |       648 |  231.046 | 7fce9ea93f            | 2019-01-01＜＝ここをキーにする
        992954 |          5 |       518 | 3058.612 | bddf40c90e            | 2019-01-01
        993407 |          5 |       202 | 1643.126 | ff13043c60            | 2019-01-01
        993878 |          5 |       305 | 5854.718 | e8590003f0            | 2019-01-01
        994495 |          5 |       973 | 8662.716 | 803e36b2be            | 2019-01-01
        994628 |          5 |       152 | 4002.453 | 24e4dfbcfc            | 2019-01-01
        995617 |          5 |       149 | 4843.632 | 12516bf6d8            | 2019-01-01
        996712 |          5 |       948 | 6764.766 | 224c8c3aaa            | 2019-01-01
        998088 |          5 |        23 | 4314.589 | 9def3af209            | 2019-01-01
        998210 |          5 |       682 |  146.102 | a032f90106            | 2019-01-01
        999235 |          5 |       549 | 7711.560 | 9875c20006            | 2019-01-01
(11 rows)

Time: 15068.021 ms (00:15.068)
```

</div></details>

# シーク法による最後の10行

実行計画を取得します。
本体のsql文は``MySQL``と同じです。

<details><summary>シーク法による最後の10行</summary><div>

```sql:シーク法による最後の10行
  explain
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( cast('2019-01-01' as date) > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 = product_id and 992619 < order_item_id )
  order by
    order_date desc,
    product_id,
    order_item_id
  limit 10
  ;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

オフセット法と比較すると違いが顕著です。index有りの方は、``Bitmap Heap Scan``や``Bitmap Index Scan``などインデックス効果が出てそうなキーワードが出ています。
index無しの方はオフセット法にはなかった``filter``というのが登場しています。恐らく``where句``で抽出対象が絞られる効果が出ていると思われます(MySQLと同じような挙動)。

```sql:シーク法による最後の10行(index有り)
                                                                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=48.89..48.91 rows=9 width=37)
   ->  Sort  (cost=48.89..48.91 rows=9 width=37)
         Sort Key: order_date DESC, product_id, order_item_id
         ->  Bitmap Heap Scan on t_order_item  (cost=13.41..48.75 rows=9 width=37)
               Recheck Cond: (('2019-01-01'::date > order_date) OR (('2019-01-01'::date = order_date) AND ('5'::numeric < product_id)) OR (('2019-01-01'::date = order_date) AND '5'::numeric  product_id) AND ('992619'::numeric < order_item_id)))
               ->  BitmapOr  (cost=13.41..13.41 rows=9 width=0)
                     ->  Bitmap Index Scan on idx1_t_order_item  (cost=0.00..4.43 rows=1 width=0)
                           Index Cond: (order_date < '2019-01-01'::date)
                     ->  Bitmap Index Scan on idx1_t_order_item  (cost=0.00..4.43 rows=1 width=0)
                           Index Cond: ((order_date = '2019-01-01'::date) AND (product_id > '5'::numeric))
                     ->  Bitmap Index Scan on idx1_t_order_item  (cost=0.00..4.54 rows=9 width=0)
                           Index Cond: ((order_date = '2019-01-01'::date) AND (product_id = '5'::numeric) AND (order_item_id > '992619'::numeric))
```

```sql:index無し 
                                                                                                                      QUERY PLAN                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=19751.25..19752.42 rows=10 width=37)
   ->  Gather Merge  (cost=19751.25..19757.32 rows=52 width=37)
         Workers Planned: 2
         ->  Sort  (cost=18751.23..18751.29 rows=26 width=37)
               Sort Key: order_date DESC, product_id, order_item_id
               ->  Parallel Seq Scan on t_order_item  (cost=0.00..18750.67 rows=26 width=37)
                     Filter: (('2019-01-01'::date > order_date) OR (('2019-01-01'::date = order_date) AND ('5'::numeric < product_id)) OR (('2019-01-01'::date = order_date) AND ('5'::numeric = product_id) AND ('992619'::numeric < order_item_id)))
```

オフセット法では最後の10行を取得するのに``00:15.146``秒の処理時間がかかっていますが、シーク法のindex有りは``2.425 ms``です。index無しでも``985.916 ms``です。段違いです。

```
index有り:Time: 2.425 ms
index無し:Time: 985.916 ms
```

</div></details>

# シーク法でのページジャンプ

## 全てのページの境界を求める

PostgreSQLの場合、問い合わせ結果が一定行数になるとページャが働いて``more``となって表示が止まります。一気に10万行表示したいので、ページャの機能をオフにします。

```sql:ページャオフ
\pset pager off
```

実行計画を取得します。

<details><summary>全てのページの境界を求める</summary><div>

```sql:全てのページの境界を求める
  explain
  select
    x.*,
    row_number() over(
      order by
        order_date desc, product_id, order_item_id
    ) + 1 page_number
  from
    ( select
        case mod(row_number() over(
          order by
            order_date desc, product_id, order_item_id
        ), 10)  -- 10行でページング
          when 0
          then 1
          else 0
        end page_boundary,
        t.*
      from
        t_order_item t
      order by
        order_date desc,
        product_id,
        order_item_id
    ) x
  where
    x.page_boundary = 1
  ;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

``Seq Scan``となており、``Parallel``ですらなくなっています。

```sql:index有り
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 WindowAgg  (cost=172682.84..212795.34 rows=5000 width=49)
   ->  Subquery Scan on x  (cost=172682.84..212682.84 rows=5000 width=41)
         Filter: (x.page_boundary = 1)
         ->  WindowAgg  (cost=172682.84..200182.84 rows=1000000 width=41)
               ->  Sort  (cost=172682.84..175182.84 rows=1000000 width=37)
                     Sort Key: t.order_date DESC, t.product_id, t.order_item_id
                     ->  Seq Scan on t_order_item t  (cost=0.00..18334.00 rows=1000000 width=37)
 JIT:
   Functions: 10
   Options: Inlining false, Optimization false, Expressions true, Deforming true
```

```sql:index無し
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 WindowAgg  (cost=172682.84..212795.34 rows=5000 width=49)
   ->  Subquery Scan on x  (cost=172682.84..212682.84 rows=5000 width=41)
         Filter: (x.page_boundary = 1)
         ->  WindowAgg  (cost=172682.84..200182.84 rows=1000000 width=41)
               ->  Sort  (cost=172682.84..175182.84 rows=1000000 width=37)
                     Sort Key: t.order_date DESC, t.product_id, t.order_item_id
                     ->  Seq Scan on t_order_item t  (cost=0.00..18334.00 rows=1000000 width=37)
 JIT:
   Functions: 10
   Options: Inlining false, Optimization false, Expressions true, Deforming true
```

index有無は処理時間の差はわずかです。実際に10万行表示するのに18秒くらいかかりました。

```
index有り:Time: 18301.758 ms (00:18.302)
index無し:Time: 17955.903 ms (00:17.956)
```

</div></details>

# Oracle

## 処理時間をコンソールに表示

```sql:
set timing on
```

1行当たりの文字数とページ行数の設定

```sql:
set linesize 120
set pagesize 100
```

実行計画を格納するPLAN表の作成

```sql:実行計画を格納するPLAN表の作成
@?/rdbms/admin/utlxpls.sql
```

## 最初の10行を取得

実行計画を取得します。
本体のsql文は``MySQL``や``PostgreSQL``とちょっと違います。

<details><summary>最初の10行を取得</summary><div>

```sql:最初の10行
  explain plan for
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  offset 0 rows fetch first 10 rows only
  ;
  select  plan_table_output from  table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

index有無に関係なく100万行の``TABLE ACCESS FULL``です。

```sql:index有り
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2137560040

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  1 |  VIEW                    |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|              |  1000K|    35M|    53M| 11226   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_ORDER_ITEM |  1000K|    35M|       |  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------


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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (0>=0) THEN 0
              ELSE 0 END +10 AND "from$_subquery$_002"."rowlimit_$$_rownumber">0)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_ORDER_ITEM"."ORDER_DATE")
               DESC ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=CASE  WHEN (0>=0)
              THEN 0 ELSE 0 END +10)
```

```sql:index無し
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2137560040

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  1 |  VIEW                    |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|              |  1000K|    35M|    53M| 11226   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_ORDER_ITEM |  1000K|    35M|       |  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (0>=0) THEN 0
              ELSE 0 END +10 AND "from$_subquery$_002"."rowlimit_$$_rownumber">0)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_ORDER_ITEM"."ORDER_DATE")
               DESC ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=CASE  WHEN (0>=0)
              THEN 0 ELSE 0 END +10)
```

処理時間を計測します。上記sqlの``explain plan for``と``select  plan_table_output from  table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));``を外して実行します。
index有無で若干差が出ていますが、ざっくり1秒かからないです。

```
index有り:Elapsed: 00:00:00.44
index無し:Elapsed: 00:00:00.48
```

</div></details>

## オフセット法による最後10行

実行計画を取得します。
本体のsql文は``MySQL``や``PostgreSQL``とちょっと違います。

<details><summary>オフセット法による最後10行を取得</summary><div>

```sql:オフセット法による最後10行
  explain plan for
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  offset 999990 rows fetch first 10 rows only
  ;
  select  plan_table_output from  table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

index有無に関係なく100万行の``TABLE ACCESS FULL``です。

```sql:オフセット法による最後の10行(index有り)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2137560040

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  1 |  VIEW                    |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|              |  1000K|    35M|    53M| 11226   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_ORDER_ITEM |  1000K|    35M|       |  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------


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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (999990>=0) THEN
              999990 ELSE 0 END +10 AND "from$_subquery$_002"."rowlimit_$$_rownumber">999990)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_ORDER_ITEM"."ORDER_DATE")
               DESC ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=CASE  WHEN
              (999990>=0) THEN 999990 ELSE 0 END +10)
```

```sql:オフセット法による最後の10行(index無し)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2137560040

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  1 |  VIEW                    |              |  1000K|   115M|       | 11226   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|              |  1000K|    35M|    53M| 11226   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_ORDER_ITEM |  1000K|    35M|       |  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN (999990>=0) THEN
              999990 ELSE 0 END +10 AND "from$_subquery$_002"."rowlimit_$$_rownumber">999990)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("T_ORDER_ITEM"."ORDER_DATE")
               DESC ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=CASE  WHEN
              (999990>=0) THEN 999990 ELSE 0 END +10)
```

処理時間を計測します。上記sqlの``explain plan for``と``select  plan_table_output from  table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));``を外して実行します。
index有無で若干差が出ていますが、ざっくり9秒くらいです。

```
index有り:Elapsed: 00:00:08.95
index無し:Elapsed: 00:00:09.29
```

</div></details>

## オフセット法による最後11行

この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文は``MySQL``や``PostgreSQL``とちょっと違います。

<details><summary>オフセット法による最後11行</summary><div>

```sql:オフセット法による最後11行
select
 *
from
 t_order_item
order by
 t_order_item.order_date desc,
 t_order_item.product_id,
 t_order_item.order_item_id
offset 999989 rows fetch first 11 rows only
;

ORDER_ITEM_ID PRODUCT_ID  ORDER_QTY   UNIT_PRC SHIPPING_A ORDER_DATE
------------- ---------- ---------- ---------- ---------- -------------------
       993345          5        771    715.027 8A754A27E2 2019-01-01 00:00:00＜＝ここをキーにする
       993625          5        460     84.191 C17AFC1ECE 2019-01-01 00:00:00
       994455          5        777     16.591 861B93AEA5 2019-01-01 00:00:00
       994525          5        692    490.703 25A11413D8 2019-01-01 00:00:00
       995956          5        159    442.299 D33F17230D 2019-01-01 00:00:00
       997695          5        953     44.002 2E51B69029 2019-01-01 00:00:00
       998152          5        614    638.381 6EF2FECF01 2019-01-01 00:00:00
       999040          5        111    331.362 0603022051 2019-01-01 00:00:00
       999143          5        343    453.721 D9B6FB3DD0 2019-01-01 00:00:00
       999408          5        342     142.84 577DD08BBB 2019-01-01 00:00:00
       999750          5        873    209.179 C5BE6F1053 2019-01-01 00:00:00

11 rows selected.

Elapsed: 00:00:09.29
```

</div></details>


# シーク法による最後の10行

実行計画を取得します。
本体のsql文は``MySQL``や``PostgreSQL``とちょっと違います。

<details><summary>シーク法による最後の10行</summary><div>

```sql:シーク法による最後の10行
  -- 日付書式を変えます。
  alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

  explain plan for
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( cast('2019-01-01' as date) > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 = product_id and 993345 < order_item_id )
  order by
    order_date desc,
    product_id,
    order_item_id
  fetch first 10 rows only
  ;
  select  plan_table_output from  table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

オフセット法と比較すると違いが顕著です。index有りの方は``INDEX RANGE SCAN``となっており、インデックスが効果を出しています。
index無しの方は``TABLE ACCESS FULL``ですが、``ROWS``を見ると、``7355``行なので``where句``で抽出対象が絞られています。

```sql:シーク法による最後の10行(index有り)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2789792141

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                   |    10 |  1210 |   949   (1)| 00:00:01 |
|*  1 |  VIEW                                 |                   |    10 |  1210 |   949   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK             |                   |  7355 |   265K|   949   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORDER_ITEM      |  7355 |   265K|   948   (1)| 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS       |                   |       |       |            |          |
|   5 |      BITMAP OR                        |                   |       |       |            |          |
|   6 |       BITMAP CONVERSION FROM ROWIDS   |                   |       |       |            |          |
|   7 |        SORT ORDER BY                  |                   |       |       |            |          |
|*  8 |         INDEX RANGE SCAN              | IDX1_T_ORDER_ITEM |       |       |    44   (0)| 00:00:01 |
|   9 |       BITMAP CONVERSION FROM ROWIDS   |                   |       |       |            |          |
|  10 |        SORT ORDER BY                  |                   |       |       |            |          |
|* 11 |         INDEX RANGE SCAN              | IDX1_T_ORDER_ITEM |       |       |     7   (0)| 00:00:01 |
|  12 |       BITMAP CONVERSION FROM ROWIDS   |                   |       |       |            |          |
|  13 |        SORT ORDER BY                  |                   |       |       |            |          |
|* 14 |         INDEX RANGE SCAN              | IDX1_T_ORDER_ITEM |       |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORDER_DATE") DESC
              ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=10)
   8 - access("ORDER_DATE"<CAST('2019-01-01' AS date))
       filter("ORDER_DATE"<CAST('2019-01-01' AS date))
  11 - access("ORDER_DATE"=CAST('2019-01-01' AS date) AND "PRODUCT_ID">5)
       filter("PRODUCT_ID">5 AND "ORDER_DATE"=CAST('2019-01-01' AS date))
  14 - access("ORDER_DATE"=CAST('2019-01-01' AS date) AND "PRODUCT_ID"=5 AND
              "ORDER_ITEM_ID">993345)
       filter("ORDER_ITEM_ID">993345 AND "PRODUCT_ID"=5 AND "ORDER_DATE"=CAST('2019-01-01' AS
              date))
```

```sql:シーク法による最後の10行(index無し)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2137560040

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |    10 |  1210 |  1588   (2)| 00:00:01 |
|*  1 |  VIEW                    |              |    10 |  1210 |  1588   (2)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|              |  7355 |   265K|  1588   (2)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL     | T_ORDER_ITEM |  7355 |   265K|  1587   (2)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORDER_DATE") DESC
              ,"T_ORDER_ITEM"."PRODUCT_ID","T_ORDER_ITEM"."ORDER_ITEM_ID")<=10)
   3 - filter("ORDER_DATE"<CAST('2019-01-01' AS date) OR "PRODUCT_ID">5 AND
              "ORDER_DATE"=CAST('2019-01-01' AS date) OR "ORDER_ITEM_ID">993345 AND
              "PRODUCT_ID"=5 AND "ORDER_DATE"=CAST('2019-01-01' AS date))
```

オフセット法では最後の10行を取得するのに``00:00:08.95``秒の処理時間がかかっていますが、シーク法のindex有りは``00:00:00.04``です。index無しでも``00:00:00.41``です。段違いです。

```
index有り:Elapsed: 00:00:00.04
index無し:Elapsed: 00:00:00.41
```

</div></details>

# シーク法でのページジャンプ

## 全てのページの境界を求める

実行計画を取得します。
Oracleは剰余の計算にmod関数を使います。

<details><summary>全てのページの境界を求める</summary><div>

```sql:全てのページの境界を求める
  explain plan for
  select
    x.*,
    row_number() over(
      order by
        order_date desc, product_id, order_item_id
    ) + 1 page_number
  from
    ( select
        -- Oracleは剰余の計算にmod関数を使います。
        case mod(row_number() over(
          order by
            order_date desc, product_id, order_item_id
        ), 10)  -- 10行でページング
          when 0
          then 1
          else 0
        end page_boundary,
        t.*
      from
        t_order_item t
      order by
        order_date desc,
        product_id,
        order_item_id
    ) x
  where
    x.page_boundary = 1
  ;
  select  plan_table_output from  table(dbms_xplan.display('PLAN_TABLE',NULL,'SERIAL'));
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

index有無に関係なく100万行の``TABLE ACCESS FULL``です。

```sql:index有り
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4158901137

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|   1 |  WINDOW NOSORT       |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|*  2 |   VIEW               |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|   3 |    WINDOW SORT       |              |  1000K|    35M|  1567   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T_ORDER_ITEM |  1000K|    35M|  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - filter("X"."PAGE_BOUNDARY"=1)
```

```sql:index無し
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4158901137

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|   1 |  WINDOW NOSORT       |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|*  2 |   VIEW               |              |  1000K|    72M|  1567   (1)| 00:00:01 |
|   3 |    WINDOW SORT       |              |  1000K|    35M|  1567   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T_ORDER_ITEM |  1000K|    35M|  1567   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - filter("X"."PAGE_BOUNDARY"=1)
```

index有無は処理時間の差はわずかです。実際に10万行表示するのに42秒くらいかかりました。

```
index有り:Elapsed: 00:00:43.80
index無し:Elapsed: 00:00:41.32
```

</div></details>

# SQLServer

## 処理時間をコンソール表示

```sql:
set statistics time on;
go
```

## 最初の10行を取得

実行計画を取得します。
本体のsql文は``Oracle``と同じです。``MySQL``や``PostgreSQL``とちょっと違います。

<details><summary>最初の10行を取得</summary><div>

```sql:最初の10行
set showplan_text on;
go
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  offset 0 rows fetch first 10 rows only
  ;
  go
set showplan_text off;
go
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

実行計画の見方がよくわからないです。フルスキャンになっているんだろうか？

```sql:index有り
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(TOP 10, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))
       |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]))                                                              
```

```sql:index無し
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(TOP 10, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))
       |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]))                                                              
```

処理時間を計測します。上記sqlの``set showplan_text on;``と``set showplan_text off;``を外して実行します。
ざっくり2秒です。

```
index有り:   CPU time = 1567 ms,  elapsed time = 1596 ms.
index無し:   CPU time = 1570 ms,  elapsed time = 1596 ms.
```

</div></details>

## オフセット法による最後10行

実行計画を取得します。
本体のsql文は``Oracle``と同じです。``MySQL``や``PostgreSQL``とちょっと違います。

<details><summary>オフセット法による最後10行を取得</summary><div>

```sql:オフセット法による最後10行
set showplan_text on;
go
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  offset 999990 rows fetch first 10 rows only
  ;
  go
set showplan_text off;
go
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

実行計画の見方がよくわからないです。フルスキャンになっているんだろうか？

```sql:オフセット法による最後の10行(index有り)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(OFFSET EXPRESSION:((999990)),TOP EXPRESSION:((10)))                                                                                          
       |--Sort(TOP 1000000, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))
            |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]))                                                         
```

```sql:オフセット法による最後の10行(index無し)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(OFFSET EXPRESSION:((999990)),TOP EXPRESSION:((10)))                                                                                          
       |--Sort(TOP 1000000, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))
            |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]))                                                         
```

処理時間を計測します。上記sqlの``set showplan_text on;``と``set showplan_text off;``を外して実行します。
ざっくり7秒です。

```
index有り:   CPU time = 6175 ms,  elapsed time = 6557 ms.
index無し:   CPU time = 6217 ms,  elapsed time = 6719 ms.
```

</div></details>

# オフセット法による最後11行

この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文は``Oracle``と同じです。``MySQL``や``PostgreSQL``とちょっと違います。

<details><summary>オフセット法による最後11行</summary><div>

```sql:オフセット法による最後11行

  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  offset 999989 rows fetch first 11 rows only
  ;
  go

order_item_id product_id order_qty unit_prc  shipping_address_desc order_date
------------- ---------- --------- --------- --------------------- ----------------
       990913          5       325   544.387 D9B563CB0D                  2019-01-01＜＝ここをキーにする
       991307          5       722  4875.844 9401D8E665                  2019-01-01
       992318          5       782  2300.518 EF135253B0                  2019-01-01
       992549          5       583  2651.842 6138B01A85                  2019-01-01
       993405          5       934  1726.319 41759C5AAC                  2019-01-01
       997085          5       204  7942.090 C13635D25A                  2019-01-01
       997471          5       428  5180.960 2630ED13D4                  2019-01-01
       997898          5       634  9250.947 C458DAE46D                  2019-01-01
       999406          5        80  1191.171 23B6092077                  2019-01-01
       999680          5       354  2146.997 74F0158945                  2019-01-01
       999961          5       551  7962.957 B5D3CE925C                  2019-01-01

(11 rows affected)

 SQL Server Execution Times:
   CPU time = 6374 ms,  elapsed time = 7000 ms.
```

</div></details>


# シーク法による最後の10行

実行計画を取得します。
本体のsql文は``Oracle``と同じです。``MySQL``や``PostgreSQL``とちょっと違います。

<details><summary>シーク法による最後の10行</summary><div>

```sql:シーク法による最後の10行
set showplan_text on;
go
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( cast('2019-01-01' as date) > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 = product_id and 990913 < order_item_id )
  order by
    order_date desc,
    product_id,
    order_item_id
  offset 0 rows fetch first 10 rows only
  ;
  go
set showplan_text off;
go
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

オフセット法と比較すると違いが顕著です。index有りの方は``Index Seek``と``[idx1_t_order_item]``となっています。たぶん、インデックスが効果を出しています。
index無しの方はよくわからないですが、``Sort(TOP 10,``となっているので効果がありそうです(オフセット法は``Sort(TOP 1000000, ``となっている)。

```sql:シーク法による最後の10行(index有り)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(TOP 10, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))                                                                                                                                                                                                                                                                                  
       |--Nested Loops(Inner Join, OUTER REFERENCES:([testdb].[dbo].[t_order_item].[order_item_id], [Expr1002]) OPTIMIZED WITH UNORDERED PREFETCH)                                                                                                                                                                                                                                                                                                                
            |--Index Seek(OBJECT:([testdb].[dbo].[t_order_item].[idx1_t_order_item]), SEEK:([testdb].[dbo].[t_order_item].[order_date] < '2019-01-01' OR [testdb].[dbo].[t_order_item].[order_date]='2019-01-01' AND [testdb].[dbo].[t_order_item].[product_id]=(5.) AND [testdb].[dbo].[t_order_item].[order_item_id] > (990913.) OR [testdb].[dbo].[t_order_item].[order_date]='2019-01-01' AND [testdb].[dbo].[t_order_item].[product_id] > (5.)) ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]), SEEK:([testdb].[dbo].[t_order_item].[order_item_id]=[testdb].[dbo].[t_order_item].[order_item_id]) LOOKUP ORDERED FORWARD)                                                                                                                                                                                                                                          
```

```sql:シーク法による最後の10行(index無し)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(TOP 10, ORDER BY:([testdb].[dbo].[t_order_item].[order_date] DESC, [testdb].[dbo].[t_order_item].[product_id] ASC, [testdb].[dbo].[t_order_item].[order_item_id] ASC))                                                                                                                            
       |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item]), WHERE:([testdb].[dbo].[t_order_item].[order_date]<'2019-01-01' OR '2019-01-01'=[testdb].[dbo].[t_order_item].[order_date] AND [testdb].[dbo].[t_order_item].[product_id]>(5.) OR '2019-01-01'=[testdb].[dbo].[t_order_item].[order_date] AND (5.)=[testdb].[dbo].[t_order_item].[product_id] AND [testdb].[dbo].[t_order_item].[order_item_id]>(990913.)))
```

処理時間を計測します。上記sqlの``set showplan_text on;``と``set showplan_text off;``を外して実行します。
ざっくり1秒かかってないです。index有りはCPU timeも``0 ms``となかか高速です。

```
index有り:   CPU time = 0 ms,  elapsed time = 0 ms.
index無し:   CPU time = 678 ms,  elapsed time = 691 ms.
```

</div></details>

# シーク法でのページジャンプ

## 全てのページの境界を求める

実行計画を取得します。

<details><summary>全てのページの境界を求める</summary><div>


```sql:全てのページの境界を求める
set showplan_text on;
go
  select
    x.*,
    row_number() over(
      order by
        order_date desc, product_id, order_item_id
    ) + 1 page_number
  from
    ( select
        -- 10行でページング
        case ((row_number() over(order by order_date desc, product_id, order_item_id)) % 10)
          when 0
          then 1
          else 0
        end page_boundary,
        t.*
      from
      t_order_item t
    ) x
  where
    x.page_boundary = 1
  ;
  go
set showplan_text off;
go
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

実行計画がよくわからない。。。

```sql:index有り
-----------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1004]=[Expr1003]+(1)))
       |--Sequence Project(DEFINE:([Expr1003]=row_number))
            |--Segment
                 |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [Expr1001]%(10)=(0) THEN (1) ELSE (0) END))
                      |--Filter(WHERE:(CASE WHEN [Expr1001]%(10)=(0) THEN (1) ELSE (0) END=(1)))
                           |--Sequence Project(DEFINE:([Expr1001]=row_number))
                                |--Segment
                                     |--Sort(ORDER BY:([t].[order_date] DESC, [t].[product_id] ASC, [t].[order_item_id] ASC))
                                          |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item] AS [t]))
```

```sql:index無し
-----------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1004]=[Expr1003]+(1)))
       |--Sequence Project(DEFINE:([Expr1003]=row_number))
            |--Segment
                 |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN [Expr1001]%(10)=(0) THEN (1) ELSE (0) END))
                      |--Filter(WHERE:(CASE WHEN [Expr1001]%(10)=(0) THEN (1) ELSE (0) END=(1)))
                           |--Sequence Project(DEFINE:([Expr1001]=row_number))
                                |--Segment
                                     |--Sort(ORDER BY:([t].[order_date] DESC, [t].[product_id] ASC, [t].[order_item_id] ASC))
                                          |--Clustered Index Scan(OBJECT:([testdb].[dbo].[t_order_item].[pk_t_order_item] AS [t]))
```

index有無は処理時間の差はわずかです。elapsが約9秒ですが、実際に10万行表示は30秒以上かかってます(ストップウォッチで図るの面倒)。
恐らく標準出力に書き出し終わったのが9秒で、画面描画やりきるのに30秒かかっているのだと思います。
この辺、データベースの違いが出ていて面白いです。

```
index有り:   CPU time = 6943 ms,  elapsed time = 8406 ms.
index無し:   CPU time = 7091 ms,  elapsed time = 8559 ms.
```

</div></details>

# DB2

実行計画取得の仕方を参考にしたURL:[https://www.slideshare.net/simosako/db2-30672626](https://www.slideshare.net/simosako/db2-30672626 "https://www.slideshare.net/simosako/db2-30672626")


## EXPLAIN表の作成

一度コネクトしてから抜けて作る。

```sql:
connect to testdb;
quit;
```

コンテナ内部のbashコマンドプロンプトで次のコマンドを実行します。

```bash:コンテナ内部
db2 -tvf ~/sqllib/misc/EXPLAIN.DDL
```

DB2のCUIツールを起動するときは、``-t``オプションで起動すること。
こうしないと複数行に渡るsql文をコピペで貼り付けられないので。
(改行文字でsqlを実行してしまうため)

```bash:コンテナ内部
db2 -t
```

``testdb``に接続します。

```sql:
connect to testdb;
```

## 最初の10行を取得

実行計画を取得します。
本体のsql文は``MySQL``と同じです。``limit``と``offset``の組み合わせです。

<details><summary>最初の10行を取得</summary><div>

```sql:最初の10行を取得
set current explain mode explain;
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 0
  ;
set current explain mode no;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

DB2では、実行計画を見やすく整形するツールがあるので、これを利用します。
コンテナ内部の``bash``コマンドプロンプトで``db2exfmt``コマンドを使います。

```bash:コンテナ内部
db2exfmt -1 -d testdb -o explain-first10-index.txt
db2exfmt -1 -d testdb -o explain-first10-noindex.txt
```

情報がありすぎなので抜粋しています。見方がわからないですが、この後のシーク法の実行計画と比べてCostが大きいのでフルスキャンなんだと思います。

```sql:index有り
Access Plan:
-----------
        Total Cost:             11466.4
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
       10
     TBSCAN
     (   2)
     11466.4
      11242
       |
       10
     SORT
     (   3)
     11466.4
      11242
       |
      1e+06
     TBSCAN
     (   4)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1
```

```sql:index無し
Access Plan:
-----------
        Total Cost:             11466.4
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
       10
     TBSCAN
     (   2)
     11466.4
      11242
       |
       10
     SORT
     (   3)
     11466.4
      11242
       |
      1e+06
     TBSCAN
     (   4)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1
```

処理時間を計測します。

DB2のCUIツールはelaps時間を表示してくれないので、``db2batch``コマンドでsqlを実行してelaps時間を取得します。
(メンドクサイ)
上記sqlの本体部分を``top10.sql``として切り出し、``db2batch``コマンドで実行してelaps時間を取得します。

```bash:コンテナ内部
db2batch -d testdb -f top10.sql
```

index有無で若干差が出ていますが、ざっくり1秒くらいです。

```
index有り:* Elapsed Time is:       0.805196 seconds
index無し:* Elapsed Time is:       0.805790 seconds
```

</div></details>

## オフセット法による最後10行を取得

実行計画を取得します。
本体のsql文は``MySQL``と同じです。``limit``と``offset``の組み合わせです。

<details><summary>オフセット法による最後10行を取得</summary><div>

```sql:オフセット法による最後10行
set current explain mode explain;
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 999990
  ;
set current explain mode no;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

実行計画を整形します。

```bash:コンテナ内部
db2exfmt -1 -d testdb -o explain-offset-last10-index.txt
db2exfmt -1 -d testdb -o explain-offset-last10-noindex.txt
```

実行計画の見方が。。。


```sql:オフセット法による最後の10行(index有り)

Access Plan:
-----------
        Total Cost:             15595.4
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
     333333
     FILTER
     (   2)
     15595.4
      11242
       |
      1e+06
     TBSCAN
     (   3)
     14431.3
      11242
       |
      1e+06
     SORT
     (   4)
     14431.3
      11242
       |
      1e+06
     TBSCAN
     (   5)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1
```

```sql:オフセット法による最後の10行(index無し)
Access Plan:
-----------
        Total Cost:             15595.4
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
     333333
     FILTER
     (   2)
     15595.4
      11242
       |
      1e+06
     TBSCAN
     (   3)
     14431.3
      11242
       |
      1e+06
     SORT
     (   4)
     14431.3
      11242
       |
      1e+06
     TBSCAN
     (   5)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1
```

処理時間を計測します。

```bash:コンテナ内部
db2batch -d testdb -f offset.sql
```

index有無で若干差が出ていますが、ざっくり9秒くらいです。

```
index有り:* Elapsed Time is:       8.287903 seconds
index無し:* Elapsed Time is:       8.510650 seconds
```

</div></details>

## オフセット法による最後11行

この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文は``MySQL``と同じです。``limit``と``offset``の組み合わせです。

<details><summary>オフセット法による最後11行</summary><div>

```sql:オフセット法による最後11行
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 11 offset 999989
  ;

ORDER_ITEM_ID PRODUCT_ID ORDER_QTY UNIT_PRC  SHIPPING_ADDRESS_DESC ORDER_DATE
------------- ---------- --------- --------- --------------------- ----------
      990156.         5.      567.  8170.409 174AC4F98F            01/01/2019＜＝ここをキーにする
      991238.         5.        1.  4067.441 C27DC23DDA            01/01/2019
      992174.         5.       22.  8059.180 B59D2EE94D            01/01/2019
      992914.         5.      914.  5385.903 76DF69D2FB            01/01/2019
      993365.         5.      579.  1337.713 FE4BAB3543            01/01/2019
      994680.         5.      381.  2501.721 CA253B2F37            01/01/2019
      995542.         5.      755.  5127.181 2BAA3FEC3A            01/01/2019
      996232.         5.      539.  7789.173 618712FFBE            01/01/2019
      996577.         5.      717.  7552.965 B1B09F9C58            01/01/2019
      996581.         5.      644.  9716.192 171F2F7460            01/01/2019
      996726.         5.      402.  9129.561 80BFC8A14B            01/01/2019

  11 record(s) selected.
```

</div></details>

# シーク法による最後の10行

実行計画を取得します。
本体のsql文は``MySQL``と同じです。

<details><summary>シーク法による最後の10行</summary><div>

```sql:シーク法による最後の10行
set current explain mode explain;
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( cast('2019-01-01' as date) > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( cast('2019-01-01' as date) = order_date and 5 = product_id and 990156 < order_item_id )
  order by
    order_date desc,
    product_id,
    order_item_id
  limit 10
  ;
set current explain mode no;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

実行計画を整形します。

```bash:コンテナ内部
db2exfmt -1 -d testdb -o explain-seek-last10-index.txt
db2exfmt -1 -d testdb -o explain-seek-last10-noindex.txt
```

オフセット法と比べると、Costが段違いに少ないです。``IXSCAN``といったキーワードが出てるので、インデックスが効いてそう。

```sql:シーク法による最後の10行(index有り)
Access Plan:
-----------
        Total Cost:             198.045
        Query Degree:           1


             Rows
            RETURN
            (   1)
             Cost
              I/O
              |
              10
            TBSCAN
            (   2)
            198.045
            53.6559
              |
              10
            SORT
            (   3)
            198.044
            53.6559
              |
            14.734
            FETCH
            (   4)
            198.035
            53.6559
          /---+----\
      14.734        1e+06
      IXSCAN   TABLE: DB2INST1
      (   5)    T_ORDER_ITEM
      98.4011        Q1
      38.9466
        |
       1e+06
  INDEX: DB2INST1
 IDX1_T_ORDER_ITEM
        Q1

```

```sql:シーク法による最後の10行(index無し)
Access Plan:
-----------
        Total Cost:             12883.9
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
       10
     TBSCAN
     (   2)
     12883.9
      11242
       |
       10
     SORT
     (   3)
     12883.9
      11242
       |
     13.9128
     TBSCAN
     (   4)
     12883.9
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1

```

処理時間を計測します。

```bash:コンテナ内部
db2batch -d testdb -f seek.sql
```

index有無に関係なくオフセット法と比べるとかなり高速です。

```
index有り:* Elapsed Time is:       0.009107 seconds
index無し:* Elapsed Time is:       0.619869 seconds
```

</div></details>

# シーク法でのページジャンプ

## 全てのページの境界を求める

実行計画を取得します。

<details><summary>全てのページの境界を求める</summary><div>

```sql:全てのページの境界を求める
set current explain mode explain;
  select
    x.*,
    row_number() over(
      order by
        order_date desc, product_id, order_item_id
    ) + 1 page_number
  from
    ( select
          -- 10行でページング
          case ((row_number() over(order by order_date desc, product_id, order_item_id)) % 10)
          when 0
          then 1
          else 0
        end page_boundary,
        t.*
      from
        t_order_item t
    ) x
  where
    x.page_boundary = 1
  ;
set current explain mode no;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

実行計画を整形します。

```bash:コンテナ内部
db2exfmt -1 -d testdb -o explain-boundary-index.txt
db2exfmt -1 -d testdb -o explain-boundary-noindex.txt
```

```sql:index有り
Access Plan:
-----------
        Total Cost:             16227.2
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
      40000
     FILTER
     (   2)
     16191.4
      11242
       |
      1e+06
     TBSCAN
     (   3)
     14972.4
      11242
       |
      1e+06
     SORT
     (   4)
     14819.9
      11242
       |
      1e+06
     TBSCAN
     (   5)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1
```

```sql:index無し
Access Plan:
-----------
        Total Cost:             16227.2
        Query Degree:           1


      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
      40000
     FILTER
     (   2)
     16191.4
      11242
       |
      1e+06
     TBSCAN
     (   3)
     14972.4
      11242
       |
      1e+06
     SORT
     (   4)
     14819.9
      11242
       |
      1e+06
     TBSCAN
     (   5)
      10998
      11242
       |
      1e+06
 TABLE: DB2INST1
  T_ORDER_ITEM
       Q1
```

処理時間を取得します。

```bash:コンテナ内部
db2batch -d testdb -f boundary.sql
```

10万行表示し終わるのに40秒くらいかかってます。

```
index有り:* Elapsed Time is:      39.321630 seconds
index無し:* Elapsed Time is:      38.948654 seconds
```

</div></details>


# sqlite

## 処理時間をコンソールに表示

``.headers on``はクエリ結果にカラム名のヘッダーをつけるため。

```sql:
.timer on
.headers on
```
## 最初の10行を取得

実行計画を取得します。
本体のsql文は``MySQL``と同じです。``limit``と``offset``の組み合わせです。

<details><summary>最初の10行を取得</summary><div>

```sql:最初の10行を取得
  explain query plan
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 0
  ;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

```sql:index有り
QUERY PLAN
|--SCAN TABLE t_order_item USING INDEX idx1_t_order_item
`--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
```

```sql:index無し
QUERY PLAN
|--SCAN TABLE t_order_item
`--USE TEMP B-TREE FOR ORDER BY
```

処理時間を計測します。上記sqlの``explain query plan``を外して実行します。
index有無で若干差が出ていますが、ざっくり1秒くらいです。

```
index有り:Run Time: real 0.053 user 0.028097 sys 0.024213
idnex無し:Run Time: real 0.813 user 0.769735 sys 0.041614
```

</div></details>


## オフセット法による最後10行を取得

実行計画を取得します。
本体のsql文は``MySQL``と同じです。``limit``と``offset``の組み合わせです。

<details><summary>オフセット法による最後10行を取得</summary><div>

```sql:オフセット法による最後10行
  explain query plan
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 10 offset 999990
  ;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

実行計画の見方が分からない。

```sql: オフセット法による最後10行(index有り)
QUERY PLAN
|--SCAN TABLE t_order_item USING INDEX idx1_t_order_item
`--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
```

```sql: オフセット法による最後10行(index無し)
QUERY PLAN
|--SCAN TABLE t_order_item
`--USE TEMP B-TREE FOR ORDER BY
```

処理時間を計測します。
index有無で若干差が出ていますが、ざっくり15秒くらいです。

```
index有り:Run Time: real 12.043 user 8.530403 sys 3.503906
index無し:Run Time: real 19.638 user 10.585848 sys 9.024575
```

</div></details>

## オフセット法による最後11行

この後のシーク法では、前ページの最終行をキーにして、次ページを検索しますので、最後の11行を検索して、返ってきた11行の1行目をキーとします。
本体のsql文は``MySQL``と同じです。``limit``と``offset``の組み合わせです。

<details><summary>オフセット法による最後11行</summary><div>

```sql:オフセット法による最後11行
  select
    *
  from
    t_order_item
  order by
    t_order_item.order_date desc,
    t_order_item.product_id,
    t_order_item.order_item_id
  limit 11 offset 999989
  ;

order_item_id|product_id|order_qty|unit_prc|shipping_address_desc|order_date
993272|5|611|7099.066|HELDNF1O87|2019-01-01＜＝ここをキーにする
993288|5|718|4004.409|P60UWIVTPP|2019-01-01
994711|5|146|524.474|P8V2EJMK8N|2019-01-01
995546|5|912|59.209|GVCUXA1RKT|2019-01-01
997147|5|225|8210.519|6RBXR4R5PE|2019-01-01
997653|5|775|181.531|MRMVXY1SEC|2019-01-01
998014|5|593|9775.705|EPNZFR6SOI|2019-01-01
998552|5|288|6715.191|UC27EGLKII|2019-01-01
998808|5|850|2812.261|47K965649X|2019-01-01
999126|5|709|2253.525|WTHR81JDMX|2019-01-01
999748|5|693|7628.87|RKBHOBHJ94|2019-01-01
Run Time: real 13.039 user 9.328710 sys 3.700142
```
</div></details>

# シーク法による最後の10行

実行計画を取得します。
本体のsql文は``MySQL``と同じです。

<details><summary>シーク法による最後の10行</summary><div>

```sql:シーク法による最後の10行
  explain query plan
  select
    *
  from
    t_order_item
  where
    -- 前ページのorder_dateより小さい日付(descなので)なら残す。
    ( date('2019-01-01') > order_date )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも大きい(ascなので)なら残す。
    ( date('2019-01-01') = order_date and 5 < product_id )
    or
    -- 前ページのorder_dateと同じ日付で、前ページのproduct_idも同じなら、前ページのorder_item_idより大きい(ascなので)なら残す。
    ( date('2019-01-01') = order_date and 5 = product_id and 993272 < order_item_id)
  order by
    order_date desc,
    product_id,
    order_item_id
  limit 10
  ;
```

</div></details>

<details><summary>実行計画と処理時間</summary><div>

オフセット法と比較すると違いが顕著です。``USING INDEX idx1_t_order_item``とインデックス効果が出てそうなキーワードが出ています。

```sql:シーク法による最後の10行(index有り)
QUERY PLAN
|--MULTI-INDEX OR
|  |--INDEX 1
|  |  `--SEARCH TABLE t_order_item USING INDEX idx1_t_order_item (order_date<?)
|  |--INDEX 2
|  |  `--SEARCH TABLE t_order_item USING INDEX idx1_t_order_item (order_date=? AND product_id>?)
|  `--INDEX 3
|     `--SEARCH TABLE t_order_item USING INDEX idx1_t_order_item (order_date=? AND product_id=? AND order_item_id>?)
`--USE TEMP B-TREE FOR ORDER BY
```

```sql:シーク法による最後の10行(index無し)
QUERY PLAN
|--SCAN TABLE t_order_item
`--USE TEMP B-TREE FOR ORDER BY
```

オフセット法が15秒くらいに対して、1秒くらいなので、高速です。

```
index有り:Run Time: real 0.000 user 0.000422 sys 0.000105
index無し:Run Time: real 1.883 user 1.854988 sys 0.026341
```

</div></details>

# シーク法でのページジャンプ

## 全てのページの境界を求める

実行計画を取得します。

<details><summary>全てのページの境界を求める</summary><div>

```sql:全てのページの境界を求める
  explain query plan
  select
  x.*,
  row_number() over(
    order by
      order_date desc, product_id, order_item_id
  ) + 1 page_number
  from
  ( select
      case (row_number() over(
        order by
          order_date desc, product_id, order_item_id
      ) % 10)  -- 10行でページング
        when 0
        then 1
        else 0
      end page_boundary,
      t.*
    from
      t_order_item t
    order by
      order_date desc,
      product_id,
      order_item_id
  ) x
  where
  x.page_boundary = 1
  ;
```
</div></details>

<details><summary>実行計画と処理時間</summary><div>

```sql:index有り
QUERY PLAN
|--CO-ROUTINE 3
|  |--CO-ROUTINE 1
|  |  |--CO-ROUTINE 4
|  |  |  |--SCAN TABLE t_order_item AS t USING INDEX idx1_t_order_item
|  |  |  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
|  |  `--SCAN SUBQUERY 4
|  |--SCAN SUBQUERY 1 AS x
|  `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 3
```

```sql:index無し
QUERY PLAN
|--CO-ROUTINE 3
|  |--CO-ROUTINE 1
|  |  |--CO-ROUTINE 4
|  |  |  |--SCAN TABLE t_order_item AS t
|  |  |  `--USE TEMP B-TREE FOR ORDER BY
|  |  `--SCAN SUBQUERY 4
|  |--SCAN SUBQUERY 1 AS x
|  `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 3
```

index有無は処理時間の差はわずかです。実際に10万行表示するのに42秒くらいかかりました。

```
index有り:Run Time: real 42.422 user 14.213993 sys 3.987254
index無し:Run Time: real 41.138 user 16.528704 sys 1.048000
```

</div></details>