Oracle拡張の統計

カラムの統計情報とヒストグラムを収集するとき、あるカラムの収集を行うことが多い.述語が複数の関連カラムを使用する場合、制約条件の冗長性が発生します.これらの関連するカラムは、関連カラムとも呼ばれます.このような場合、クエリー・オプティマイザも不正確な判断をします.従って,これらの相関列に対して統計情報またはヒストグラムを収集して,この依存関係を記述しなければならない.
幸いなことに、Oracle 11 gからデータベースは、式または列のセットに基づいたオブジェクト統計とヒストグラムを収集し、この問題を解決することができます.この新しい統計を拡張統計(extension statistics)と呼ぶ.
この技術は実際には、式または列のセットに基づいて、拡張(extension)と呼ばれる非表示の列を作成し、拡張列に統計とヒストグラムを収集する.

一、拡張列の定義方法

Oracleに付属するパッケージdbms_を呼び出すことができます.statsの関数create_extended_statsが実現します.次に、テストテーブルの関連列を拡張列にします.テスト・テーブル文は、式upper(pad)ベースのカラムとval 2ベースのカラム・グループの「Oracleでのテーブルとカラム統計の収集」を参照してください.テストテーブルでは、val 2とval 3の値が同じで、高度に関連しています.

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME   => 'TEST',
                                        TABNAME   => 'T',
                                        EXTENSION => '(upper(pad))'),
       DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME   => 'TEST',
                                        TABNAME   => 'T',
                                        EXTENSION => '(val2,val3)')
  FROM DUAL;

これにより、2つの拡張カラムが定義されます.彼らはそれぞれ式に基づいていて、複数の列に基づいています.

二、拡張カラム情報の照会方法

userベースstat_extensions、dba_stat_extensionsとall_stat_extensionsでは、関連する拡張カラム情報をクエリーできます.

SELECT COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN, DATA_DEFAULT
  FROM USER_TAB_COLS
 WHERE TABLE_NAME = 'T';

COLUMN_NAME                              DATA_TYPE  HID DATA_DEFAULT
---------------------------------------- ---------- --- ----------------------------------------
ID                                       NUMBER     NO
VAL1                                     NUMBER     NO
VAL2                                     NUMBER     NO
VAL3                                     NUMBER     NO
PAD                                      VARCHAR2   NO
SYS_STU0KSQX64#I01CKJ5FPGFK3W9           VARCHAR2   YES UPPER("PAD")
SYS_STUPS77EFBJCOTDFMHM8CHP7Q1           NUMBER     YES SYS_OP_COMBINED_HASH("VAL2","VAL3")

data_からdefaultこの列ではSYS_OP_COMBINED_HASH(「VAL 2」,「VAL 3」)は、拡張カラム統計にハッシュ関数が使用されるため、val 2とval 3は等しい(=)述語を使用する場合にのみ、オプティマイザが拡張統計を使用します.

二、拡張統計の削除方法

Oracleが所有するdbms_を使用します.statsが提供するプロシージャdrop_extended_statsは、拡張統計を削除します.

BEGIN
  DBMS_STATS.DROP_EXTENDED_STATS(OWNNAME   => 'TEST',
                                 TABNAME   => 'T',
                                 EXTENSION => '(upper(pad))');
  DBMS_STATS.DROP_EXTENDED_STATS(OWNNAME   => 'TEST',
                                 TABNAME   => 'T',
                                 EXTENSION => '(val2,val3)');
END;

最後に、拡張統計は、Oracle 11 gの別の新しい特性である仮想カラムに基づいています.データは格納されていませんが、現実的な意味はありますか?開発コードでは、upper(varchar 2)、trunc(date)が多くのsql文で使用されていることが予想されます.この場合、これらの列にインデックスが作成されても、実行計画はインデックス化されません.全表スキャンを避けるために、述語はできるだけ関数に変換されないように書き換えるのが最善ですが、文を変換しにくいときに仮想列を作成し、仮想列にインデックスを作成することができます.たとえば、次の方法があります.

CREATE TABLE persons(
NAME VARCHAR2(100),
name_upper AS (UPPER(NAME)));

頻繁なクエリでupper(name)='MIKE'を使用した場合、name_を使用できます.upper='MIKE'は、仮想カラムがインデックスを確立することを前提としています.もちろん、仮想カラムはデータが保存されていないため、データを挿入してすべてのカラムを指定することはできません.