hatenob

プログラムって分からないことだらけ

Oracle 12c 行数制限をつける

Native SQL Support for Query Row Limits and Row Offsets

Oracle 12cでは、取得する行数をこれまでより直感的に制限できるようになりました。
と言っても、ANSI SQL標準のサポートてことなんだけれども。
とりあえず21件分のデータを入れた状態でちょっと見てみたいと思います。

create table emp (
  id number generated always as identity primary key,
  name varchar2(20),
  created timestamp default systimestamp
);

insert into emp(name) values('emp001');
insert into emp(name) values('emp002');
insert into emp(name) values('emp003');
insert into emp(name) values('emp004');
insert into emp(name) values('emp005');
insert into emp(name) values('emp006');
insert into emp(name) values('emp007');
insert into emp(name) values('emp008');
insert into emp(name) values('emp009');
insert into emp(name) values('emp010');
insert into emp(name) values('emp011');
insert into emp(name) values('emp012');
insert into emp(name) values('emp013');
insert into emp(name) values('emp014');
insert into emp(name) values('emp015');
insert into emp(name) values('emp016');
insert into emp(name) values('emp017');
insert into emp(name) values('emp018');
insert into emp(name) values('emp019');
insert into emp(name) values('emp020');
insert into emp(name) values('emp021');
commit;

今までの行数制限

ダメな例

行数を絞ると言えば、ROWNUMでした。
でも疑似列なので少し癖があって、

select row_number() over(order by id desc) no
 , id
 , name
 , to_char(created,'YYYY-MM-DD HH24:MI:SS')
from emp where rownum <= 5 order by id desc;

        NO         ID NAME                 TO_CHAR(CREATED,'YY
---------- ---------- -------------------- -------------------
         1          5 emp005               2013-10-24 23:21:37
         2          4 emp004               2013-10-24 23:21:37
         3          3 emp003               2013-10-24 23:21:37
         4          2 emp002               2013-10-24 23:21:37
         5          1 emp001               2013-10-24 23:21:37

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 2297916432

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     5 |   190 |     2   (0)| 00:00:01 |
|   1 |  WINDOW SORT        |      |     5 |   190 |     2   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| EMP  |    21 |   798 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=5)

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

ID降順でソートして最初の5行を取りたいと思っても、これだと先頭の5行が降順で出てしまってます。
5行に絞ってからソートをしているからですね。

OKな例

ソートをしてから5行に絞るようにするために、手っ取り早くインラインビューにしてしまいます。

select * from (
 select row_number() over(order by id desc) no
  , id
  , name
  ,to_char(created,'YYYY-MM-DD HH24:MI:SS')
 from emp
 order by id desc
) where rownum <= 5;

        NO         ID NAME                 TO_CHAR(CREATED,'YY
---------- ---------- -------------------- -------------------
         1         21 emp021               2013-10-24 23:21:37
         2         20 emp020               2013-10-24 23:21:37
         3         19 emp019               2013-10-24 23:21:37
         4         18 emp018               2013-10-24 23:21:37
         5         17 emp017               2013-10-24 23:21:37

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 588356519

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     5 |   245 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY       |      |       |       |            |          |
|   2 |   VIEW               |      |    21 |  1029 |     2   (0)| 00:00:01 |
|   3 |    WINDOW SORT       |      |    21 |   798 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    21 |   798 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)

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

無事にソートをしてから行数を絞ることができました。

FETCHとOFFSETを使った制限

新機能ではFETCHとOFFSETを使って取得します。

最初の○行

結果の最初の5行を取得します。

select
 row_number() over(order by id desc) no
 , id
 , name
 , to_char(created,'YYYY-MM-DD HH24:MI:SS')
from emp
order by id desc
fetch first 5 rows only;

        NO         ID NAME                 TO_CHAR(CREATED,'YY
---------- ---------- -------------------- -------------------
         1         21 emp021               2013-10-24 23:21:37
         2         20 emp020               2013-10-24 23:21:37
         3         19 emp019               2013-10-24 23:21:37
         4         18 emp018               2013-10-24 23:21:37
         5         17 emp017               2013-10-24 23:21:37

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 3291446077

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    21 |  1575 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |      |    21 |  1575 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |    21 |   798 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMP  |    21 |   798 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ID") DESC
              )<=5)

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

最初の5行を取得しなさいということで、直感的に書けています。
実行計画を見てみると、何やら色々とやっておりまして、特にフィルタのところでは内部的にサブクエリ化をしたり、内部関数を読んだりしているようです。
コストも少し増えてる。

○行目から○行目

5行目から次の5行、つまり10行目までを取得します。

select
 row_number() over(order by id desc) no
 , id
 , name
 , to_char(created,'YYYY-MM-DD HH24:MI:SS')
from emp
order by id desc
offset 5 rows fetch next 5 rows only;

        NO         ID NAME                 TO_CHAR(CREATED,'YY
---------- ---------- -------------------- -------------------
         6         16 emp016               2013-10-24 23:21:37
         7         15 emp015               2013-10-24 23:21:37
         8         14 emp014               2013-10-24 23:21:37
         9         13 emp013               2013-10-24 23:21:37
        10         12 emp012               2013-10-24 23:21:37


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 3291446077

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |    21 |  1575 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |      |    21 |  1575 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |    21 |   798 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMP  |    21 |   798 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN
              (5>=0) THEN 5 ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownum
              ber">5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ID") DESC
              )<=CASE  WHEN (5>=0) THEN 5 ELSE 0 END +5)

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

無事に5行目から10行目を取れました。
なんか、フィルタ部分はさらに複雑になっている。。
CASEとかもでてきているし。
ROWNUMでインラインビュー使ったほうが、内部処理としてはシンプルになりそう。

○パーセントを取得

行数ではなく、取得したレコード集合のうち、何パーセントを取得ということができるようです。
5パーセント(21行なので、2行)取得してみます。

select
 row_number() over(order by id desc) no
 , id
 , name
 , to_char(created,'YYYY-MM-DD HH24:MI:SS')
from emp order by id desc
fetch first 5 percent rows only;

        NO         ID NAME                 TO_CHAR(CREATED,'YY
---------- ---------- -------------------- -------------------
         1         21 emp021               2013-10-24 23:21:37
         2         20 emp020               2013-10-24 23:21:37


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 4130734685

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    21 |  1848 |     3   (0)| 00:00:01 |
|*  1 |  VIEW               |      |    21 |  1848 |     3   (0)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    21 |   798 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    21 |   798 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$
              _subquery$_002"."rowlimit_$$_total"*5/100))

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

無事に2行だけ取れました。
フィルタを見ると、トータル×5/100で行数を計算している雰囲気。

まとめ

今までより直感的で書き方はシンプルに思える。
ただし内部処理は少し複雑になっている模様。

行数制限は、一度に画面に一覧できない大量のデータを表示する画面で、ページングをする時なんかに使われることが多いです。
となると、対象データが多い状態できちんと性能を測る必要がありますね。