** 大量データからROWNUMを使用してn件目を取得する際のパフォーマンス劣化対応 [#u7756ba7] ----------------------------------------------------------------- -- テスト用テーブルの作成など ----------------------------------------------------------------- -- テーブル作成 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も少ない。(っていうか0?・・・それって、この結果あってんのかな?) 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使用しても変わらず。 ヒントなしでも変わらずだった。。 オプティマイザの判断ミスか?50万件のフルスキャンで 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時に索引が使われていないという事が判明!(何故だ?) という事で、主キーの時しか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 一緒か。。。 という事は、やっぱ主キーで並び替えた時しか索引が使われていない? まてまて、キャッシュとかは関係ないのか? もう少し調べてみよう。 主キー以外の索引が、何故かオプティマイザに考慮されていない気がする。 もしくは、統計情報がちゃんと取れてない? もう少し調査が必要っぽい。。