最近の20件を取得するSQL

質問

現在PostgreSQL上にあるDBをOracleに移行しようとしていますが、Oracleに詳しくないためご教示いただきたく存じます。
　2000万行ほどの売り上げテーブルがあります。
　そこである商品やジャンルで絞り込みをおこない（または全く絞り込みをせず）最近20件を検索したいと思っています。（正確に言うと、次ページ、前ページなどでその前後を取得する機能も必要です。）

　で、この最近の20件、というところで躓いています。

　いろいろと検索してみると

select * from (
select 検索項目... from sold
where 絞り込み条件
order by sold_date desc
)
where rownum<20;

なんていう例が載っていたりするのですが、これだと絞り込みがない場合に内側のselect文で2000万件なめてしまうので論外です。（試しにやってみたら30秒くらいかかりました）
　別段レアなケースでもなく、こういう要件はとても一般的だと思うのですが、Oracleではどのように実装するのが一般的なのでしょうか。
　よろしくお願いいたします。

回答No.5

nora1962 · ベストアンサー

set autotrace on;

select /*+ index( sold sold_dateについて作成した降順索引 ) */
sold.*
from (
select 検索項目... from sold
order by sold_date desc
)
where rownum<20;

を実行してみてください。通常ならば、ORACLEがどのようにデータを取得しているかが示されます。
ここで、「INDEX FULL SCAN DESCENDING」と出れば、降順索引が使われているという証拠です。
これならば、索引順にデータを取得し20件でSELECT文は終了します。
しつこいようですが、Oracleは通常の「Create INDEX」は昇順で作成され、降順ソートには使われません。索引が降順「CREATE INDEX テーブル名 （ 項目名 desc）」で作成されているか確認してください。

補足

試す前に質問なのですが、内側のselect文、
select 検索項目... from sold
order by sold_date desc
は20件では終わらず、2000万件なめますよね？

回答No.11

mitoneko

No.1です。
　しばらく、静かに、やりとりを拝見させていただきました。
　この文章は、私見ですのであしからず。

　質問の例示に上げてあるＳＱＬも、私の例示したＳＱＬも、オプティマイザーに与えられた情報だけでは最適化しきれないのかもしれませんね。
　最初の２０件だけ検索してくれれば良いの。だから、最初の２０件さえ解れば「後のデータは、ソートも何もする必要が無いの」と言いたいわけですが、
　後段の部分がオプティマイザーに伝わっているかどうかです。
　質問の方のＳＱＬだと、まずソートしろと言われた。だから、２０００万件のソートをした。当然、ソートするためには、最低限インデックスのフルスキャンが必要。その結果から、rownum疑似列が２０以下のデータだけ出力・・・これなら、当然、全件検索になりますね。（ｒｏｗｎｕｍにインデックスはあるはずがないですからね。）
　私の例示の方もそうです。まず、内部結合しろと言われたから全部やった。その結果の中で上位２０件を探せと言われたから探した。とやられると、２０００万＊２０００万の全件結合になってしまいます。こんなの確かにやってられない。

　両方とも、「上位２０件」と言うのが、キーで、この知識があれば、人間なら別の最適化計画を作ります。rownumを使った方なら、降順インデックスの最初の２０件だけ見れば良し。データも当然これだけ見れば後は不要。
　私の例示の方も、この知識があれば、アルゴリズム上は全件結合する必要は無いです。
　インデックスが昇順なら下位になるだけで実害はないはず・・・なんですが・・・・

　ｔｏｐ＊＊　などの独自構文を持っているＤＢＭＳは、この知識をフル活用しているような気がします。oracleだとこのような構文無しで間接的に指定するから、最適化しきれないのかも・・・

　この推論で終わりなら、解決手段がなくなってしまいます。

