複数のインデックスを連携させることはできますか?

database indexing optimization oracle
複数のインデックスを連携させることはできますか?

“foo”と “bar”という2つのフィールドを持つデータベーステーブルがあるとします。 どちらも固有のものではありませんが、それぞれ索引付きです。 ただし、一緒に索引付けされるのではなく、それぞれ個別の索引があります。

ここで、SELECT * FROM sometable WHERE foo = ‘hello’ AND bar = ‘world’などのクエリを実行したとします。 ‘my table fooが’ hello ‘の膨大な行数と、どのバーの少数の行数「世界」です。

そのため、データベースサーバがフードの下で行う最も効率的な方法は、barが ‘world’であるすべてのフィールドを検索し、fooが ‘hello’である行だけを返すようにbarインデックスを使用することです。 これは `O(n)`で、nはbarが ‘world’である行の数です。

ただし、foインデックスを使用して結果を検索した場合は、このプロセスが逆になる可能性があります。 これは `O(m)`になります。ここで、mはfooが ‘hello’である行数です。

それで、Oracleはここで効率的に検索するのに十分賢いですか? 他のデータベースはどうですか? それとも、適切な順序で検索するようにクエリで指定できる方法はありますか。 おそらく、「WHERE」句の最初に「bar = ‘world」を置くことによって?

  14  2


ベストアンサー

Oracleは、ほとんど確実に、最も選択的なインデックスを使用してクエリを操作します。そのことをEXPLAIN PLANで確認できます。

さらに、Oracleは2つの方法で2つの索引の使用を組み合わせることができます。つまり、Bツリー索引をビットマップに変換してそれらに対してビットマップANd操作を実行するか、または2つの索引によって返されるROWIDに対してハッシュ結合を実行できます。

ここで考慮すべき重要な点の1つは、照会されている値間の相関関係です。 foo = ‘hello’が表の値の80%を占め、bar = ‘world’が10%を占める場合、Oracleは問合せが表の行の0.8 * 0.1 = 8%を返すと推定します。 ただし、これは正しくない場合があります。値の相関関係に応じて、クエリは実際には10%のrwまたは0%の行を返します。 現在では、テーブル全体のそれらの行の分布によっては、それらを見つけるためにインデックスを使用するのは効率的ではないかもしれません。 必要な行を取得するために70%または表ブロックにアクセスする必要があるかもしれません(「クラスタリング係数」の場合はGoogle)。その場合、見積もりが正しい場合、Oracleは完全な表スキャンを実行します。

11gでは、この状況に役立つように複数列の統計を収集できます。 9iと10gでは、動的サンプリングを使用して、取得する行数を非常に正確に見積もることができます。

実行計画を立てるには、次のようにします。

SELECTの計画の説明*どこかにWHERE foo = 'hello' AND bar = 'world' /テーブルから選択*(dbms_xplan.display)/

それとは対照的に:

SELECTの計画の説明/ * dynamic_sampling(4)* / * FROMの場合WHERE foo = 'hello' ANDバー= '世界' /テーブルから選択*(dbms_xplan.display)/

11


はい。Oracleへの問合せで「ヒント」を渡すことができます。 これらのヒントはデータベースへのコメント( “/ * HINT * /”)に偽装されており、主にベンダー固有のものです。 そのため、あるデータベースに対するヒントが他のデータベースでは機能しません。

ここではインデックスヒントを使用します。これは、小さなテーブルに対する最初のヒントです。 ここを参照してください。

一方、これら2つのフィールドを頻繁に検索する場合は、これら2つのフィールドにインデックスを作成しないでください。 私は正しい構文を持っていませんが、それは次のようになります

いくつかのCREATE INDEX IX_BAR_AND_FOO(bar、foo);

このようにデータ検索はかなり速いはずです。 そして、連結がユニークである場合は、あなたは単に非常に速くなるべきユニークなインデックスを作成します。

3


Eli,

あなたが書いたコメントで:

