[Oracle9i] 降順索引の謎

■ 例えば、次のようなテーブルがある。

CREATE TABLE URIAGE
(
    HANBAIBI                       DATE,
    STORE                          NUMBER(3,0),
    URIAGE                         NUMBER(7,0)
)
/

■ HANBAIBIには2006年1月1日から12月31日までの日付、STOREとURIAGEには適当に数値を入れておく。

■ 降順索引を作成する。

CREATE INDEX URIAGE_HANBAIBI_DESC
    ON URIAGE (HANBAIBI DESC)
/

■ 検索してみる。

SELECT HANBAIBI,SUM(URIAGE)
FROM URIAGE
WHERE HANBAIBI BETWEEN TO_DATE('2006/12/01') AND TO_DATE('2006/12/31')
GROUP BY HANBAIBI
/

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     TABLE ACCESS (FULL) OF 'URIAGE'

　せっかく作った降順索引が使われない。

　これはなぜかというと、降順索引はファンクション索引の一種で、統計情報を取得しないと利用できないから。

■ 統計情報を取り、再度、検索してみる。

SELECT HANBAIBI,SUM(URIAGE)
FROM URIAGE
WHERE HANBAIBI BETWEEN TO_DATE('2006/12/01') AND TO_DATE('2006/12/31')
GROUP BY HANBAIBI
/

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=22)
   1    0   SORT (GROUP BY NOSORT) (Cost=8 Card=1 Bytes=22)
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'URIAGE' (Cost=4 Card=1 Bytes=22)
   4    3         INDEX (RANGE SCAN) OF 'URIAGE_HANBAIBI_DESC' (NON-UNIQUE) (Cost=3 Card=1)

　降順索引が使われるようになり、ソートも実質発生していない。

　で、遊びとして同じ列に昇順の索引も作ったらどうなるかだろうか？

CREATE INDEX URIAGE_HANBAIBI_ASC
    ON URIAGE (HANBAIBI)
/

　索引を作って、再度統計情報をとり、検索してみた。

SELECT HANBAIBI,SUM(URIAGE)
FROM URIAGE
WHERE HANBAIBI BETWEEN TO_DATE('2006/01/01') AND TO_DATE('2006/01/31')
GROUP BY HANBAIBI
/

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=15 Bytes=315)
   1    0   SORT (GROUP BY NOSORT) (Cost=23 Card=15 Bytes=315)
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'URIAGE' (Cost=19 Card=21 Bytes=441)
   4    3         INDEX (RANGE SCAN) OF 'URIAGE_HANBAIBI_DESC' (NON-UNIQUE) (Cost=3 Card=37)

　期待としては、1月分を検索したので昇順索引を使ってほしかったんだけどそううまくはいかないみたい。

　ちなみにヒント句をつけると以下の通り。

SELECT /*+ INDEX (URIAGE URIAGE_HANBAIBI_ASC) */ HANBAIBI,SUM(URIAGE)
FROM URIAGE
WHERE HANBAIBI BETWEEN TO_DATE('2006/01/01') AND TO_DATE('2006/01/31')
GROUP BY HANBAIBI
/

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=15 Bytes=315)
   1    0   SORT (GROUP BY NOSORT) (Cost=36 Card=15 Bytes=315)
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'URIAGE' (Cost=36 Card=21 Bytes=441)
   4    3         INDEX (RANGE SCAN) OF 'URIAGE_HANBAIBI_ASC' (NON-UNIQUE) (Cost=23 Card=21)

　昇順索引がコスト36、降順索引がコスト23と、コスト争いに負けたみたい。ちなみにフルスキャンだと以下の通り。

SELECT /*+ FULL (URIAGE) */ HANBAIBI,SUM(URIAGE)
FROM URIAGE
WHERE HANBAIBI BETWEEN TO_DATE('2006/01/01') AND TO_DATE('2006/01/31')
GROUP BY HANBAIBI
/

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=24 Card=15 Bytes=315)
   1    0   SORT (GROUP BY) (Cost=24 Card=15 Bytes=315)
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'URIAGE' (Cost=20 Card=21 Bytes=441)

　ここら辺のコストの違いは納得しづらいね。