TOP ＞ ファンクション索引

ファンクション索引

  1. はじめに
  2. ファンクション索引
  3. 索引作成前
  4. 索引の作成
  5. 索引作成後
  6. 手順のまとめ

1.はじめに

ファンクション索引を使用する際に統計情報に関する情報が必要になるので以下にコマンドを書いておきます。
統計情報の詳細についてはパフォーマンス・チューニング(初級編)を参照して下さい。

【表の統計情報を取得】オブジェクトの所有者で行なう
 ANALYZE TABLE テーブル名 COMPUTE STATISTICS;

【索引の統計情報を取得】オブジェクトの所有者で行なう
 ANALYZE INDEX 索引名 COMPUTE STATISTICS;

【表の統計情報を削除】オブジェクトの所有者で行なう
 ANALYZE TABLE テーブル名 DELETE STATISTICS;

【索引の統計情報を削除】オブジェクトの所有者で行なう
 ANALYZE INDEX 索引名 DELETE STATISTICS;

【表の統計情報を参照】DBAで行なう
  SQL>COL TABLE_NAME FOR A15
  SQL>
  SELECT 
      TABLE_NAME, 
      NUM_ROWS, 
      BLOCKS, 
      AVG_ROW_LEN,
      TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS')
  FROM 
      DBA_TABLES
  WHERE 
      TABLE_NAME = 'テーブル名';

【索引の統計情報を参照】DBAで行なう
  SQL>COL INDEX_NAME FOR A15
  SQL>
  SELECT 
      INDEX_NAME, 
      NUM_ROWS, 
      DISTINCT_KEYS "DISTINCT",
      LEAF_BLOCKS, 
      CLUSTERING_FACTOR "CF",
      AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY"
  FROM 
      DBA_INDEXES
  WHERE 
      TABLE_NAME = 'テーブル名'
  ORDER BY 
      INDEX_NAME;

2.ファンクション索引

ファンクション索引を作成する事によるパフォーマンスの向上を試してみます。
なお、当機能は Oracle 8i Enterprise Edition または Oracle 9i Standard Edition以降 が必要です。

ファンクション索引は検索に使用する列に対する計算結果を事前に索引に登録しておき、検索時にその索引を使用するというものです。

ファンクション索引を使用する為には QUERY REWRITEシステム権限が必要になります。
また、該当の表に対して統計情報を取得しておく必要があります。
索引を作成しただけでは検索時に索引が使用されないので注意が必要です。

実際にファンクション索引を作ってみます。
今回使用するテーブル
  SQL> DESC LTSTOCKDIC
   名前                                        NULL?    型
   ----------------------------------------- -------- ----------------------------
   KB_NCODE                                  NOT NULL CHAR(7)      →コード１
   KB_SUBCD                                  NOT NULL CHAR(5)      →コード２
   KB_FROM                                   NOT NULL CHAR(8)
   KB_TO                                     NOT NULL CHAR(8)
   KB_KRNAME1                                         VARCHAR2(30) →名前１
   KB_KRNAME2                                         VARCHAR2(70) →名前２

   データ件数は約 25,000件です。

   名前１  ＋名前２  を検索の対象とします。
   コード１＋コード２も検索の対象とします。

3.索引作成前

ファンクション索引を作成する前に、索引作成前の実行計画と時間を示しておきます。
ファンクション索引を使用しない場合
【実行計画】

    SQL> SET AUTOTRACE TRACEONLY
    SQL> 
        UPDATE
            LTLARGESTOCKO L
        SET
            (
                L.KB_NCODE,
                L.KB_SUBCD
            )
            =
            (
                SELECT
                    SD.KB_NCODE,
                    SD.KB_SUBCD
                FROM
                    LTSTOCKDIC SD
                WHERE
                    ( SD.KB_NCODE || SD.KB_SUBCD)
                    =
                    (
                        SELECT
                            MAX( D.KB_NCODE || D.KB_SUBCD ) CODE
                        FROM
                            LTSTOCKDIC D
                        WHERE
                            LTCMF000( L.KB_KBNAME ) = ( D.KB_KRNAME1 || D.KB_KRNAME2 )
                    )
            )
        WHERE
            L.CM_NCODE  = '00000002' AND
            L.CM_YYYYMM = '200203';

    31行が更新されました。

    Execution Plan
    ----------------------------------------------------------
       0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=3 Card=33 Bytes=2838)
       1    0   UPDATE OF 'LTLARGESTOCKO'
       2    1     TABLE ACCESS (BY INDEX ROWID) OF 'LTLARGESTOCKO' (Cost=3 Card=33 Bytes=2838)
       3    2       INDEX (RANGE SCAN) OF 'LTLARGESTOCKO_IXP' (UNIQUE) (Cost=2 Card=33)
       4    0   INDEX (FULL SCAN) OF 'LTSTOCKDIC_IXP' (UNIQUE) (Cost=109 Card=241 Bytes=7712)
       5    4     SORT (AGGREGATE)
       6    5       TABLE ACCESS (FULL) OF 'LTSTOCKDIC' (Cost=65 Card=241 Bytes=16870)

    Statistics
    ----------------------------------------------------------
              0  recursive calls
            186  db block gets
          24154  consistent gets
              0  physical reads
          13856  redo size
            858  bytes sent via SQL*Net to client
            943  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             31  rows processed

