大量データからROWNUMを使用してｎ件目を取得する際のパフォーマンス劣化対応 †

-----------------------------------------------------------------
-- テスト用テーブルの作成など
-----------------------------------------------------------------
-- テーブル作成
CREATE TABLE T_ORDER (ORDER_ID NUMBER(10), ORDER_NAME VARCHAR(20), ORDER_DATE DATE);
ALTER TABLE T_ORDER ADD CONSTRAINT PK_T_ORDER PRIMARY KEY(ORDER_ID);
CREATE INDEX IDX1_ORDER ON T_ORDER(ORDER_DATE);
CREATE INDEX IDX2_ORDER ON T_ORDER(ORDER_NAME);

-- データ登録
DECLARE
	START_NUM NUMBER(10);
BEGIN
	SELECT MAX(ORDER_ID) INTO START_NUM FROM T_ORDER;
	FOR i IN (START_NUM+1)..START_NUM+100000 LOOP
		INSERT INTO T_ORDER(ORDER_ID, ORDER_NAME, ORDER_DATE) VALUES(i,'ORDER_' || LPAD(TO_CHAR(i),10,'0') ,SYSTIMESTAMP + i);
	END LOOP;
	COMMIT;
END;
/

-- 統計情報取得
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME       => 'DAISUKE'
    ,TABNAME      => 'T_ORDER'
    ,METHOD_OPT   => 'FOR ALL INDEXED'  -- 索引列のカラム統計を取る
    ,CASCADE      => TRUE               -- 関連する索引の統計も取る
  );
END;
/

SET AUTOTRACE ON
SET SERVEROUTPUT ON
-----------------------------------------------------------------
-- 主キーでの並び替えて最近の10件を取得(FIRST_ROWSヒントを使用)
-----------------------------------------------------------------
select order_id, order_date
  from (
        select /*+ FIRST_ROWS(10) */ order_id,order_date
         from t_order
        order by order_id desc
       )
 where rownum <= 10

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=10 Bytes=260)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=1 Card=10 Bytes=260)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' (Cost=4485 Card=500000 Bytes=8000000)
   4    3         INDEX (FULL SCAN DESCENDING) OF 'PK_T_ORDER' (UNIQUE) (Cost=1260 Card=500000)

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        645  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

速いし、physical readsも少ない。（っていうか０？・・・それって、この結果あってんのかな？）
INDEXも使われてる！
-----------------------------------------------------------------
-- 主キーでの並び替えて最近の10件を取得(INDEX_DESCヒントを使用)
-----------------------------------------------------------------
select order_id, order_date
  from (
        select /*+ INDEX_DESC(t_order PK_T_ORDER) */ order_id,order_date 
          from t_order
         order by order_id desc
       )
 where rownum <= 10

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4485 Card=10 Bytes=13000000)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=4485 Card=500000 Bytes=13000000)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' (Cost=4485 Card=500000 Bytes=8000000)
   4    3         INDEX (FULL SCAN DESCENDING) OF 'PK_T_ORDER' (UNIQUE) (Cost=1260 Card=500000)

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        645  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

速いし、physical readsも少ない。
INDEXも使われてる！
-----------------------------------------------------------------
-- 主キー以外の項目（注文日）で並び替えて最近の10件を取得(FIRST_ROWSヒントを使用)
-----------------------------------------------------------------
select order_id, order_date
  from (
       select /*+ FIRST_ROWS(10) */ order_id,order_date
         from t_order
        order by order_date desc
       ) 
 where rownum <= 10;

select order_id, order_date
  from (
       select /*+ INDEX_DESC(T_ORDER IDX1_ORDER) */ order_id,order_date
         from t_order
        order by order_date desc
       ) 
 where rownum <= 10;

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=10 Bytes=260)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=1 Card=10 Bytes=260)
   3    2       SORT (ORDER BY STOPKEY) (Cost=6033 Card=500000 Bytes=8000000)
   4    3         TABLE ACCESS (FULL) OF 'T_ORDER' (Cost=312 Card=500000 Bytes=8000000)

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3232  consistent gets
       3218  physical reads
          0  redo size
        627  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

