2008年1月10日木曜日

オラクルで行数指定のSQL

去年から何ヶ月も更新が滞っていましたが
特に忙しいわけでもなく、ただネタがなかっただけです^^;
ということで、久しぶりに苦労したのでメモ。
例えば10万件あるデータのうち最初にひっかかった500件
だけ表示したいような場合にどうしたもんかといろいろ
としらべてわかったこと。


select code,
value
from (
select code,
value
from table
where code like '%10%'
order by code
)
where rownum <= 500

とまぁわかってしまえばいたって
簡単なのですが、ここで曲者だったのが
ソートですね。
これがなければわざわざこんなことしなくても
よかったのですが、
ソート後の先頭から500件だったので
改めてrownumを取得する必要があるということですね。

私の場合は上記でことすんだのですが
これを調べてる最中にとても勉強になることが
オラクルのページにあったので転機。

以下
-----------------------------------------------------
/*オブジェクト型を作成*/
CREATE OR REPLACE TYPE MEMOBJECT AS OBJECT (
MEMBER_CD NUMBER,
MEMBER_NAME VARCHAR2(50)
);

/*オブジェクト型を作成*/
CREATE OR REPLACE TYPE MEMTYPE AS TABLE OF MEMOBJECT;

/*開始番号と終了番号でデータを返すプロシージャ*/
PROCEDURE LISTMEMBER (
ST IN NUMBER,
EN IN NUMBER,
MEM OUT MEMTYPE
)
AS
BEGIN
BEGIN
SELECT CAST(MULTISET(
SELECT MEMBER_CD,MEMBER_NAME
FROM MEMBER A
WHERE (SELECT COUNT(B.MEMBER_CD)+1 FROM MEMBER B
WHERE B.MEMBER_CD > A.MEMBER_CD
AND ROWNUM<=EN) BETWEEN ST AND EN
AND ROWNUM <= (EN-ST)+1
ORDER BY MEMBER_CD DESC ) AS MEMTYPE) INTO MEM FROM DUAL;
END;
END;
-----------------------------------------------------

こんな風にやれば100万件のデータがあっても 指定したページ単位で高速に取得できるそうな・・・。 正直、理解できてないですねorz。 さらに詳しくはここで議論されてます。いやすごいとしかいいようがない http://otn.oracle.co.jp/cgi-bin/non/msgview_r.cgi?communityid=otn-901233&bbsid=1&no=8857&view=9

0 件のコメント: