オプティマイザの判断ミスを疑ってみよう：Oracleパフォーマンス障害の克服（4）（2/3 ページ）

» 2004年11月09日 00時00分公開

問題解決！オプティマイザの挙動を正確に理解する

サンプル表の作成

　それでは統計情報がどのように実行計画に影響を与えるか、確認していきます。まずリスト5のサンプル表と索引を作成します。以降、オプティマイザのモードは「CHOOSE」であるとします。

表：

CREATE TABLE test_table(no number, data VARCHAR2(20));



データ：　テストデータを1000件挿入

BEGIN

  FOR i IN 1..1000 LOOP

    INSERT INTO test_table(no,data)

    VALUES((i * 100), TO_CHAR('data_' || (i * 100)));

  END LOOP;

END;

/



索引：

CREATE INDEX idx_test_table ON test_table(no);

リスト5　サンプル表と索引の作成

統計情報の有無による実行計画の違い

　まずは統計情報を取得せずに、AUTOTRACEを実行し実行計画を確認してみます。

SQL> SET AUTOTRACE ON;

SQL> SELECT * FROM test_table WHERE no >= 1 AND no <= 1000;

リスト6　AUTOTRACEを実行し実行計画を確認

図1　統計情報を取得する前の実行計画（クリックで拡大します）

　「INDEX (RANGE SCAN) OF 'IDX_TEST_TABLE' (NON-UNIQUE)」と表示され、インデックス・スキャンが行われたことが分かります。オプティマイザ・モードが「CHOOSE」であるため、統計情報の存在しないテーブルはルールベースを採用した検索となります。

　次に、test_tableとidx_test_tableにANALYZEコマンドで統計情報を作成し、同じSQLを発行します。

SQL> ANALYZE TABLE test_table COMPUTE STATISTICS;

SQL> ANALYZE INDEX idx_test_table COMPUTE STATISTICS;

SQL> SELECT * FROM test_table WHERE no >= 10 AND no <=10000;

リスト7　ANALYZEコマンドを実行し、AUTOTRACEで実行計画を確認

図2　統計情報を取得した後の実行計画（クリックで拡大します）

　「TABLE ACCESS (FULL) OF 'TEST_TABLE' (Cost=2 Card=100 Bytes=1300)」と表示されました。オプティマイザ・モードが「CHOOSE」で統計情報が存在するため、コストベースを採用した検索となり、ここではフル・スキャンが行われました。

実行計画の検証とオプティマイザの挙動

　索引が存在するのに、なぜインデックス・スキャンが採用されないのでしょうか。コストベースでは、テーブル全体の構成を統計情報から取得し、NO列の10から10000までのテーブルデータを取得する際に、索引を使用して検索するよりも、表データを1行目から順に見ていき、10より大きいデータを採用する方がコスト的に有効であると判断したからです。

　以下のSQL文では1000行のデータから1行を取得するので索引を使用した方が有効と判断され、「INDEX (RANGE SCAN) OF 'IDX_TEST_TABLE'(NON-UNIQUE) (Cost=1 Card=1)」の表示からインデックス・スキャンが採用されたことが分かります。

SQL> SELECT * FROM test_table WHERE no =10000;

リスト8　1件のデータを検索