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で行数を計算している雰囲気。
まとめ
今までより直感的で書き方はシンプルに思える。
ただし内部処理は少し複雑になっている模様。
行数制限は、一度に画面に一覧できない大量のデータを表示する画面で、ページングをする時なんかに使われることが多いです。
となると、対象データが多い状態できちんと性能を測る必要がありますね。