　ただ、次善の策で、パーテーションを利用していると言うことなので、レンジパーテーションインデックスがうまく使えるようにすると、多大な効果がありそうな気がします。
　表示するのが２０件だろうが１００件だろうが、人間が操作してページめくりをする限り、１００ページもめくる気がする人はいないでしょう。であれば、例えば、sold_dateの年を指定する欄を強制にしてデータを最初から限定してしまうと、パテーションインデックスの効果がフル活用できるように感じます。(つまり、where句には必ず、sold_date between '****-1-1' and '****-12-31'　が付くわけです。）
　当然、この例で、年と書いた単位を月にするか日にするか、はたまた四半期にするかは、利用用途と効率の板挟みになりますから、考える必要がありますけど。
　なんだか、逃げのようになって酷く心苦しいところはありますが・・・・

お礼

ありがとうございます。
　確かにおっしゃるとおりだと思います。
　個人的には、ちょっと調べた段階で「これはまともにはできないんじゃないか」という気がしていました。
　で、本当に知りたかったのはそれができるかできないかではなくて（もちろんOracleの「中の人」がすごく賢くて、問題なくインデックスだけ見てくれればラッキーですが、その可能性は低いと思っていたので）、「みんなはどうしてるんだろう？」というところだったんです。なにぶんにも「最近の20件を表示する」なんて要件はあまりにもありがちなので、経験のある方は無限にいらっしゃるはずなので。
　ただ、そもそもできないことを無理矢理やっているとすればそれが美しい方法であるわけはなく、ここで俺様がご教示してやろう、というような方は全然無限ではなかった、ということなのかもしれません。私としては「普通はこうする」というのを知りたかったのですが、そういうのもあまりなくて、「自分はこうしてるけど」くらいのレベルでしか一般化されていない問題なのかもしれません。
　今のところ方針としては、SQL一発、っていうのは諦めて、ある特定期間をbetweenで検索して、足りなかったら過去にさかのぼってbetweenで検索して、、、、というのをアプリ側かストアド内で繰り返すか、というようなことを考えております。
　あと、ここ以外では人づてでOracleに死ぬほど詳しいという人に訊いてもらっているので、もしかしたら何かわかるかもしれません。
　もし何かいい方法がわかったら補足したいと思います。

補足

結局わかったことは、「インデックスだけを見に行くはず。検証してみたけどインデックスしか見に行っていない。でもそうならない理由はよくわからない」ということでした。
実際に似たようなテーブルを作成して実験してもらったのですが、送られてきた実行計画を見てみると、確かにテーブルは見に行かずにインデックスだけ見ていました。
私の方でやった作業を伝えたのですが、表の作り方もインデックスの張り方も特に問題なし。もしかしたら最新のパッチを当てて検証しなおした方がいいかも、とのことでした。
というわけで、結論としては「できる（はず）」のようです。
結局何が悪いのかわからないままですが、とにかく私の環境のせいのようで、そのために長々とおつきあいさせることになってしまって心苦しく思っております。
非常に勉強になりました。ありがとうございました。

回答No.10

nora1962

> 疑うようで申し訳ないのですが、昇順で作られたインデックスが降順検索で使われないというのは本当でしょうか？

検証してみました。確かに降順検索でも使用するみたいですね。
私の情報の元はOracle8.1の新機能で「CREATE INDEX DESC」が実現された時のものなので、バージョンアップによって動作が変わったのかもしれません。
誤解を与えて申し訳ありません。

回答No.9

nora1962

SELECT *
FROM ( SELECT /*+ INDEX( i_test ) */
* FROM YUBIN
ORDER BY ZIPCD )
WHERE ROWNUM <= 20;

Oracleの「INDEX」ヒント句は「INDEX( テーブル名 INDEX名)」です（テーブル名が抜けています）。
あと、降順に索引を張ってあるのに、上記のSQLではORDER BYでDESC指定してないので昇順ソートになっています。そのため、一旦全件読み込んだ後にソートが発生しています。

Postgresqlは索引を双方向リストを使っているのか、DESC指定しないで作成したINDEXで昇順、降順ともソートしてくれますね。

お礼

お礼の欄に補足をして申し訳ないです。
　10万行のテーブルから200行ヒットするSQLで、同じ昇順インデックスを使い、降順と昇順で検索してみました。
　結論としては、どちらでもインデックスは使われるようです。

SQL> analyze table sold compute statistics;

表が分析されました。

SQL> select * /* +index(sold i_sold)*/ from sold where to_date('2010-01-01') < sold_date
2 and sold_date < to_date('2010-01-02')
3 order by sold_date;

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 223 | 9366 | 224 (0)| 00:00:03 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SOLD | 223 | 9366 | 224 (0)| 00:00:03 |
|* 3 | INDEX RANGE SCAN | I_SOLD | 223 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

SQL> select * /* +index(sold i_sold)*/ from sold where to_date('2010-01-01') < sold_date
2 and sold_date < to_date('2010-01-02')
3 order by sold_date desc;

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 223 | 9366 | 224 (0)| 00:00:03 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | SOLD | 223 | 9366 | 224 (0)| 00:00:03 |
|* 3 | INDEX RANGE SCAN DESCENDING| I_SOLD | 223 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

補足

ご指摘いただきありがとうございました。修正して実行してみました。
> SQL> analyze table yubin compute statistics;
>
> 表が分析されました。
>
> SQL> SELECT *
> 2 FROM ( SELECT /*+ INDEX( yubin i_test ) */
> 3 * FROM YUBIN
> 4 ORDER BY ZIPCD DESC)
> 5 WHERE ROWNUM <= 20;

ですが、まだフルスキャンします。

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 20 | 16060 |
|* 1 | COUNT STOPKEY
| 2 | VIEW | | 10M| 7939M|
|* 3 | SORT ORDER BY STOPKEY| | 10M| 504M| 756M| 156K (1)| 00:31:16 |
| 4 | TABLE ACCESS FULL | YUBIN | 10M| 504M| | 24708 (1)| 00:04:57 |

ところで、、、

> Postgresqlは索引を双方向リストを使っているのか、DESC指定しないで作成したINDEXで昇順、降順ともソートしてくれますね。

PostgreSQLもOracleもデフォルトのインデックスはどちらもB-TREEで構造は同じですよ。Postgreにもインデックスは作成時に昇順か降順か指定できますが、これが合っている方がちょっとは効果が高いというだけで、順序が合っていなくても、インデックスがないよりは圧倒的に早くなります。
で、疑問なのですが。
Oracleのチューニングガイドを探しまくっても、昇順で作ったインデックスがDESCの時に使われない、というのはどこにもみつからず、また、B-TREEの構造から考えても使われないというのはあまりにも不自然な気がします。
疑うようで申し訳ないのですが、昇順で作られたインデックスが降順検索で使われないというのは本当でしょうか？

回答No.8

nora1962

実行するSQLは
SELECT *
FROM ( SELECT /*+ INDEX( YUBIN IDX_YUBIN_CITY_ZIPCD ) */
* FROM YUBIN
WHERE CITY LIKE '千代田区%'
ORDER BY ZIPCD DESC )
WHERE ROWNUM <= 20
になります。

補足

いえ、私が質問しているのは

> SELECT *
> FROM ( SELECT /*+ INDEX( YUBIN IDX_YUBIN_ZIPCD ) */
> * FROM YUBIN
> ORDER BY ZIPCD DESC )
> WHERE ROWNUM <= 20
> の実行計画はINDEX「IDX_YUBIN_ZIPCD 」を使用して20件取得して終了します。

という部分です。ようするに全件なめないSQLが知りたいのです。
ちなみに、今更ですがバージョンは11.2.0.2.0です。

回答No.7

nora1962

oracleのチューニングで難しいのは、絞り込み条件が指定されると、ヒント句で指定されたINDEXのランダムREADで全件取得して、絞り込みを実行する点です。
このような場合は、かえってINDEXを使わないテーブルFULL SCANのほうが実行コストは低くなります。
日本郵便が公開している郵便番号データを例にとると
CREATE　TABLE YUBIN
（
CODE CHAR(5) /* 全国j公共団体コード */,
OLDZIPCD VARCHAR2(5) /* 旧郵便コード */,
ZIPCD VACHAR2(7) /* 郵便番号 */,
KEN_KANA VARCHAR2(255) /* 都道府県名カナ */,
CITY_KANA VARCHAR2(255) /* 市町村名カナ */,
AREA_KANA VARCHAR2（255） /* 町域名カナ */,
KEN VARCHAR2(255) /* 都道府県名 */,
CITY VARCHAR2(255) /* 市町村名 */,
AREA VARCHAR2（255） /* 町域名 */
）；
で
CREATE INDEX IDX_YUBIN_ZIPCD ON YUBIN ( ZIPCD DESC );
を作って
ANALYZE TABLE YUBIN COMPUTE STATISTICS;
では
SELECT *
FROM ( SELECT /*+ INDEX( YUBIN IDX_YUBIN_ZIPCD ) */
* FROM YUBIN
ORDER BY ZIPCD DESC )
WHERE ROWNUM <= 20
の実行計画はINDEX「IDX_YUBIN_ZIPCD 」を使用して20件取得して終了します。
しかし
SELECT *
FROM ( SELECT /*+ INDEX( YUBIN IDX_YUBIN_ZIPCD ) */
* FROM YUBIN
WHERE CITY LIKE '千代田区%'
ORDER BY ZIPCD DESC )
WHERE ROWNUM <= 20
では、INDEXで全件取得してから、絞り込みを行ってしまうのです。

対処方法としては、絞り込み条件がある程度確定している場合（たとえば絞り込みは市町村名でしか行わない）は複合INDEXを作成することでパフォーマンスをアップすることが可能です。
たとえば上記の例なら
CREATE INDEX IDX_YUBIN_CITY_ZIPCD ON YUBIN ( CITY, ZIPCD DESC );
です。
これなら、絞り込みを行った時点でソート順にデータが取得可能です。

補足

同じことをやってみました。
・YUBINテーブルの作成
・テストデータ100万件ほど
・ZIPCDにI_TESTというインデックスをDESCで張る
・ANALYZE TABLE YUBIN COMPUTE STATISTICSを実行
・下記のSQLの実行計画を取得
SELECT *
FROM ( SELECT /*+ INDEX( i_test ) */
* FROM YUBIN
ORDER BY ZIPCD )
WHERE ROWNUM <= 20;

結果（文字数の関係で必要ない部分は省略しています）
| Id | Operation | Name | Rows | Bytes |TempSpc
| 0 | SELECT STATEMENT||20 | 16060 |
|* 1 | COUNT STOPKEY
| 2 | VIEW
|* 3 | SORT ORDER BY STOPKEY||10M| 504M| 756M|
| 4 | TABLE ACCESS FULL | YUBIN |10M| 504M|

おそらく表示上見づらいと思うのですが、一時表を756M使うようなソートがかかっています。そして、5段階目ではTABLE ACCESS FULLがかかっています。
個人的にはサブクエリー部分では絞り込みを行っていないので、アクセスフルは自然な動きだとは思いますが。。。

どのようにすれば
> 実行計画はINDEX「IDX_YUBIN_ZIPCD 」を使用して20件取得して終了します。
という風になるのでしょうか？

ところで、
> SELECT *
> FROM ( SELECT /*+ INDEX( YUBIN IDX_YUBIN_ZIPCD ) */
> * FROM YUBIN
> WHERE CITY LIKE '千代田区%'
> ORDER BY ZIPCD DESC )
> WHERE ROWNUM <= 20
> では、INDEXで全件取得してから、絞り込みを行ってしまうのです。

CITYにインデックスを張っていないのなら、当然の動作だと思いますが違うでしょうか？？

回答No.6

nora1962

前提としてSQL実行する前にテーブルの統計情報を取得してください。
これで実行すると、INDEXはFULL SCANしますが、実データは20件しか取得しません。
ORACLEのヒント句はバージョンによって、かなり異なりますので使用する際注意が必要です。

EXPLAINコマンドやAUTOTRACEなどを使って、適切な実行計画が選ばれるようにするようにしてください。
PosqgresqlやMySQLは降順索引でもINDEXを使用するみたいなので、oracleはちょっと使いにくいかもしれません。

お礼

1 filter(ROWNUM<=20)
5 filter(ROWNUM<=20)
9 filter(ROWNUM<=20)

統計

80 recursive calls
0 db block gets
164778 consistent gets
161640 physical reads
0 redo size
1184 bytes sent via SQL*Net to client
427 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
113 sorts (memory)
0 sorts (disk)
20 rows processed

結論としては
・インデックスをdescにしても変わったのはrecursive callsの数だけ（80->49）で、あとは全く変わらなかった
・内側のselect文で、検索する列を増やしたら、CPUコストが大きく上がった（26756->40053）

よって
・インデックスはdescをつけてもつけなくても同じ
　・なぜなら全テーブルをなめているから？
・内側のselect文では実データも取っている
ということのようです。

補足

お世話になります。実行計画をとってみました。
やったことは
・今の状態の実行
・インデックスをdescで張り替えて実行
・検索する列を増やして実行
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |INOUT| PQ Distrib |

| 0 | SELECT STATEMENT | | 20 | 960 | | 26756 (1)| 00:05:22 | | | | | |
|* 1 | COUNT STOPKEY | | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | | |
| 3 | PX SEND QC (ORDER) | :TQ10001 | 21M| 966M| | 26756 (1)| 00:05:22 | | | Q1,01 | P>S | QC (ORDER) |
| 4 | VIEW | | 21M| 966M| | 26756 (1)| 00:05:22 | | | Q1,01 | PCWP | |
|* 5 | SORT ORDER BY STOPKEY | | 21M| 483M| 729M| 26756 (1)| 00:05:22 | | | Q1,01 | PCWP | |
| 6 | PX RECEIVE | | 21M| 483M| | 6168 (1)| 00:01:15 | | | Q1,01 | PCWP | |
| 7 | PX SEND RANGE | :TQ10000 | 21M| 483M| | 6168 (1)| 00:01:15 | | | Q1,00 | P>P | RANGE |
| 8 | PX BLOCK ITERATOR | | 21M| 483M| | 6168 (1)| 00:01:15 | 1 | 1 | Q1,00 | PCWC | |
|* 9 | SORT ORDER BY STOPKEY| | 20 | 960 | | | | | | Q1,00 | PCWP | |
| 10 | TABLE ACCESS FULL | FUN1 | 21M| 483M| | 6168 (1)| 00:01:15 | 1 | 1 | Q1,00 | PCWP | |
続きはお礼の方に書きます↓

回答No.4

nora1962

付け加えるとpostgresqlのlimit句もorder byの後に適用されます（そうでないと最新のデータが取得できません）。問題は、「order by」に指定した項目の索引でアクセスさせることだと思います。

出来れば、実行計画を取得してみてください。

降順索引を作成していてもテーブルフルスキャンを選択するようでしたら、

SELECT /*+ INDEX(テーブル名 インデックス名) */

のようにINDEXを使用するようにヒントを与えることで状況が改善されるかもしれません。

補足

うーん、ちょっとおっしゃっていることがよくわからないのですが。。。
私が問題にしているのは、20件の検索で2000万件なめる方法しか思いつかない、という点です。
それに対するご回答のようには見えないのですが。。
どういう意図でしょうか？

回答No.3

nora1962

An2です。
「sold_date」に索引をつけられているとのことですが、「DESC」指定はされているでしょうか。
ORACLEの場合、「DESC」指定の索引でないと降順ソートには使われなかったと思います。

回答No.2

nora1962

検証していないのですが
sold_dateにNOT NULL制約をつけて
create index インデックス名 ON テーブル名 （ 絞り込み条件項目, sold_date desc）
というインデックスを作成すれば
ORDER BY の際にINDEXサーチするかもしれません。

補足

ご回答ありがとうございます。
sold_dateにはnot nullもインデックスもついています。付け加えるなら、売り上げ表はsold_dateをキーにしたレンジパーティション表にしています。
問題は「最初の20件だけを検索したい」というところなのです。
PostgreSQLにはlimit という独自構文があって、limit 20　とすると、20件索引検索してそこで結果を返してくれます。ですので、このケースでも0.数秒で検索が完了します。
　ですが、Oracleにはそういう構文がなくて、rownumというorder byの前の順序でしか順番がとれないので、こういうケースでも2000万件全検索してしまう。
　ここで困っているのです。

回答No.1

mitoneko

今、ちょっとデバッグできる環境にないので、書き流しですが、こんな感じでどうでしょうか？

select * from sold s1
where
(select count(*)
from sold s2
where (s1.sold_date < s2.sold_date) or ((s1.sold_date=s2.sold_date) and s1.key < s2.key)) =< 20
and 検索条件
order by sold_date desc;

　Ｏｒａｃｌｅに関わらずの汎用品です。
　内側の副select文でやっていることは、「自分より上位にいるレコードの数」です。後は、これでインデックスをちゃんと使ってくれるかどうかですが、これは、実験しないと解らないです。もし、sold_dateが一意であるなら、副セレクト文のor以降は不要かと。keyの部分はプライマリーキーのフィールド名に置き換えてください。

お礼

ありがとうございます。
2000万と2000万の自己結合をいきなり試すのはちょっと怖いので、10万行に絞って試してみましたが、やはり実行して3時間たっても帰ってこないです。。。
ですが、このSQL自体はすばらしいと思います。勉強になりました。