_
残念ながら、それぞれに独自のインデックスを持つ多数の列を持つテーブルがあります。 ユーザーは任意のフィールドの組み合わせを照会できるため、各フィールドの組み合わせに対して効率的にインデックスを作成することはできません。 しかし、インデックスが必要なフィールドが2つしかない場合は、2つのインデックスを使用するというあなたの提案に完全に同意します。 –エリ・コートライト(9月29日15:51)
_

これは実際にはかなり重要な情報です。 質問をするとき、プログラマーは時々自分自身を際立たせます。 彼らは問題点を精巧な点に留めようとしますが、非常に単純化しすぎて最善の答えを得るのを逃します。

このシナリオがまさにビットマップインデックスが発明された理由です。つまり、未知の列のグループがwhere句で使用される時間を処理するためです。

万が一のために、BMIは基数の低い列のみを対象としており、あなたのケースには適用されない可能性があると誰かが言っています。 低はおそらくあなたが思うほど小さくはありません。 唯一の真の問題は、表に対するDMLの並行性です。 これが機能するためには、シングルスレッドまたはレアでなければなりません。

3


_
それで、Oracleはここで効率的に検索するのに十分賢いですか?
_

簡単な答えは「おそらく」です。 各データベースベンダーには、クエリオプティマイザの最適化に取り組んでいる非常に優秀な人材がたくさんいるので、おそらく考えていないことをやっているのでしょう。 そしてあなたが統計を更新すれば、それはおそらくさらにもっとするでしょう。

2


まず最初に、あなたがいい、普通の、標準的なb *ツリーインデックスについて話していると思います。 ビットマップインデックスに対する答えは根本的に異なります。 そしてOracleには答えを変えるかもしれないし変えないかもしれない様々なタイプのインデックスのためのたくさんのオプションがあります。

最低限、オプティマイザが特定の条件の選択性を決定することができる場合、オプティマイザはより選択性の高いインデックス(すなわち バーのインデックス) しかし、データが歪んでいる場合(列バーにN個の値がありますが、特定の値の選択性がデータの1 / Nより実質的に大きいか小さい場合)、列にヒストグラムを表示する必要があります。どの値が多かれ少なかれありそうなオプティマイザ。 また、Oracleのバージョンによっては、バインド変数を使用している場合(すべての優れたOLTP開発者が推奨するとおり)、バインド変数のピークに問題がある可能性があります。

場合によっては、Oracleは2つのb *ツリー索引をビットマップにその場で変換し、両方の索引を使用して取得する必要がある行を見つけることもできます。 しかし、これはかなり変わったクエリプランです。特に、1つの列の選択性が高い2つの列しかない場合は特にそうです。

2


Oracleが問合せ計画を表示するようにして、最初にどの索引が使用されるかを正確に確認できるようにすることもできます。

1


どのインデックスを使用するかについてのヒントを提供できます。 私はOracleに慣れていませんが、MysqlではUSE | IGNORE | FORCE_INDEXを使用することができます(詳細はhttp://dev.mysql.com/doc/refman/5.1/en/index-hints.htmlを参照してください)。 ) 最高のパフォーマンスを得るためには、結合インデックスを使うべきです。

1


最善の方法は、barのインデックスにfooを追加するか、fooのインデックスにbarを追加することです(あるいはその両方)。 fooのインデックスにbarのインデックスも含まれている場合、そのインデックスの現在の使用において、そのインデックスレベルがfooインデックスの有用性に影響を与えることはなく、またインデックスを維持するパフォーマンスに明らかに影響を与えません。例のようにクエリを最適化する際に使用する情報。

1


それよりはましだ。

インデックスシークは、全表スキャンよりも常に高速です。 そのため、Oracle(およびそのことについてはSQLサーバー)の背後では、まず両方のインデックスの行の範囲を特定します。 次に、どの範囲が短いかを調べ(内部結合であることを確認して)、2つのうち大きい方と一致するものを見つけるために短い範囲を繰り返します。

1


タイトルとURLをコピーしました