【実行時間】

    SQL> SET TIMING ON
    SQL>
        UPDATE
            LTLARGESTOCKO L
        SET
        (
            L.KB_NCODE,
            L.KB_SUBCD
        )
        =
        (
            SELECT
                SD.KB_NCODE,
                SD.KB_SUBCD
            FROM
                LTSTOCKDIC SD
            WHERE
                ( SD.KB_NCODE || SD.KB_SUBCD)
                =
                (
                    SELECT
                        MAX( D.KB_NCODE || D.KB_SUBCD ) CODE
                    FROM
                        LTSTOCKDIC D
                    WHERE
                        LTCMF000( L.KB_KBNAME ) = ( D.KB_KRNAME1 || D.KB_KRNAME2 )
                )
        )
        WHERE
        L.CM_NCODE  = '00000002' AND
        L.CM_YYYYMM = '200203';

    31行が更新されました。

    経過: 00:02:48.12　→2分48秒
対象のテーブルをフルスキャンしているので非常に遅く（2分48秒）なっています。

4.索引の作成

ファンクション索引を作成します。

DBAで該当ユーザ（ファンクション索引を作成するユーザ）に QUERY REWRITEシステム権限を付与します。
今回は LT02 ユーザに権限を付与します。
  SQL> CONNECT SYSTEM/MANAGER
  接続されました。
  SQL> GRANT QUERY REWRITE TO LT02;

  権限付与が成功しました。

該当ユーザでファンクション索引を作成します。
  SQL> CONNECT LT02/LT02
  接続されました。
  SQL> CREATE INDEX DIC_TEST1 ON LTSTOCKDIC ( KB_KRNAME1 || KB_KRNAME2 );

  索引が作成されました。

  SQL> CREATE INDEX DIC_TEST2 ON LTSTOCKDIC ( KB_NCODE || KB_SUBCD );

  索引が作成されました。
まだ統計情報が存在しないので検索を行っても索引は使用されません。

表の統計情報を作成します
  SQL> CONNECT LT02/LT02
  接続されました。
  SQL> ANALYZE TABLE LTSTOCKDIC COMPUTE STATISTICS;

  表が分析されました。

統計情報を参照してみます。
  SQL> CONNECT SYSTEM/MANAGER
  接続されました。

  SQL> COL TABLE_NAME FOR A15
  SQL> SELECT
      TABLE_NAME,
      NUM_ROWS,
      BLOCKS,
      AVG_ROW_LEN,
      TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS')
  FROM
      DBA_TABLES
  WHERE
      TABLE_NAME = 'LTSTOCKDIC';

  TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANALYZ
  --------------- ---------- ---------- ----------- -------------------
  LTSTOCKDIC           24045        670         198 03/04/2003 15:50:31


  索引の統計情報を参照してみます。
  SQL> COL INDEX_NAME FOR A15
  SQL> SELECT
      INDEX_NAME,
      NUM_ROWS,
      DISTINCT_KEYS "DISTINCT",
      LEAF_BLOCKS,
      CLUSTERING_FACTOR "CF",
      AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY"
  FROM
      DBA_INDEXES
  WHERE
      TABLE_NAME = 'LTSTOCKDIC'
  ORDER BY
      INDEX_NAME;

  INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS         CF   ALFBPKEY
  --------------- ---------- ---------- ----------- ---------- ----------
  DIC_INDX1            24045       4420         106      22138          1
  DIC_INDX2            19236          4          82        546         20
  DIC_TEST1            24045      22100          94      21958          1
  DIC_TEST2            24045      24045          81      24021          1
  LTSTOCKDIC_IXP       24045      24045         108      24021          1
