| Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) B71277-09 |
|
 前 |
 次へ |
ヒストグラムは、表の列のデータ分布に関する詳細な情報を提供する特殊なタイプの列統計です。ヒストグラムでは、コインをバケツに分けるように、値をバケットに分類します。
NDVおよびデータの分布に基づいて、作成するヒストグラムのタイプが選択されます。(場合によっては、ヒストグラムを作成する際、データベースで、内部的に事前定義された数の行がサンプリングされます。)ヒストグラムのタイプは次のとおりです。
頻度ヒストグラムおよび上位頻度ヒストグラム
高さ調整済ヒストグラム(レガシー)
ハイブリッド・ヒストグラム
この項の内容は次のとおりです。
デフォルトでは、オプティマイザは、列内の個別値全体で行のデータ配分が均一であると想定します。
列にデータの偏りがある(列内のデータ配分が均一ではない)場合は、ヒストグラムを使用することにより、これらの列を含むフィルタ述語や結合述語に対してオプティマイザが正確なカーディナリティ予測を生成できるようになります。
たとえば、カリフォルニアに拠点を置く書籍店では、書籍の95%をカリフォルニアに、4%をオレゴンに、1%をネバダに出荷しています。書籍注文表には300,000行あります。表の列には、注文の出荷先の州が格納されています。ユーザーが、オレゴンに出荷された書籍の数を問い合せます。ヒストグラムを使用しない場合、オプティマイザは300000/3 (NDVは3)で均一に分布されていると想定し、カーディナリティを100,000行で見積ります。この見積りの場合、オプティマイザは全表スキャンを選択します。ヒストグラムがある場合、オプティマイザは、書籍の4%がオレゴンに出荷されていると計算し、索引スキャンを選択します。
DBMS_STATSによって表の統計が収集され、問合せによってこの表の列が参照される場合、これまでの問合せのワークロードに従って、必要に応じて自動的にヒストグラムが作成されます。
基本的なプロセスは次のとおりです。
METHOD_OPTパラメータにデフォルトのSIZE AUTOが設定された状態で、表にDBMS_STATSを実行します。
ユーザーが、この表に問合せを行います。
データベースにより前の問合せの述語が記録され、データ・ディクショナリ表SYS.COL_USAGE$が更新されます。
DBMS_STATSを再度実行すると、DBMS_STATSによりSYS.COL_USAGE$に問い合され、前回の問合せのワークロードに基づいて、どの列にヒストグラムが必要かが決定されます。
AUTO機能の結果には次のものが含まれます。
問合せが時間の経過とともに変更される場合、DBMS_STATSによって収集される統計の種類が変更される場合があります。たとえば、表のデータに変更がない場合でも、問合せとDBMS_STATSの操作によって、これらの表を参照する問合せの計画が変更される場合があります。
表の統計を収集するが問合せは行わない場合、この表の列のヒストグラムは作成されません。データベースに自動でヒストグラムを作成させるには、1つ以上の問合せを実行してSYS.COL_USAGE$の列使用情報に移入する必要があります。
例11-1 ヒストグラムの自動作成
sh.sh_extは、sh.sales表と同じ列を含む外部表だとします。新しい表sales2を作成して、sh_extをソースとして使用してバルク・ロードを実行すると、自動的にsales2の統計が作成されます(「バルク・ロードのためのオンライン統計収集」を参照)。また、次のように索引を作成します。
SQL> CREATE TABLE sales2 AS SELECT * FROM sh_ext; SQL> CREATE INDEX sh_12c_idx1 ON sales2(prod_id); SQL> CREATE INDEX sh_12c_idx2 ON sales2(cust_id,time_id);
データ・ディクショナリに問い合せて、sales2列のヒストグラムが存在するかどうか確認します。sales2にはまだ問い合されていないため、ヒストグラムはまだ作成されていません。
SQL> SELECT COLUMN_NAME, NOTES, HISTOGRAM 2 FROM USER_TAB_COL_STATISTICS 3 WHERE TABLE_NAME = 'SALES2'; COLUMN_NAME NOTES HISTOGRAM ------------- -------------- --------- AMOUNT_SOLD STATS_ON_LOAD NONE QUANTITY_SOLD STATS_ON_LOAD NONE PROMO_ID STATS_ON_LOAD NONE CHANNEL_ID STATS_ON_LOAD NONE TIME_ID STATS_ON_LOAD NONE CUST_ID STATS_ON_LOAD NONE PROD_ID STATS_ON_LOAD NONE
sales2に製品42の行数を問い合せた後、GATHER AUTOオプションを使用して表統計を収集します。
SQL> SELECT COUNT(*) FROM sales2 WHERE prod_id = 42;
COUNT(*)
----------
12116
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'SALES2',OPTIONS=>'GATHER AUTO');
データ・ディクショナリに問い合せると、前回の問合せで収集された情報に基づいてprod_id列のヒストグラムが作成されたことがわかります。
SQL> SELECT COLUMN_NAME, NOTES, HISTOGRAM
2 FROM USER_TAB_COL_STATISTICS
3 WHERE TABLE_NAME = 'SALES2';
COLUMN_NAME NOTES HISTOGRAM
------------- -------------- ---------
AMOUNT_SOLD STATS_ON_LOAD NONE
QUANTITY_SOLD STATS_ON_LOAD NONE
PROMO_ID STATS_ON_LOAD NONE
CHANNEL_ID STATS_ON_LOAD NONE
TIME_ID STATS_ON_LOAD NONE
CUST_ID STATS_ON_LOAD NONE
PROD_ID HISTOGRAM_ONLY FREQUENCY
Oracle Databaseでは、複数の基準を使用して、作成するヒストグラム(頻度、上位頻度、高さ調整済またはハイブリッド)を決定します。
ヒストグラム式では、次の変数が使用されます。
NDV
これは、列内の個別値の数を表します。たとえば、列に値100、200および300のみが含まれている場合、この列のNDVは3です。
n
この変数は、ヒストグラム・バケットの数を表します。デフォルトは254です。
p
この変数は、内部割合のしきい値を表し、(1–(1/n)) * 100と等しくなります。たとえば、n = 254の場合、pは99.6です。
追加の基準は、DBMS_STATS統計収集プロシージャでestimate_percentパラメータがAUTO_SAMPLE_SIZE (デフォルト)に設定されているかどうかです。
次の図は、ヒストグラム作成のディシジョン・ツリーを示しています。
エンドポイント番号は、バケットを一意に識別する数値です。頻度ヒストグラムおよびハイブリッド・ヒストグラムでは、エンドポイント番号は、現在および前のバケットに含まれるすべての値の頻度の累積になります。たとえば、バケットのエンドポイント番号が100の場合、現在およびすべての前のバケットの合計頻度は100です。高さ調整済ヒストグラムでは、オプティマイザはバケットの数を0または1から順に数えます。いずれの場合でも、エンドポイント番号はバケット番号になります。
エンドポイント値は、バケット内の値の範囲で最も高い値です。たとえば、バケットに含まれる値が52794と52795のみの場合、エンドポイント値は52795です。
ヒストグラムの値のポピュラリティは、次のようにカーディナリティ予測アルゴリズムに影響します。
ポピュラー値
ポピュラー値は複数のバケットのエンドポイント値として出現します。オプティマイザは、値がポピュラー値かどうかを判断するために、まずその値がバケットのエンドポイント値かどうかを確認します。エンドポイント値の場合、頻度ヒストグラムでは、オプティマイザは現在のバケットのエンドポイント番号から前のバケットのエンドポイント番号を減算します。ハイブリッド・ヒストグラムでは、エンドポイントごとにすでにこの情報が個別に格納されています。この値が1よりも大きい場合、その値はポピュラーです。
オプティマイザは、次の式を使用してポピュラー値のカーディナリティ予測を計算します。
cardinality of popular value = (num of rows in table) * (num of endpoints spanned by this value / total num of endpoints)
非ポピュラー値
ポピュラーでない値は、非ポピュラー値です。オプティマイザは、次の式を使用して非ポピュラー値のカーディナリティ予測を計算します。
cardinality of nonpopular value = (num of rows in table) * density
オプティマイザは、バケット数やNDVなどの要因に基づいて内部アルゴリズムを使用し、密度を計算します。密度は、0から1の間の小数値として表されます。値が1に近いと、述語リストでこの列を参照する問合せによって多くの行が戻されることをオプティマイザが予測していることを示します。値が0に近いと、少ない行が戻されることをオプティマイザが予測していることを示します。
関連項目:
DBA_TAB_COL_STATISTICS.DENSITY列の詳細は、『Oracle Databaseリファレンス』を参照してください。
場合によっては、バケットの合計数を減らすために、オプティマイザが複数のバケットを1つのバケットに圧縮することがあります。たとえば、次の頻度ヒストグラムは、最初のバケット番号が1で、最後のバケット番号が23であることを示しています。
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792
6 52793
8 52794
9 52795
10 52796
12 52797
14 52798
23 52799
いくつかのバケットが欠落しています。当初は2から6の各バケットに、値52793の単一のインスタンスが含まれていました。オプティマイザは、これらのバケットのすべてを最も高いエンドポイント番号(バケット6)のバケットに圧縮しました。これにより、現在このバケットには値52793のインスタンスが5個含まれています。現在のバケット(6)と前のバケット(1)とのエンドポイント番号の差が5であるため、この値はポピュラーです。このように、値52793は、圧縮前は5個のバケットを使用するエンドポイントでした。
次の注釈は、圧縮されたバケットとポピュラー値を示しています。
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792 -> nonpopular
6 52793 -> buckets 2-6 compressed into 6; popular
8 52794 -> buckets 7-8 compressed into 8; popular
9 52795 -> nonpopular
10 52796 -> nonpopular
12 52797 -> buckets 11-12 compressed into 12; popular
14 52798 -> buckets 13-14 compressed into 14; popular
23 52799 -> buckets 15-23 compressed into 23; popular
頻度ヒストグラムでは、列の各個別値がヒストグラムの1つのバケットに対応しています。各値には専用のバケットがあるため、多くの値を持つバケットもあり、ほとんど値がないバケットもあります。
頻度ヒストグラムをコインの分類に例えて考えるならば、個々のコインが最初は固有のバケットに入るようにすることです。たとえば、最初のペニーをバケット1に、2番目のペニーはバケット2に、最初のニッケルはバケット3に入れるなどのようにします。続いて、すべてのペニーを1つのペニーのバケットにまとめ、すべてのニッケルを1つのニッケルのバケットにまとめます。残りのコインについても同様に行います。
この項の内容は次のとおりです。
頻度ヒストグラムは、要求されたヒストグラム・バケットの数に依存します。
「Oracle Databaseによるヒストグラム・タイプの選択方法」の論理図に示すように、次の基準を満たす場合に、データベースは頻度ヒストグラムを作成します。
NDVがn以下である(nはヒストグラム・バケットの数(デフォルトは254)です)。
たとえば、sh.countries.country_subregion_id列には、52792から52799までの範囲で順番に8個の個別値があります。nがデフォルトの254である場合、8 <= 254であるため、オプティマイザは頻度ヒストグラムを作成します。
DBMS_STATS統計収集プロシージャのestimate_percentパラメータが、ユーザー指定の値またはAUTO_SAMPLE_SIZEに設定されている。
Oracle Database 12cからは、サンプリング・サイズがデフォルトのAUTO_SAMPLE_SIZEである場合、全表スキャンによって頻度ヒストグラムが作成されます。他のすべてのサンプリング率の指定では、データベースは、サンプルから頻度ヒストグラムを導出します。Oracle Database 12cより前のリリースでは、データベースは、小規模なサンプルに基づいてヒストグラムを収集していました。これは、頻度の低い値は多くの場合、サンプルに出現しないことを意味します。このような場合、密度を使用すると、オプティマイザが選択性を過大に見積ることがありました。
関連項目:
AUTO_SAMPLE_SIZEの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
このシナリオでは、サンプル・スキーマを使用して、頻度ヒストグラムを生成する方法を説明します。
前提条件
このシナリオでは、sh.countries.country_subregion_id列に頻度ヒストグラムを生成することを想定しています。この表には23行が含まれています。
次の問合せは、country_subregion_id列には分布が均一ではない8個の個別値が含まれていることを示しています(出力例も示します)。
SELECT country_subregion_id, count(*)
FROM sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
COUNTRY_SUBREGION_ID COUNT(*)
-------------------- ----------
52792 1
52793 5
52794 2
52795 1
52796 1
52797 2
52798 2
52799 9
頻度ヒストグラムを生成する手順は次のとおりです。
バケット数はデフォルトの254のままで、sh.countriesおよびcountry_subregion_id列の統計を収集します。
たとえば、次のPL/SQL無名ブロックを実行します。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SH'
, tabname => 'COUNTRIES'
, method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID'
);
END;
country_subregion_id列のヒストグラム情報を問い合せます。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='COUNTRIES' AND COLUMN_NAME='COUNTRY_SUBREGION_ID'; TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM ---------- -------------------- ------------ --------------- COUNTRIES COUNTRY_SUBREGION_ID 8 FREQUENCY
列の個別値がn以下(nはデフォルトの254)であるため、オプティマイザは頻度ヒストグラムを選択します。
country_subregion_id列のエンドポイント番号とエンドポイント値を問い合せます。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_HISTOGRAMS
WHERE TABLE_NAME='COUNTRIES'
AND COLUMN_NAME='COUNTRY_SUBREGION_ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792
6 52793
8 52794
9 52795
10 52796
12 52797
14 52798
23 52799
図11-2に、ヒストグラムの8個のバケットを図示します。それぞれの値は、バケットに入るコインとして表されています。
図11-2に示したように、それぞれの個別値には固有のバケットがあります。これは頻度ヒストグラムであるため、エンドポイント番号はエンドポイントの頻度の累積です。52793の場合、エンドポイント番号6は、この値が5回(6 - 1)出現することを示しています。52794の場合、エンドポイント番号8は、この値が2回(8 - 6)出現することを示しています。
エンドポイントが2以上で、前のエンドポイントよりも大きいバケットにはすべて、ポピュラー値が含まれます。したがって、バケット6、8、12、14および23にはポピュラー値が含まれます。オプティマイザは、エンドポイント番号に基づいてそれらのカーディナリティを計算します。たとえば、オプティマイザは次の式を使用して、値52799のカーディナリティ(C)を計算します。この場合、表内の行数は23です。
C = 23 * ( 9 / 23 )
バケット1、9および10には非ポピュラー値が含まれます。オプティマイザは、それらのカーディナリティを密度に基づいて見積ります。
関連項目:
DBMS_STATS.GATHER_TABLE_STATSプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
USER_TAB_COL_STATISTICSビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
USER_HISTOGRAMSビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
上位頻度ヒストグラムは頻度ヒストグラムのバリエーションで、統計上重要ではない非ポピュラー値を無視する頻度ヒストグラムです。たとえば、1000枚のコインの山にペニーが1枚しか含まれていない場合、コインをバケットに分類する際に、このペニーは無視できます。上位頻度ヒストグラムでは、高ポピュラー値に対してより適切なヒストグラムを生成できます。
この項の内容は次のとおりです。
低い数値が行のほとんどを占める場合、この低い値のセットに頻度ヒストグラムを作成すると効果的です。これは、NDVが要求されたヒストグラム・バケットの数よりも大きい場合でも同様です。頻繁に出現する値に関するより高品質なヒストグラムを作成するために、オプティマイザは、非ポピュラー値を無視し、上位頻度ヒストグラムを作成します。
「Oracle Databaseによるヒストグラム・タイプの選択方法」の論理図に示すように、次の基準を満たす場合に、データベースは上位頻度ヒストグラムを作成します。
NDVがnより大きい(nはヒストグラム・バケットの数(デフォルトは254)です)。
上位nの頻出値によって占められている行の割合がしきい値p以上である。ここで、pは(1-(1/n))*100です。
DBMS_STATS統計収集プロシージャのestimate_percentパラメータがAUTO_SAMPLE_SIZEに設定されている。
関連項目:
AUTO_SAMPLE_SIZEの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
このシナリオでは、サンプル・スキーマを使用して、上位頻度ヒストグラムを生成する方法を説明します。
前提条件
このシナリオでは、sh.countries.country_subregion_id列に上位頻度ヒストグラムを生成することを想定しています。この表には23行が含まれています。
次の問合せは、country_subregion_id列には分布が均一ではない8個の個別値が含まれていることを示しています(出力例も示します)。
SELECT country_subregion_id, count(*)
FROM sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
COUNTRY_SUBREGION_ID COUNT(*)
-------------------- ----------
52792 1
52793 5
52794 2
52795 1
52796 1
52797 2
52798 2
52799 9
上位頻度ヒストグラムを生成する手順は次のとおりです。
個別値よりも少ないバケット数を指定して、sh.countriesおよびcountry_subregion_id列の統計を収集します。
たとえば、次のコマンドを入力して7個のバケットを指定します。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SH'
, tabname => 'COUNTRIES'
, method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID SIZE 7'
);
END;
country_subregion_id列のヒストグラム情報を問い合せます。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='COUNTRIES' AND COLUMN_NAME='COUNTRY_SUBREGION_ID'; TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM ---------- -------------------- ------------ --------------- COUNTRIES COUNTRY_SUBREGION_ID 7 TOP-FREQUENCY
sh.countries.country_subregion_id列には8個の個別値が含まれますが、ヒストグラムには7個のバケットのみが含まれ、n=7となります。この場合は、上位頻度ヒストグラムまたはハイブリッド・ヒストグラムのみが作成されます。country_subregion_id列では、上位7個の最頻出値が行数の95.6%を占めています。これは、しきい値である85.7%を上回っているため、上位頻度ヒストグラムが生成されます(「頻度ヒストグラムの基準」を参照)。
この列のエンドポイント番号とエンドポイント値を問い合せます。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_HISTOGRAMS
WHERE TABLE_NAME='COUNTRIES'
AND COLUMN_NAME='COUNTRY_SUBREGION_ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 52792
6 52793
8 52794
9 52796
11 52797
13 52798
22 52799
図11-3に、上位頻度ヒストグラムの7個のバケットを図示します。この図では、値はコインとして表されています。
図11-3で示したように、それぞれの個別値には固有のバケットがあります。ただし52795は例外です。この値は非ポピュラー値であり、統計上重要ではないため、ヒストグラムから除外されます。通常の頻度ヒストグラムの場合と同様に、エンドポイント番号は値の頻度の累積になります。
関連項目:
DBMS_STATS.GATHER_TABLE_STATSプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
USER_TAB_COL_STATISTICSビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
USER_HISTOGRAMSビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
レガシーの高さ調整済ヒストグラムでは、列値がバケットに分割され、各バケットにほぼ同数の行が存在します。
たとえば、99枚のコインを4つのバケットに分配する場合、それぞれのバケットに入るコインの枚数はおよそ25枚です。ヒストグラムは、値範囲でのエンドポイントの位置を示します。
この項の内容は次のとおりです。
Oracle Database 12cより前は、NDVがnを超えた場合に、高さ調整済ヒストグラムが作成されていました。このタイプのヒストグラムは、範囲述語、および少なくとも2つのバケットでエンドポイントとして出現する値の等価述語に対して有用でした。
「Oracle Databaseによるヒストグラム・タイプの選択方法」の論理図に示すように、次の基準を満たす場合に、データベースは高さ調整済ヒストグラムを作成します。
NDVがnより大きい(nはヒストグラム・バケットの数(デフォルトは254)です)。
DBMS_STATS統計収集プロシージャのestimate_percentパラメータがAUTO_SAMPLE_SIZEに設定されていない。
このため、Oracle Database 12cによって新しいヒストグラムが作成される場合、サンプリング率がAUTO_SAMPLE_SIZEに設定されていると、作成されるヒストグラムは、高さ調整済ヒストグラムではなく、上位頻度ヒストグラムかハイブリッド・ヒストグラムのいずれかになります。
Oracle Database 11gをOracle Database 12cにアップグレードした場合、アップグレード前に作成された高さ調整済ヒストグラムは引き続き使用されます。ただし、ヒストグラムが作成された表の統計をリフレッシュすると、この表の既存の高さ調整済ヒストグラムはデータベースによって置き換えられます。置き換わるヒストグラムのタイプは、NDVと次の基準の両方によって異なります。
サンプリング率がAUTO_SAMPLE_SIZEである場合は、ハイブリッド・ヒストグラムまたは頻度ヒストグラムが作成されます。
サンプリング率がAUTO_SAMPLE_SIZEでない場合は、高さ調整済ヒストグラムまたは頻度ヒストグラムが作成されます。
このシナリオでは、サンプル・スキーマを使用して、高さ調整済ヒストグラムを生成する方法を説明します。
前提条件
このシナリオでは、sh.countries.country_subregion_id列に高さ調整済ヒストグラムを生成することを想定しています。この表には23行が含まれています。
次の問合せは、country_subregion_id列には分布が均一ではない8個の個別値が含まれていることを示しています(出力例も示します)。
SELECT country_subregion_id, count(*)
FROM sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
COUNTRY_SUBREGION_ID COUNT(*)
-------------------- ----------
52792 1
52793 5
52794 2
52795 1
52796 1
52797 2
52798 2
52799 9
高さ調整済ヒストグラムを生成する手順は次のとおりです。
個別値よりも少ないバケット数を指定して、sh.countriesおよびcountry_subregion_id列の統計を収集します。
注意:
高さ調整済ヒストグラムの作成に必要なOracle Database 11gの動作をシミュレートするには、estimate_percentをデフォルト以外の値に設定します。デフォルト以外の値を指定すると、頻度ヒストグラムまたは高さ調整済ヒストグラムが作成されます。
たとえば、次のコマンドを入力します。
BEGIN DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SH'
, tabname => 'COUNTRIES'
, method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID SIZE 7'
, estimate_percent => 100
);
END;
country_subregion_id列のヒストグラム情報を問い合せます。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='COUNTRIES' AND COLUMN_NAME='COUNTRY_SUBREGION_ID'; TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM ---------- -------------------- ------------ --------------- COUNTRIES COUNTRY_SUBREGION_ID 8 HEIGHT BALANCED
個別値の数(8)がバケット数(7)よりも大きく、estimate_percent値がデフォルト以外であるため、オプティマイザは高さ調整済ヒストグラムを選択します。
各個別値が占める行数を問い合せます。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT COUNT(country_subregion_id) AS NUM_OF_ROWS, country_subregion_id
FROM countries
GROUP BY country_subregion_id
ORDER BY 2;
NUM_OF_ROWS COUNTRY_SUBREGION_ID
----------- --------------------
1 52792
5 52793
2 52794
1 52795
1 52796
2 52797
2 52798
9 52799
country_subregion_id列のエンドポイント番号とエンドポイント値を問い合せます。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_HISTOGRAMS
WHERE TABLE_NAME='COUNTRIES'
AND COLUMN_NAME='COUNTRY_SUBREGION_ID';
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 52792
2 52793
3 52795
4 52798
7 52799
図11-4に、高さ調整済ヒストグラムを図示します。この図では、値はコインとして表されています。
バケット番号とエンドポイント番号は同じです。オプティマイザはそれぞれのバケットの最後の行の値をエンドポイント値として記録し、最小値が最初のバケットのエンドポイント値であり、最大値が最後のバケットの最大値であることを確認します。この例では、オプティマイザは、最小値52792がバケットのエンドポイントとなるようにバケット0を追加しています。
オプティマイザは、指定された7個のヒストグラム・バケットに23行を均一に配分する必要があります。そのため、それぞれのバケットに入る行数はおよそ3行です。ただし、オプティマイザは同じエンドポイントでバケットを圧縮します。そのため、バケット1には値52793のインスタンスが2つ、バケット2には値52793のインスタンスが3つ含まれます。さらに、オプティマイザは値52793の5つのインスタンスすべてをバケット2に入れます。同様に、オプティマイザは、バケット5、6および7に、各バケットのエンドポイントとして52799を設定してそれぞれ3つの値を入れるかわりに、値52799の9個のインスタンスすべてをバケット7に入れます。
この例では、現在のエンドポイント番号と前のエンドポイント番号との差が1であるため、バケット3および4には非ポピュラー値が含まれます。オプティマイザは、これらの値のカーディナリティを密度に基づいて計算します。残りのバケットにはポピュラー値が含まれます。オプティマイザは、これらの値のカーディナリティをエンドポイント番号に基づいて計算します。
関連項目:
DBMS_STATS.GATHER_TABLE_STATSプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
USER_TAB_COL_STATISTICSビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
USER_HISTOGRAMSビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
ハイブリッド・ヒストグラムは、高さ調整済ヒストグラムと頻度ヒストグラムの両方の特性を組み合せたものです。「両者の長所を活かす」アプローチにより、オプティマイザは、一部の状況で、より適切な選択性の見積りを得ることができます。
高さ調整済ヒストグラムでは、ほぼポピュラーな値について正確ではない見積りが作成される場合があります。たとえば、1つのバケットのみのエンドポイント値として出現するが、2つのバケットをほぼ占めている値は、ポピュラー値とはみなされません。
この問題を解決するため、ハイブリッド・ヒストグラムでは、複数のバケットを占める値がないように値が配分され、エンドポイント繰返しカウント値が格納されます。これは、ヒストグラム内の各エンドポイント(バケット)について、エンドポイント値が繰り返された回数です。繰返しカウントを使用することで、オプティマイザは、ほぼポピュラーな値についての正確な見積りを得ることができます。
この項の内容は次のとおりです。
バケツに分けられたコインの例えにより、エンドポイント繰返しカウントの仕組みを示します。
次の図は、値が低いものから高いものへ分類されたcoins列を示しています。
DBMS_STATS.GATHER_TABLE_STATSのmethod_opt引数をFOR ALL COLUMNS SIZE 3に設定して、この表の統計を収集します。この場合、オプティマイザはまず、次の図に示すようにcoins列内の値を3つのバケットにグループ化します。
バケット境界により、値の一部のオカレンスがあるバケットに入り、別のオカレンスが別のバケットに入るように、値が分けられた場合、オプティマイザは、値のすべてのオカレンスが含まれるように、バケット境界(および他のすべての続くバケット境界)を移動します。たとえば、オプティマイザは、値5を、すべてが最初のバケットに入るように移動し、値25を、すべてが2番目のバケットに入るように移動します。
エンドポイント繰返しカウントは、対応するバケットのエンドポイント(右のバケット境界にある値)が繰り返される回数を測定します。たとえば、最初のバケットで、値5は3回繰り返されています。したがって、エンドポイント繰返しカウントは3です。
高さ調整済ヒストグラムでは、ハイブリッド・ヒストグラムほどの情報量は格納されません。繰返しカウントを使用することで、オプティマイザは、エンドポイント値のオカレンスがいくつ存在するかを正確に特定できます。たとえば、オプティマイザは、値5は3回、値25は4回、値100は2回出現していると認識できます。頻度情報は、オプティマイザがより適切なカーディナリティの見積りを生成するのに役立ちます。
ハイブリッド・ヒストグラムと上位頻度ヒストグラムを区別するための唯一の基準は、上位n個の頻度の値が内部しきい値pより小さいことです。
「Oracle Databaseによるヒストグラム・タイプの選択方法」の論理図に示すように、次の基準を満たす場合に、データベースはハイブリッド・ヒストグラムを作成します。
NDVがnより大きい(nはヒストグラム・バケットの数(デフォルトは254)です)。
上位頻度ヒストグラムの基準が適用されない。
これは、上位nの頻出値によって占められている行の割合がしきい値pより小さいことを示す別の方法です。ここで、pは(1-(1/n))*100です。「上位頻度ヒストグラムの基準」を参照してください。
DBMS_STATS統計収集プロシージャのestimate_percentパラメータがAUTO_SAMPLE_SIZEに設定されている。
ユーザーが独自の率を指定した場合、データベースでは、頻度ヒストグラムまたは高さ調整済ヒストグラムが作成されます。
関連項目:
このシナリオでは、サンプル・スキーマを使用して、ハイブリッド・ヒストグラムを生成する方法を説明します。
前提条件
このシナリオでは、sh.products.prod_subcategory_id列にハイブリッド・ヒストグラムを生成することを想定しています。この表には72行が含まれています。prod_subcategory_id列には、22個の個別値があります。
ハイブリッド・ヒストグラムを生成する手順は次のとおりです。
10個のバケットを指定して、sh.productsおよびprod_subcategory_id列の統計を収集します。
たとえば、次のコマンドを入力します。
BEGIN DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SH'
, tabname => 'PRODUCTS'
, method_opt => 'FOR COLUMNS PROD_SUBCATEGORY_ID SIZE 10'
);
END;
各個別値が占める行数を問い合せます。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT COUNT(prod_subcategory_id) AS NUM_OF_ROWS, prod_subcategory_id
FROM products
GROUP BY prod_subcategory_id
ORDER BY 1 DESC;
NUM_OF_ROWS PROD_SUBCATEGORY_ID
----------- -------------------
8 2014
7 2055
6 2032
6 2054
5 2056
5 2031
5 2042
5 2051
4 2036
3 2043
2 2033
2 2034
2 2013
2 2012
2 2053
2 2035
1 2022
1 2041
1 2044
1 2011
1 2021
1 2052
22 rows selected.
この列には22個の個別値があります。バケットの数(10)が22よりも小さいので、オプティマイザは頻度ヒストグラムを作成できません。オプティマイザはハイブリッド・ヒストグラムと上位頻度ヒストグラムの両方を検討します。上位頻度ヒストグラムに該当するには、上位10の頻出値によって占められている行の割合がしきい値p以上である必要があります。この場合、pは(1-(1/10))*100、つまり90%です。しかし、この場合、上位10の頻出値が占めているのは72行のうち54行、つまり全体の75%にしかなりません。したがって、上位頻度ヒストグラムの基準には該当しないため、オプティマイザはハイブリッド・ヒストグラムを選択します。
country_subregion_id列のヒストグラム情報を問い合せます。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='PRODUCTS' AND COLUMN_NAME='PROD_SUBCATEGORY_ID'; TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM ---------- ------------------- ------------ --------- PRODUCTS PROD_SUBCATEGORY_ID 22 HYBRID
country_subregion_id列のエンドポイント番号、エンドポイント値およびエンドポイント繰返しカウントを問い合せます。
たとえば、次の問合せを使用します(出力例も示します)。
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_REPEAT_COUNT
FROM USER_HISTOGRAMS
WHERE TABLE_NAME='PRODUCTS'
AND COLUMN_NAME='PROD_SUBCATEGORY_ID'
ORDER BY 1;
ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_REPEAT_COUNT
--------------- -------------- ---------------------
1 2011 1
13 2014 8
26 2032 6
36 2036 4
45 2043 3
51 2051 5
52 2052 1
54 2053 2
60 2054 6
72 2056 5
10 rows selected.
高さ調整済ヒストグラムでは、オプティマイザは、指定された10個のヒストグラム・バケットに72行を均一に配分する必要があります。そのため、それぞれのバケットに入る行数はおよそ7行です。これはハイブリッド・ヒストグラムなので、オプティマイザは、1つの値が1個より多くのバケットを占めないように値を分配します。たとえば、オプティマイザは値2036の一部のインスタンスを別のバケットに入れることはしません。すべてのインスタンスをバケット36に入れます。
エンドポイント繰返しカウントは、バケット内で最も高い値が繰り返される回数を示します。これらの値のエンドポイント番号と繰返しカウントを使用することによって、オプティマイザはカーディナリティを見積ることができます。たとえば、バケット36には値2033、2034、2035および2036のインスタンスが格納されています。エンドポイント値2036のエンドポイント繰返しカウントは4です。そのため、オプティマイザはこの値に4つのインスタンスが存在することを認識しています。2033など、エンドポイントではない値の場合、オプティマイザは密度を使用してカーディナリティを見積ります。
関連項目:
DBMS_STATS.GATHER_TABLE_STATSプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
USER_TAB_COL_STATISTICSビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
USER_HISTOGRAMSビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
