Database
技術記事
Ask Tom
テクノロジー： Ask Tom
クラスタ化係数とキーの検証についてTom Kyte著オラクルの技術者が、統計の差異と外部キーの検証方法について説明します。 クラスタ化係数を確認するために次のような表と索引を作成したのですが、いくつか疑問があります。 create table t (a number); USER_INDEXESを問い合わせると、LEAF_BLOCKSの数は2,226でCLUSTERING_FACTORは1,516でした。この表をドロップし、CHAR(8)データ型を使用して表を作成し直して同じ索引を作成したところ、LEAF_BLOCKSの数は27,856でクラスタ化係数は1,916,062に増えていました。 同じデータが含まれているのに、2つの表のCLUSTERING_FACTORがこんなに異なる理由を教えてください。異なるのはデータ型だけなのですが。 ご質問に回答する前に、クラスタ化係数統計の意味と重要性を説明したいと思います。 この統計は、索引の相対的な効率を判断して問合せを最適化するときに、オプティマイザによって使用されます。 要するに、索引のクラスタ化係数は、表に含まれるすべての行を索引を経由して索引順で読み取る場合に、データベースが実行すると予想されるI/Oの回数の測定値です。 ディスク上にある表の行が索引キーとほぼ同じ順序でソートされていれば、索引を経由して表全体を読み取るときにデータベースが表に対して実行するI/Oの回数は最小になります。 なぜなら、索引キーが次に必要とする行は、表ブロック内でも次の行にある可能性が高いためです。 この問合せでは、1行また1行と検索するときに表を最初から最後まで移動したりはしません。当然ながら、行はブロック内で隣り合っているからです。 逆に、表に含まれる行が索引キーと同じ順序でディスク上に並んでいない（データがばらばらに存在する）場合は、表に対するI/Oの実行回数が最大（表に含まれるすべての行につき1回のI/O）になる傾向があります。 というのも、索引を経由してデータベースをスキャンするとき、次に必要な行が前の行と同じブロックにない可能性が高いためです。 その場合は、前のブロックを破棄して別のブロックをバッファ・キャッシュから取得する必要があります。 したがってこの問合せでは、存在する行の数と同じ回数だけ、すべてのブロックをバッファから読み取ることになります。 そのため、表と索引キーがほぼ同じ順序になっていれば、クラスタ化係数は表に含まれるブロックの数に近くなり、非常に大規模な索引レンジ・スキャンを実行する場合や多数の行を表から取得する場合は、索引が有効になります。 一方、データが無秩序に存在していれば、クラスタ化係数は表に含まれる行の数に近くなります。また、表に含まれる行の数はブロックの数より少なくとも大きさの面で多いのが普通であるため、多数の行を返す場合は、索引では効率が低くなります。 たとえば、表のサイズが100ブロックで、1ブロックに100行が含まれているとすると、クラスタ化係数が（ブロック数とほぼ同じ）100の索引では、200行を取得するとき表に対して約2回のI/Oを実行することになります。 なぜなら、行1を取得するために読み取った最初の表ブロックには、おそらく行2～100も存在するからです。そのため、この問合せでは、表ブロックを1回読み取ることで最初の100行を取得できることになります。 行101～200についても同様の処理が行われます。索引のクラスタ化係数が10,000（表に含まれる行の数）であれば、表に対して実行する必要のあるI/Oの数は、たとえブロックが100しかなかったとしても、およそ200回になります。 その理由は、索引に含まれる最初の行と2番目の行が異なるブロックに存在し、2番目の行と3番目の行も異なるブロックに存在し、4番目の行以降も同様というような場合には、1つの表ブロックから1度に2行以上を取得できない可能性が高くなるためです。 この現象は簡単に観察できます。 ALL_OBJECTSをコピーして2つの表に入れます。 ここで使用するSTAGE表は、初心者のために用意されているALL_OBJECTSをコピーしただけのものです。
SQL> create table organized
2 as
3 select x.*
4 from (select * from stage
order by object_name) x
5 /
Table created.
SQL> create table disorganized
2 as
3 select x.*
4 from (select * from stage
order by dbms_random.random) x
5 /
Table created.
これら2つの表を作成するときにORDER BY文を使用している点に注目してください。 最初の表の場合は、データをOBJECT_NAMEでソートしてから表にロードしています。 ORGANIZED表に対して全表スキャンを実行するのであれば、ORDER BYがなくてもOBJECT_NAME列はほぼソートされた状態で画面に表示されます（ただし、データをソートする必要がある場合は、問合せにORDER BYを含める必要があります）。 一方、DISORGANIZED表の作成時には、ランダムな値でソートしています。つまり、データをごちゃごちゃにしたのです。 この表に対して全表スキャンを実行したとすると、OBJECT_NAME値は任意の順序で表示されるはずです。つまり、Nで始まるオブジェクトが最初に表示され、次はZ、その次はA、そしてQ、B、と続いて再度Zで始まるオブジェクトが表示される、という具合です。 ここで、これら2つの表に索引付けを行って統計を取り、その内容を確認します。リスト1をご覧ください。 コード・リスト1：索引の作成、統計の生成、および情報の表示 SQL> create index organized_idx on organized(object_name); Index created. SQL> create index disorganized_idx on disorganized(object_name); Index created. SQL> begin 2 dbms_stats.gather_table_stats 3 ( user, 'ORGANIZED', 4 estimate_percent => 100, 5 method_opt=>'for all indexed columns size 254' 6 ); 7 dbms_stats.gather_table_stats 8 ( user, 'DISORGANIZED', 9 estimate_percent => 100, 10 method_opt=>'for all indexed columns size 254' 11 ); 12 end; 13 / PL/SQL procedure successfully completed. SQL> select table_name, blocks, num_rows from user_tables 2 where table_name like '%ORGANIZED' order by 1; TABLE_NAME BLOCKS NUM_ROWS ———————————————— ——————— ——————— DISORGANIZED 1064 72839 ORGANIZED 1064 72839 SQL> select table_name, index_name, clustering_factor 2 from user_indexes 3 where table_name like '%ORGANIZED' order by 1; TABLE_NAME INDEX_NAME CLUSTERING_FACTOR —————————————— —————————————— ———————————————— DISORGANIZED DISORGANIZED_IDX 72760 ORGANIZED ORGANIZED_IDX 1038 リスト1で確認できるとおり、両方の表は行数とブロック数がいずれも同じです。 これは想定どおりです。2つの表にはまったく同じ行が含まれており、異なるのは順序だけです。 ところが、クラスタ化係数を見ると、両者の間に大きな相違があります。 ORGANIZED表の索引のクラスタ化係数は、表に含まれるブロックの数にきわめて近く、対するDISORGANIZED表の索引のクラスタ化係数は、表に含まれる行の数とほぼ同じです。 繰り返しますが、このクラスタ化係数という測定基準は、表に含まれるすべての行を索引を経由して読み取るために実行されるI/Oの回数を表す測定値です。 この事実を検証するには、実際に索引を経由して表のすべての行を読み取る問合せを、トレースを有効にして実行すればよいのです。 そのためには、INDEXヒントを使用してオプティマイザによる索引の使用を強制し、索引に含まれないnullable列に存在するNULL以外の値をカウントさせます。 こうすると、一行ごとにデータベースを索引から表にアクセスさせることができます。
SQL> select /*+ index( organized
organized_idx) */
2 count(subobject_name)
3 from organized;
COUNT(SUBOBJECT_NAME)
——————————————————————
658
SQL> select /*+ index( disorganized
disorganized_idx) */
2 count(subobject_name)
3 from disorganized;
COUNT(SUBOBJECT_NAME)
—————————————————————————————
658
一行ごとに索引を経由して読み取っていることを、TKPROFレポートで確認してみましょう。結果をリスト2に示します。 コード・リスト2：両方の表の索引スキャンに関する情報 select /*+ index( organized organized_idx) */ count(subobject_name) from organized Row Source Operation ——————————————————————————————————————————————————————————————————— SORT AGGREGATE (cr=1401 pr=1038 pw=0 time=307733 us) TABLE ACCESS BY INDEX ROWID ORGANIZED (cr=1401 pr=1038 pw=0 tim... INDEX FULL SCAN ORGANIZED_IDX (cr=363 pr=0 pw=0 time=53562 ... select /*+ index( disorganized disorganized_idx) */ count(subobject_name) from disorganized Row Source Operation ————————————————————————————————————————————————————————————————————— SORT AGGREGATE (cr=73123 pr=1038 pw=0 time=535990 us) TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=73123 pr=1038 pw=0 t... INDEX FULL SCAN DISORGANIZED_IDX (cr=363 pr=0 pw=0 time=47207 us … リスト2で確認できるとおり、ORGANIZED表へのI/Oは索引に対して363回実行されました（INDEX FULL SCAN ORGANIZED_IDX行ソースでcr=363となっています）。この問合せで実行されたI/Oの合計1,401から363を引くと1,038になります。この値はまさに、この索引のクラスタ化係数です。 同様に、DISORGANIZED索引についても同じ分析をしてみると、表に対するI/Oは73,123 – 363 = 72,760で、この値もこの索引のクラスタ化係数になっているとわかります。 つまり、一方の表では、合計1,401回I/Oを実行すると、もう一方の表とまったく同じデータが取得されますが、もう一方の表では73,123回のI/Oが必要だったのです。 多数の行を取得する場合は、明らかに一方の索引のほうがもう一方より有効になります。 索引を経由して読み取る表のブロックが1,038を超える場合は、索引を使用するのではなく全表スキャンを実行するほうがよいに違いありません。 両方の表に対するいくつかの問合せで自動トレースを使用すれば、この事実も観察できます。それを示しているのがリスト3です。 コード・リスト3：2つの表で索引を使用する場合のコストの比較 SQL> select * from organized where object_name like 'F%'; ———————————————————————————————————————————————————————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost | ———————————————————————————————————————————————————————————————————————————— | 0 | SELECT STATEMENT | | 149 | 14602 | 6 | | 1 | TABLE ACCESS BY INDEX ROWID| ORGANIZED | 149 | 14602 | 6 | |* 2 | INDEX RANGE SCAN | ORGANIZED_IDX | 149 | | 3 | ———————————————————————————————————————————————————————————————————————————— SQL> select * from disorganized where object_name like 'F%'; —————————————————————————————————————————————————————————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost | —————————————————————————————————————————————————————————————————————————————— | 0 | SELECT STATEMENT | | 149 | 14602 | 152 | | 1 | TABLE ACCESS BY INDEX ROWID| DISORGANIZED | 149 | 14602 | 152 | |* 2 | INDEX RANGE SCAN | DISORGANIZED_IDX | 149 | | 3 | リスト3で確認できるとおり、いずれの計画でも同じ数の行（149行）が返されると予想されています。 両方の計画とも、索引レンジ・スキャンを使用することになっています。 ところが、2つの計画はコストがまったく異なっています。 一方は6という低コストで、もう一方ははるかに高く、152です。両方の計画とも、同じデータを含む2つの表からまったく同じ一連の行を選択するのに、これほど異なるのです。 コストに差が出る理由は簡単に説明がつきます。 オプティマイザでは、予想されるI/Oの数とCPUコストの相関としてコスト列の値を計算します。 この単純な問合せの場合、CPUコストは無視できるため、コストの大半はI/Oの数だけです。 最初の計画を見ていくと、ORGANIZED表と索引に索引を使用する場合のコストは3であることがわかります。索引に対するI/Oは約3回なので、これは理にかなっています。 問合せは、ルート・ブロック、ブランチの順にアクセスし、ほとんどの場合リーフ・ブロックにもアクセスします。 その後、表に対してさらに約3回のI/Oを実行します。なぜなら、必要な行はすべて2、3のデータベース・ブロック上で隣り合っているからです。こうして、合計コストは6になります。一方、DISORGANIZED索引については計算が少し異なります。 この計画でも、索引に対しては同じ3回のI/Oが発生します。この点は変わりません。ただし、必要となる表の行は隣り合っていないため、オプティマイザは行を取得するたびに表に対してI/Oを実行する必要があると推定します。したがって、149行を取得する場合の推定コストは149行+3回のI/Oで152になります。 少しだけ問合せを変更すると、問合せ計画に対してどのような影響があるかを確認できます。その内容をリスト4に示します。 コード・リスト4：問合せの変更によるコストの変化 SQL> select * from organized where object_name like 'A%'; ———————————————————————————————————————————————————————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost | ———————————————————————————————————————————————————————————————————————————— | 0 | SELECT STATEMENT | | 1825 | 174K | 39 | | 1 | TABLE ACCESS BY INDEX ROWID| ORGANIZED | 1825 | 174K | 39 | |* 2 | INDEX RANGE SCAN | ORGANIZED_IDX | 1825 | | 12 | ———————————————————————————————————————————————————————————————————————————— SQL> select * from disorganized where object_name like 'A%'; ————————————————————————————————————————————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost | ————————————————————————————————————————————————————————————————— | 0 | SELECT STATEMENT | | 1825 | 174K | 291 | |* 1 | TABLE ACCESS FULL| DISORGANIZED | 1825 | 174K | 291 | ————————————————————————————————————————————————————————————————— リスト4で確認できるとおり、推定行数は1,825に急増しますが、ORGANIZED表では引き続き索引が使用されます。 問合せのコストは39です。レンジ・スキャンによる索引へのI/Oが12で、表に対するI/Oがさらに27回発生すると推定されています。 これはつじつまが合っています。なぜなら、ALL_OBJECTSの行は70前後が1つのデータベース・ブロックに収まるサイズであり、1,825行を保持するのに約27ブロックを使用するからです。 DISORGANIZED表を見ると、推定行数は同じなのに計画はまったく異なることが分かります。 オプティマイザでは、索引を使用せずに全表スキャンを実行する選択がなされています。 索引を使用した場合のコストはどのくらいだったのでしょうか。 リスト4の結果を見ると、索引ステップ（INDEX RANGE SCAN）のコストは12と推定されていたことがわかります。また、索引のクラスタ化係数は表に含まれる行の数に近いことから、取得する必要のあるすべての行について1回ずつI/Oを実行する必要があると仮定できます。ということは、表に対してI/Oを1,825回実行する必要があり、合計コストは1,837になります。全表スキャンを実行したほうが低コストだと推定されたわけです。 実のところ、オプティマイザがいつこの索引の使用をやめるかを解明するための情報は十分にあります。 全表スキャンのコストは291だとわかっており、この表に対して索引を使用する問合せ計画のコストは、推定行数と問合せコストを合計したものと同じかそれ以上になることもわかっています。 ですから、問合せで285前後の行が取得される場合は、索引を使用するコストがおそらく5または6前後になり、表アクセスのコストは約291であるため、全表スキャンと索引スキャンのコストは互角になります。 推定行数が285を上回れば、コストに関係なくオプティマイザでは全表スキャンが実行されることになります。 ORGANIZED表から何千行も取得するコストは、DISORGANIZED表から数百行を取得するコストより低いのです。 また、クラスタ化係数は通常、索引レンジ・スキャンのコストが示している値と同じです。 さて、クラスタ化係数とは何か、そしてクラスタ化係数に留意する理由がわかったところで、最初の質問に回答しましょう。 なぜ、2つの表（NUMBERを使用して作成した表とCHAR(8)を使用して作成した表）のクラスタ化係数に大きな差があったのでしょうか。 両方の表のデータはまったく同じ順序でディスク上に存在します。ですから、2つの表のクラスタ化係数は同じになるでしょうか。 いいえ、同じにはなりません。同じにはなり得ないのです。理由は2つあります。 まず、NUMBERを使用した表とCHAR(8)を使用した表のどちらが大きいか考えてみてください。 索引リーフ・ブロックの数は、CHAR索引の場合のほうがNUMBER索引の場合より多いことが分かっています（数値をCHARまたはVARCHAR2に格納するのは、どのような場合であってもお勧めできません。理由はたくさんありますが、その1つは領域です）。 CHAR(8)列を含む表は、NUMBER列を含む表よりはるかに大きくなります。 そして、クラスタ化係数は索引を経由して表全体を読み取るために1回のレンジ・スキャンの中で実行されるI/Oの数と定義されており、合理的に考えれば1ブロックには複数行が存在するため、必然的にI/Oの数は表に含まれるブロックの数以上になります。 ということは、論理的に考えて、ある表が別の表よりはるかに大きい場合、大きい表のクラスタ化係数は小さい表のクラスタ化係数より大きくなるに決まっています。 これが、CHAR(8)を含む表のほうが明らかに大きくなり、それゆえにクラスタ化係数も増加する、という問題を引き起こしている原因の1つです。 ただし、これだけが原因ではありません。 残りの原因を理解するには、データを見る必要があります。 質問者は、2つの表に同じデータをロードしたと思い込んでいます。1回目は（適切に）NUMBERとして格納し、2回目は（完全に間違った）文字列として格納しました。 ところが、同じにはならなかったのです。できあがったのは全く異なる2つのデータセットでした。 次の結果について考えてみましょう。
SQL> with data (r)
2 as
3 (select 1 r from dual union all
4 select r+1 from data
5 where r<= 1000 )
6 select * from data
7 order by to_char(r);
R
——————————————
1
10
100
1000
1001
101
102
103
104
105
106
107
108
109
11
110
1、10、100、1000 . . . が最初に返されていることを確認してください。 索引に含まれるデータは、最初が1、10、100、および1000であったはずです。 では、この最初のリーフ・ブロックを処理するために、表に対して何回のI/Oを実行する必要があったでしょうか。 表に含まれるデータは、1、2、3、4、5. . .でした。 データは番号順に生成されたため、表は番号順になっています。索引は文字列順です。 文字列に含まれる数値は、NUMBERデータ型に含まれる数値とはまったく異なる方法でソートされます。 ですから、これが2つ目の問題の原因です。 表に含まれるデータは、索引に含まれるデータと同じ方法でソートされていないのです。 定義によれば、これによりクラスタ化係数が大幅に増加します。 次の理由により、CHAR(8)表のクラスタ化係数のほうが高くなります。
外部キーが外部キーでなくなる状況私は毎日、Oracle Databaseについて新しいことを学んだり学び直したりしています。 本当です。 たとえば、少し前にセミナーのためにセルビアのベルグラードに行ったとき、かつては知っていたのにすっかり忘れ去っていたことを、ある参加者が思い出させてくれました。 それは、外部キーと恐るべきNULL値に関係することでした。 読者の多くは、次の例を実行するのは無理だと思うかもしれませんが、実行できるのです。 では、表の作成から始めましょう。 SQL> create table p 2 ( x int, 3 y int, 4 z int, 5 constraint p_pk primary key(x,y) 6 ) 7 / Table created. SQL> create table c 2 ( x int, 3 y int, 4 z int, 5 constraint c_fk_p 6 foreign key (x,y) 7 references p(x,y) 8 ) 9 / Table created. 普通の親子関係のように見えます。 Pに親行が1つ存在してさえいれば、Cにも行が存在するでしょう。ところが、これが本当ならば、どうして次のようなことが起きるのでしょうか。
SQL> select count( x||y ) from p;
COUNT(X||Y)
———————————————
0
SQL> select count( x||y ) from c;
COUNT(X||Y)
———————————————
1
Pにはレコードがありません。0件です。 Cには少なくとも1件のレコードがあり、そのレコードはNULL以外の外部キーを持っています。 どうなっているのでしょうか。 この現象には、NULLと外部キー、それに複合外部キーのデフォルトであるMATCH NONEルールが関係しています。 NULLが許可されている外部キーと外部キーが複合キーである場合は、一部の外部キー属性のみがNULLでないという状況に気を付ける必要があります。 たとえば、上の手品を成功させるために、次のような挿入を行いました。 SQL> insert into c values ( 1, null, 0 ); 1 row created. 一部がNULLの外部キーはデータベースで検証できません。 複合外部キーにMATCH FULLルールを適用するには、次のようにして表に制約を追加します。 SQL> alter table c add constraint check_nullness 2 check ( ( x is not null 3 and y is not null ) or 4 ( x is null 5 and y is null ) ) 6 / Table altered. この制約により、確実に次のいずれかの状態になります。
この制約が設定されている限り、外部キーは読者の皆さんが考えているような動作をします。
Tom KyteはOracleのServer Technologies部門に籍を置くデータベース・エバンジェリストで、1993年からOracleに勤務しています。Expert Oracle Database Architecture（2005年、2010年Apress刊）やEffective Oracle by Design（2003年Oracle Press刊）などの著書があります。 |
Printer View