これで作成したファンクション索引が使用可能になります。
あとは使用時にQUERY_REWRITE_ENABLEDパラメータをTRUEにします。

5.索引作成後

索引作成後の実行計画と時間を示しておきます。
ファンクション索引を使用する場合
【実行計画】

    SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;  → 必要です！
    セッションが変更されました。

    SQL> 
        UPDATE
            LTLARGESTOCKO L
        SET
            (
                L.KB_NCODE,
                L.KB_SUBCD
            )
            =
            (
                SELECT
                    SD.KB_NCODE,
                    SD.KB_SUBCD
                FROM
                    LTSTOCKDIC SD
                WHERE
                    ( SD.KB_NCODE || SD.KB_SUBCD)
                    =
                    (
                        SELECT
                            MAX( D.KB_NCODE || D.KB_SUBCD ) CODE
                        FROM
                            LTSTOCKDIC D
                        WHERE
                            LTCMF000( L.KB_KBNAME ) = ( D.KB_KRNAME1 || D.KB_KRNAME2 )
                    )
            )
        WHERE
            L.CM_NCODE  = '00000002' AND
            L.CM_YYYYMM = '200203';

    31行が更新されました。

    Execution Plan
    ----------------------------------------------------------
       0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=3 Card=33 Bytes=2838)
       1    0   UPDATE OF 'LTLARGESTOCKO'
       2    1     TABLE ACCESS (BY INDEX ROWID) OF 'LTLARGESTOCKO' (Cost=3 Card=33 Bytes=2838)
       3    2       INDEX (RANGE SCAN) OF 'LTLARGESTOCKO_IXP' (UNIQUE) (Cost=2 Card=33)
       4    0   TABLE ACCESS (BY INDEX ROWID) OF 'LTSTOCKDIC' (Cost=2 Card=1 Bytes=32)
       5    4     INDEX (RANGE SCAN) OF 'DIC_TEST2' (NON-UNIQUE) (Cost=1 Card=1)
       6    5       SORT (AGGREGATE)
       7    6         TABLE ACCESS (BY INDEX ROWID) OF 'LTSTOCKDIC' (Cost=2 Card=2 Bytes=140)
       8    7           INDEX (RANGE SCAN) OF 'DIC_TEST1' (NON-UNIQUE) (Cost=1 Card=2)

    Statistics
    ----------------------------------------------------------
              0  recursive calls
             62  db block gets
             73  consistent gets
              0  physical reads
           7700  redo size
            859  bytes sent via SQL*Net to client
            943  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             31  rows processed

【実行時間】

    SQL> SET TIMING ON
    SQL>    
        UPDATE
            LTLARGESTOCKO L
        SET
            (
                L.KB_NCODE,
                L.KB_SUBCD
            )
            =
            (
                SELECT
                    SD.KB_NCODE,
                    SD.KB_SUBCD
                FROM
                    LTSTOCKDIC SD
                WHERE
                    ( SD.KB_NCODE || SD.KB_SUBCD)
                    =
                    (
                        SELECT
                            MAX( D.KB_NCODE || D.KB_SUBCD ) CODE
                        FROM
                            LTSTOCKDIC D
                        WHERE
                            LTCMF000( L.KB_KBNAME ) = ( D.KB_KRNAME1 || D.KB_KRNAME2 )
                    )
            )
        WHERE
            L.CM_NCODE  = '00000002' AND
            L.CM_YYYYMM = '200203';

    31行が更新されました。

    経過: 00:00:00.03　→0.03秒
索引を使用しているので非常に早く（0.03秒）なっています。

6.手順のまとめ

まとめると以下の手順になります。

1. DBAで該当ユーザに QUERY REWRITシステム権限を付与する。

2. 該当ユーザにてファンクション索引を作成する。

3. 表の統計情報を取得する。

4. 該当セッションを変更する。

5. 検索を行う。

TOP ＞ ファンクション索引

2003/03/04　担当：H.O