2006-11-28

OracleではLimit句が使えない

ページングでSelect結果の一部のデータをしたいとき、PostgreSQLやMySQLで使用できるLimit句はとても便利。たとえばSelect結果の10から14番目までを取得するには
select * from emp where Limit 10,5
でもOracleではLimit句は使えない...

ということでOracleではROWNUMを使用してやってみる。(Oracle10g Windows版で検証)
ところがこのROWNUMは一筋縄ではいかないのであった。

Order By 句を使って並べ替えをすると、ROWNUMが...
select rownum rn
,t.住所コード
,t.市区町村名
,t.住所1
,t.住所2
from tb_住所マスタ t
order by 住所2;
結果
RN住所コード市区町村名住所1住所2
11753620300230056小松島市坂野町か里や開
21873620300230068小松島市坂野町さんた
31903620300230071小松島市坂野町のより
41573620300230038小松島市坂野町シヅ田
51423620300230023小松島市坂野町シャウ内
6763620300200002小松島市和田津開町ヒガシ
71653620300230046小松島市坂野町ミぞ口
82043620300240001小松島市間新田町ヤケ木
92343620300010008小松島市小松島町井利ノ口
103363620300100015小松島市日開野町井理守


しょーがないので副問い合わせを使用するとなんとか並ぶ
select rownum
,s.*
from (select rownum rn,
t.住所コード
,t.市区町村名
,t.住所1
,t.住所2
from tb_住所マスタ t
order by 住所2
) s
結果
   ROWNUMRN住所コード市区町村名住所1住所2
111753620300230056小松島市坂野町か里や開
221873620300230068小松島市坂野町さんた
331903620300230071小松島市坂野町のより
441573620300230038小松島市坂野町シヅ田
551423620300230023小松島市坂野町シャウ内
66763620300200002小松島市和田津開町ヒガシ
771653620300230046小松島市坂野町ミぞ口
882043620300240001小松島市間新田町ヤケ木
992343620300010008小松島市小松島町井利ノ口
10103363620300100015小松島市日開野町井理守


で10番目から14番目を取得しよう!と意気揚々であったのだが
select rownum
,s.*
from (select rownum rn,
t.住所コード
,t.市区町村名
,t.住所1
,t.住所2
from tb_住所マスタ t
order by 住所
) s
where rownum >= 10 and rownum < 15
結果
   ROWNUMRN住所コード市区町村名住所1住所2

レコードが返ってこない!なんで!

ROWNUMというのはそもそも結果セットに対して順番につけている番号だからして、それをさらに選択はできるのはおかしいということらしい。
そのためOracle 7 まではこれでよかったのだが、Oracle8からはダメになったそうだ。orz

副問い合わせで取得したROWNUMに別名をつけてやっと選択できた。
select * from 
(
select rownum rn2
,s.*
from (select rownum rn,
t.住所コード
,t.市区町村名
,t.住所1
,t.住所2
from tb_住所マスタ t
order by 住所2
) s
) a
where a.rn2 >= 10 and a.rn2 < 15
結果
   RN2RN住所コード市区町村名住所1住所2
1103363620300100015小松島市日開野町井理守
2112653620300040007小松島市堀川町井理守
3122843620300070020小松島市金磯町一番町
413773620300200003小松島市和田津開町鵜ノ松
5143963620300140003小松島市江田町姥ヶ懐


さらに改良。minus句を使用する方がパフォーマンスがよいらしいです。
select rownum
,s.*
from (select rownum rn,
t.住所コード
,t.市区町村名
,t.住所1
,t.住所2
from tb_住所マスタ t
order by 住所2
) s
where rownum <= 14
minus
select rownum
,s.*
from (select rownum rn,
t.住所コード
,t.市区町村名
,t.住所1
,t.住所2
from tb_住所マスタ t
order by 住所2
) s
where rownum < 10;


Select結果の順番なんて所詮水物ではあるのだが、やっぱり必要なのですよLimit句。
Oracleさん、ちょっとだけ考えてくれないかしら。

via Works - 逆引きSQL比較 - ROWNUM を使用した擬似スクロール・カーソル
コメントを投稿