OS:Windows Server2003 R2
DB:Oracle10.1.0.5 Standard Edition
row_number関数を用いた時の結果について質問があります。
以下のようなテーブルが存在します。
テーブル名:TESTTABLE1
フィールド:(実際は以下の2つ以外に100くらいフィールドがあります)
CHARDATETIME(14) →CHAR型で日時がYYYYMMDDhhmmssで入る
SEQNO(7)→CHAR型で一意キー
インデクス:(実際は他のフィールドのうち、10フィールドくらいにはインデクスがあります)
TESTTABLE1_CHARDATETIME_IDX →CHARDATETIMEに対するB-treeインデクス
このテーブルには10万件のレコードが存在しています。
ここから日時の新しい方から10件取得するために以下のSQLを実行しました
SELECT ROW_NUMBER() OVER (ORDER BY CHARDATETIME DESC,SEQNO) AS NUM,SEQNO, CHARDATETIME FROM TESTTABLE1 WHERE ROWNUM <= 10
ここからが質問です。
同じSQLでも実行計画によって結果が変わることがあるのでしょうか?
このSQLにヒント句を加えて実行計画を変えてみると、最新の10件が取得できない実行計画があります。
1.日時のフィールドのインデクスを使用する。
SELECT /*+ INDEX_DESC(TESTTABLE1 TESTTABLE1_CHARDATETIME_IDX) */ ROW_NUMBER() OVER (ORDER BY CHARDATETIME DESC,SEQNO) AS NUM,SEQNO, CHARDATETIME FROM TESTTABLE1 WHERE ROWNUM <= 10
この時の実行計画
SELECT STATEMENT Cost = 1101
WINDOW NOSORT
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID TESTTABLE1
INDEX FULL SCAN DESCENDING TESTTABLE1_CHARDATETIME_IDX
この場合は最新10件が取得できます。
2.フルスキャンする
SELECT /*+ FULL(TESTTABLE1) */ ROW_NUMBER() OVER (ORDER BY CHARDATETIME DESC,SEQNO) AS NUM,SEQNO,CHARDATETIME FROM TESTTABLE1 WHERE ROWNUM <= 10
このときの実行計画
SELECT STATEMENT Cost = 21674
WINDOW SORT
COUNT STOPKEY
TABLE ACCESS FULL TESTTABLE1
この場合、出てくる10件は最新10件にならず、以下のSQLのようにソートしないで出てくる結果10件と同じ10件が出てきてしまいます。
SELECT SEQNO, CHARDATETIME FROM TESTTABLE1 where rownum <=10
実際に動作させているシステムではヒントは使用しておらず、たいていの場合は「1」の実行計画になり、ほしい結果が出てきますが、ほかのフィールドを検索に加えたときに、「2」のフルスキャンになるときがあり、この時最新の結果が得られないという問題になっています。