何故か索引が使用されない。(FULLスキャンしとる。。)
INDEX_DESC使用しても変わらず。
ヒントなしでも変わらずだった。。
オプティマイザの判断ミスか？５０万件のフルスキャンで Cost=314 って。。
索引スキャンの時の Cost=1260 より全然低いって判断されとる。。
っていうか、オプティマイザヒント効いてないし・・・（使えよ！索引）
-----------------------------------------------------------------
-- 主キーを注文日に変えて再度検証してみる事に。
-----------------------------------------------------------------
CREATE TABLE T_ORDER2 AS SELECT * FROM T_ORDER;
ALTER TABLE T_ORDER2 ADD CONSTRAINT PK_T_ORDER2 PRIMARY KEY(ORDER_DATE);
CREATE INDEX IDX1_ORDER2 ON T_ORDER2(ORDER_ID);
CREATE INDEX IDX2_ORDER2 ON T_ORDER2(ORDER_NAME);

-- 統計情報取得
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME       => 'DAISUKE'
    ,TABNAME      => 'T_ORDER2'
    ,METHOD_OPT   => 'FOR ALL INDEXED'
    ,CASCADE      => TRUE
  );
END;
/
-----------------------------------------------------------------
-- 主キー(注文日)で並び替えて最近の10件を取得(FIRST_ROWSヒントを使用)
-----------------------------------------------------------------
select order_id, order_date
  from (
        select /*+ FIRST_ROWS(10) */ order_id,order_date
         from t_order2
        order by order_date desc
       )
 where rownum <= 10

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=10 Bytes=260)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=1 Card=10 Bytes=260)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER2' (Cost=4938 Card=500000 Bytes=8000000)
   4    3         INDEX (FULL SCAN DESCENDING) OF 'PK_T_ORDER2' (UNIQUE) (Cost=1692 Card=500000)

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          2  physical reads
          0  redo size
        645  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

おお！速なった！
INDEXも使われてる！
physical readsも少ない。
-----------------------------------------------------------------
-- 主キー以外（注文ID）で並び替えて最近の10件を取得(FIRST_ROWSヒントを使用)
-----------------------------------------------------------------
select order_id, order_date
  from (
        select /*+ FIRST_ROWS(10) */ order_id,order_date 
          from t_order2
         order by order_id desc
       )
 where rownum <= 10

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=10 Bytes=260)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=1 Card=10 Bytes=260)
   3    2       SORT (ORDER BY STOPKEY) (Cost=6035 Card=500000 Bytes=8000000)
   4    3         TABLE ACCESS (FULL) OF 'T_ORDER2' (Cost=314 Card=500000 Bytes=8000000)

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3252  consistent gets
       3245  physical reads
          0  redo size
        645  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

おお！遅なった！
INDEXも使われてない。 
physical reads も増えてる。
という事で、主キーの時しかSORT時に索引が使われていない事が判明。
-----------------------------------------------------------------
-- じゃあじゃあ、UNIQUE な索引だとどうだろうか？
-----------------------------------------------------------------
-- ユニークな索引に作り変える
DROP INDEX IDX1_ORDER2;
CREATE UNIQUE INDEX IDX1_ORDER2 ON T_ORDER2(ORDER_ID);

-- 統計情報取得
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME       => 'DAISUKE'
    ,TABNAME      => 'T_ORDER2'
    ,METHOD_OPT   => 'FOR ALL INDEXED'
    ,CASCADE      => TRUE
  );
END;
/

select order_id, order_date
  from (
        select /*+ FIRST_ROWS(10) */ order_id,order_date 
         from t_order2 
        order by order_id desc
       ) 
 where rownum <= 10

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=10 Bytes=260)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=1 Card=10 Bytes=260)
   3    2       SORT (ORDER BY STOPKEY) (Cost=6035 Card=500000 Bytes=8000000)
   4    3         TABLE ACCESS (FULL) OF 'T_ORDER2' (Cost=314 Card=500000 Bytes=8000000)

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3252  consistent gets
       3246  physical reads
          0  redo size
        645  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

一緒か。。。
という事は、やっぱ主キーで並び替えた時しか索引が使われていない？
主キー以外の索引が、何故かオプティマイザに考慮されていない気がする。
もしくは、統計情報がちゃんと取れてない？
もう少し調査が必要っぽい。。
トップ   差分 バックアップ リロード   一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2009-06-11 (木) 00:48:52 (5698d)