応答時間短縮のための問合せの最適化

10.2.1 問合せの応答時間に影響するその他の要因

問合せの応答時間に影響を与える要因として、次のものが考えられます。

表統計の収集
メモリー割当て
ソート
元表に存在するラージ・オブジェクト(LOB)列
パーティション化
並列度
問合せで語句を拡張する回数

関連項目:

「問合せのパフォーマンスに関するFAQ(よくある質問)」

10.2.2 FIRST_ROWS(n)ヒントによるORDER BY問合せの応答時間の短縮

ORDER BY問合せの最初のいくつかの行が必要な場合は、コストベースであるFIRST_ROWS(n)ヒントを使用することをお薦めします。

ノート:

FIRST_ROWS(n)ヒントはコストベースであるため、使用する前に表に関する統計を収集しておくことをお薦めします。

FIRST_ROWS(n)ヒントは、最初のn行をできるだけ短時間で受け取る必要がある場合に使用します。たとえば、次のPL/SQLブロックでは、カーソルを使用して問合せの最初の10ヒットを取り出し、FIRST_ROWS(n)ヒントを使用して応答時間が短縮されるように最適化しています。

declare
cursor c is 

select /*+ FIRST_ROWS(10) */ article_id from articles_tab
   where contains(article, 'Omophagia')>0 order by pub_date desc;

begin

for i in c
loop
insert into t_s values(i.pk, i.col);
exit when c%rowcount > 11;
end loop;

end;
/

cカーソルは、omophagiaというワードを含むソートされたROWIDを戻すSELECT文です。コードがカーソル内をループして、最初の10行を取り出します。取り出された行は、一時t_s表に格納されます。

FIRST_ROWS(n)ヒントを指定した場合、オプティマイザは上位n個のヒットを戻すコストが低い場合に、Oracle Text索引に対してROWIDをスコア順で戻すように指示します。

このヒントがない場合、Oracle Text索引によりCONTAINS述語を満たすすべての行がソートされていない順序で戻された後、Oracle DatabaseによりROWIDがソートされます。結果セット全体を取り出すと時間がかかります。

この問合せでは、最初の10ヒットのみが必要なため、ヒントを使用することでパフォーマンスが向上します。

ノート:

FIRST_ROWS(n)ヒントは、問合せで最初の数個のヒットのみが必要な場合に使用します。結果セット全体が必要な場合は、パフォーマンスの低下につながるため、このヒントは使用しないでください。

10.2.3 DOMAIN_INDEX_SORTヒントを使用した応答時間の短縮

また、DOMAIN_INDEX_SORTヒントを使用して応答時間を短縮することもできます。FIRST_ROWS(n)の場合と同じように、応答時間が短縮されるように問合せが最適化されている場合は、Oracle Textは最初の数行を最短時間で戻します。

たとえば、このヒントは次のように使用できます。

select /*+ DOMAIN_INDEX_SORT */ pk, score(1), col from ctx_tab 
            where contains(txt_col, 'test', 1) > 0 order by score(1) desc;

ただし、このヒントはルールベースのみです。つまり、Oracle Textでは、ORDER BY句を満たす索引を常に選択します。このヒントにより、CONTAINS句の選択性が非常に高い問合せでは、パフォーマンスが最善にはならないことがあります。そのような場合は、完全にコストベースのFIRST_ROWS(n)ヒントを使用することをお薦めします。

10.2.4 ローカル・パーティションCONTEXT索引を使用した応答時間の短縮

データをパーティション化し、ローカル・パーティション索引を作成すると、問合せのパフォーマンスが向上します。パーティション表では、各パーティションに独自の索引表セットがあります。実際には複数の索引がありますが、結果が必要に応じて組み合され、最終的な結果セットが生成されます。

LOCALキーワードを使用して、CONTEXT索引を次のように作成します。

CREATE INDEX index_name ON table_name (column_name) 
INDEXTYPE IS ctxsys.context
PARAMETERS ('...')
LOCAL

パーティション化された表および索引を使用すると、次のような問合せのパフォーマンスが向上します。

パーティション・キー列の範囲検索: この問合せは、パーティション・キーでもある列の特定の値範囲に検索を限定します。たとえば、日付範囲に対する次の問合せについて考えます。
```
SELECT storyid FROM storytab WHERE CONTAINS(story, 'oliver')>0 and pub_date BETWEEN '1-OCT-93' AND '1-NOV-93';
```
日付範囲が制限されている場合は、1つのパーティションを検索するだけで問合せが満たされる可能性があります。
ORDER BYパーティション・キー列: これは、最初のn個のヒットのみが必要で、ORDER BY句がパーティション・キーを指定します。次のように、price列に対するORDER BY問合せで最初の20ヒットをフェッチする場合を考えてみます。
```
SELECT * FROM (
```
```
SELECT itemid FROM item_tab WHERE CONTAINS(item_desc, 'cd player')
  >0 ORDER BY price)
  WHERE ROWNUM < 20;
```
この例では、表はpriceによりパーティション化されており、最初のパーティションからのヒットを取得すれば問合せが満たされる可能性があります。

10.2.5 スコア順のローカル・パーティション索引を使用した応答時間の短縮

ローカル・パーティション索引に対するDOMAIN_INDEX_SORTヒントにより、特にスコア順にした場合にパフォーマンスが大幅に低下することがあります。結果をソートする前に、全パーティションにまたがる問合せの全ヒットを取得する必要があります。

かわりに、DOMAIN_INDEX_SORTヒントを使用するときにインライン・ビューを使用します。DOMAIN_INDEX_SORTヒントを使用すると、次のような条件下で、ローカル・パーティション表の問合せパフォーマンスが向上します。

SCORE()句による順序を含むOracle Text問合せ自体がインライン・ビューとして表されている場合。
インライン・ビュー内のOracle Text問合せにDOMAIN_INDEX_SORTヒントが含まれている場合。
インライン・ビューに対する問合せに、ビューからフェッチする行数を制限するROWNUM述語が含まれている場合。

たとえば、パーティション表doc_tabに対して、次のOracle Text問合せとローカルOracle Text索引が作成されます。

     select doc_id, score(1) from doc_tab 
        where contains(doc, 'oracle', 1)>0 
        order by score(1) desc;

上位20行のみを取り出す場合は、この問合せを次のように再作成します。

     select * from 
          (select /*+ DOMAIN_INDEX_SORT */ doc_id, score(1) from doc_tab 
              where contains(doc, 'oracle', 1)>0 order by score(1) desc) 
      where rownum < 21;

10.2.6 問合せフィルタ・キャッシュを使用した応答時間の短縮

Oracle Textには、問合せ結果のキャッシュに使用できる、問合せフィルタ・キャッシュというキャッシュ・レイヤーがあります。問合せフィルタ・キャッシュは、問合せ間で共有が可能です。キャッシュされた問合せ結果を複数の問合せで再利用して、問合せの応答時間を短縮できます。

キャッシュする問合せ結果を指定するには、ctxfiltercache演算子を使用します。次の例では、この演算子を使用して、common_predicate問合せの結果をキャッシュに格納しています。

select * from docs where contains(txt, 'ctxfiltercache((common_predicate), FALSE)')>0;

この例では、common_predicate問合せの結果がキャッシュされ、new_query問合せによって再利用されます。そのため、問合せの応答時間が短縮されます。

select * from docs where contains(txt, 'new_query & ctxfiltercache((common_predicate), FALSE)')>0;

ノート:

問合せフィルタ・キャッシュのサイズは、query_filter_cache_size基本記憶域属性を使用して指定できます。
ctx_filter_cache_statisticsビューには、問合せフィルタ・キャッシュに関する様々な統計が用意されています。

ノート:

CTXFILTERCACHE問合せ演算子は、問合せでよく使用される式を高速化するために設計されました。Oracle Databaseリリース21cでは、この機能は他の内部の改善によって置き換えられています。CTXFILTERCACHE演算子は非推奨です(また、そのオペランドを通過して通常の問合せとして実行されます)。それらには機能がないため、ビューCTX_FILTER_CACHE_STATISTICSも記憶域属性QUERY_FILTER_CACHE_SIZEも非推奨です。

10.2.7 CONTEXT索引のBIG_IOオプションを使用した応答時間の短縮

Oracle Textに用意されているBIG_IOオプションを使用すると、CONTEXT索引でIO操作が多用される場合の、問合せのパフォーマンスを改善できます。

問合せパフォーマンスが向上するのは、主として回転ディスク上に格納されているデータの場合であり、SSDに格納されているデータについてはあまり効果がありません。

BIG_IOオプションを有効にすると、CONTEXT索引では一意のトークン・テキストごとに1つのラージ・オブジェクト(LOB)データ型を伴うトークン・タイプ・ペアが作成されます。テキストが同じでトークン・タイプが異なるトークンは、$I表で対応する行が異なります。

ノート:

CONTEXT索引タイプのBIG_IO属性は、Oracle Database 23aiで非推奨になり、将来のリリースで無効化または削除される可能性があります。

Oracleでは、この値をデフォルト値Nに設定することをお薦めします。BIG_IOは、索引ポストの長さが4KBを超えた場合にシークのコストを削減するために導入されました。ただし、内部コードは比較的非効率であり、属性を新しい索引オプションと組み合せることはできません。シーク・コストは、ソリッド・ステート・ディスクまたは非揮発性メモリー・デバイス(NVMe)にはあまり関連せず、ポストがキャッシュされている場合、シーク・コストは関係ありません。したがって、この設定はほとんどの索引にとってほとんどメリットがありません。

BIG_IOオプションが有効になっている索引には、SecureFile LOBとして作成されたトークンLOBが必要です。これにより、データが複数のブロックに順次格納されます。この方法により、問合せで短い読取りを何度も実行するかわりに、連続した長い読取りを実行できるため、問合せの応答時間が短縮されます。

ノート:

SecureFilesを使用する場合は、COMPATIBLE設定を11.0以上に設定する必要があります。また、自動セグメント領域管理(ASSM)の表領域にLOBを作成する必要があります。既存のOracle Text索引をSecureFilesに移行する場合は、ASSM表領域を使用します。既存の索引をSecureFilesに移行しやすくするために、$I表にのみ影響する記憶域プリファレンスを提供するようにALTER INDEX REBUILDを拡張できます。

BIG_IO索引オプションを指定してCONTEXT索引を作成するには、まずBIG_IO記憶域属性の値をYESに設定して基本記憶域プリファレンスを作成し、CONTEXT索引の作成時にこの記憶域プリファレンスを指定します。

次の例は、mystore基本記憶域プリファレンスを作成し、そのBIG_IO記憶域属性の値をYESに設定します。

exec ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
exec ctx_ddl.set_attribute('mystore', 'BIG_IO', 'YES');

BIG_IOオプションを無効にするには、BIG_IO記憶域属性の値をNOに設定して既存の記憶域プリファレンス(mystore)を更新してから、索引を再構築します。

exec ctx_ddl.set_attribute('mystore', 'BIG_IO', 'NO');
alter index idx rebuild('replace storage mystore');

警告:

メタデータ置換操作を使用して、BIG_IO索引オプションを無効にしないでください。索引で矛盾が生じる場合があります。

索引を再構築せずにパーティション索引でBIG_IOオプションを有効にするには、BIG_IO記憶域属性の値をYESに設定して基本記憶域プリファレンスを変更し、ctx_ddl.replace_index_metadata,を使用してグローバル索引メタデータを置き換えてから、パーティション索引表のパーティションごとにREBUILDモードでoptimize_indexをコールします。

次の例では、idxパーティション索引に対してBIG_IOオプションを有効にします。

exec ctx_ddl.set_attribute('mystore', 'BIG_IO', 'YES');
exec ctx_ddl.replace_index_metadata('idx', 'replace metadata storage mystore');
exec ctx_ddl.optimize_index('idx', 'rebuild', part_name=>'part1');

ノート:

BIG_IOオプションのみを有効にして既存の索引表を変更するプロシージャでは、データの再索引付けは行われません。

ノート:

BIG_IO索引オプションは連続した長い読取りを実行するので、BIG_IO索引オプションを使用する問合せでは、必要なプログラム・グローバル領域(PGA)のメモリーが多くなります。

10.2.8 CONTEXT索引のSEPARATE_OFFSETSオプションを使用した応答時間の短縮

Oracle Textに用意されているSEPARATE_OFFSETSオプションを使用すると、CONTEXT索引でIO操作が使用されその問合せが主に単一語またはブールの問合せである場合の、問合せパのフォーマンスを改善できます。

SEPARATE_OFFSETSオプションでは、TEXTタイプのトークンに対して作成されるポスト・リストの構造が異なります。DOCID、頻度、情報の長さ(オフセット情報の長さ)およびオフセットをポスト・リストに分散させるかわりに、SEPARATE_OFFSETSオプションではDOCIDと頻度をすべてポスト・リストの最初に格納し、情報の長さとオフセットをすべてポスト・リストの最後に格納します。ポストの先頭にあるヘッダーには、DOCIDとオフセットの間の境界点に関する情報が含まれています。DOCIDとオフセットを分離すると、データを読み取る問合せの時間が短くなるため、問合せの応答時間が短縮されます。

SEPARATE_OFFSETSオプションを指定してCONTEXT索引を作成するには、まずSEPARATE_OFFSETS記憶域属性の値をTに設定して基本記憶域プリファレンスを作成します。次に、CONTEXT索引の作成時に、この記憶域プリファレンスを指定します。

次の例は、mystore基本記憶域プリファレンスを作成し、そのSEPARATE_OFFSETS記憶域属性の値をTに設定します。

exec ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
exec ctx_ddl.set_attribute('mystore', 'SEPARATE_OFFSETS', 'T');

SEPARATE_OFFSETSオプションを無効にするには、SEPARATE_OFFSETS記憶域属性の値をFに設定して既存の記憶域プリファレンス(mystore)を更新してから、索引を再構築します。

exec ctx_ddl.set_attribute('mystore', 'SEPARATE_OFFSETS', 'F');
alter index idx rebuild('replace storage mystore');

警告:

索引で矛盾が生じるので、SEPARATE_OFFSETS索引オプションの無効化にメタデータ置換操作は使用しないでください。

索引を再構築せずにパーティション索引でSEPARATE_OFFSETSオプションを有効にするには、SEPARATE_OFFSETS記憶域属性の値をTに設定して基本記憶域プリファレンスを変更し、ctx_ddl.replace_index_metadata,を使用してグローバル索引メタデータを置き換えてから、パーティション索引表のパーティションごとにREBUILDモードでoptimize_indexをコールします。

次の例では、idxパーティション索引に対してSEPARATE_OFFSETSオプションを有効にします。

exec ctx_ddl.set_attribute('mystore', 'SEPARATE_OFFSETS', 'T');
exec ctx_ddl.replace_index_metadata('idx', 'replace storage mystore');
exec ctx_ddl.optimize_index('idx', 'rebuild', part_name=>'part1');

ノート:

SEPARATE_OFFSETSオプションのみを有効にして既存の索引表を変更するプロシージャでは、データの再索引付けは行われません。

10.2.9 CONTEXT索引のSTAGE_ITAB、STAGE_ITAB_MAX_ROWS、STAGE_ITAB_PARALLELオプションを使用した応答時間の短縮

Oracle Textに用意されているSTAGE_ITABオプションを使用すると、CONTEXT索引および検索索引でほぼリアルタイムの索引付けのために挿入、更新および削除の操作が多用される場合の、問合せのパフォーマンスを改善できます。

STAGE_ITABオプションは、検索索引についてのみ、デフォルトの索引オプションとなります。

STAGE_ITAB索引オプションを使用しない場合は、新しいドキュメントをCONTEXT索引に追加するたびに、ドキュメントを検索可能にするためにSYNC_INDEXがコールされます。このコールは、$I表に新しい行が作成されるため、$I表で断片化が大きくなります。その結果、問合せのパフォーマンスが低下します。

STAGE_ITAB索引オプションを有効にすると、次の処理が行われます。

新しいドキュメントに関する情報は、$I表ではなく$Gステージング表に格納されます。このストレージによって$I表は断片化しなくなるため、問合せパフォーマンスが低下しません。
$H Bツリー索引は、$G表に作成されます。$G表と$H Bツリー索引は、$I表と$X Bツリー索引と同等です。

$G表の行数が記憶域設定STAGE_ITAB_MAX_ROWS (デフォルトは1万)を超えると、$G表から$I表に行が自動的にマージされます。MERGE最適化モードで索引最適化を実行することによって、$Gから$Iへの行の即時マージを強制することもできます。

ノート:

$G表は、KEEPプールに格納されます。問合せパフォーマンスを改善するには、十分なKEEPプール・メモリーを割り当て、新しいstage_itab_max_rowsオプションを使用して十分な大きさの$G表を維持する必要があります。

STAGE_ITAB索引オプションを指定してCONTEXT索引を作成するには、まずSTAGE_ITAB記憶域属性の値をYESに設定して基本記憶域プリファレンスを作成します。次に、CONTEXT索引の作成時に、この記憶域プリファレンスを指定します。

次の例は、mystore基本記憶域プリファレンスを作成し、そのSTAGE_ITAB記憶域属性の値をYESに設定します。

exec ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
exec ctx_ddl.set_attribute('mystore', 'STAGE_ITAB', 'YES');

ALTER INDEX文の再構築オプションを使用すれば、パーティション化されていない既存のCONTEXT索引に対してSTAGE_ITAB索引オプションを有効にすることもできます。

alter index IDX rebuild parameters('replace storage mystore');

パーティション化されていないCONTEXT索引に対してSTAGE_ITABオプションを無効にするには、STAGE_ITAB記憶域属性の値をNOに設定して既存の記憶域プリファレンス(mystore)を更新してから、索引を再構築します。

exec ctx_ddl.set_attribute('mystore', 'STAGE_ITAB', 'NO');
alter index idx rebuild parameters('replace storage mystore');

この操作は、MERGE最適化モードを使用して最適化プロセスを実行してから、$G表を削除します。

ALTER INDEX文の再構築オプションを、パーティション化されているCONTEXT索引に使用して、STAGE_ITABオプションの有効化と無効化を行うことはできません。

次の例は、CONTEXTパーティション索引idxに対してSTAGE_ITABオプションを有効にします。

alter index idx parameters('add stage_itab');

次の例は、CONTEXTパーティション索引idxに対してSTAGE_ITABオプションを無効にします。

alter index idx parameters('remove stage_itab');

$Gの行数がOracle Database 12cリリース2 (12.2)では100万、Oracle Databaseリリース18cでは10万を超えている場合は、索引の同期中に、$Gの内容が自動的に$Iに移動されました。Oracle Databaseリリース21c以降では、デフォルトでは、$Gの行数が1万を超えている場合は、索引の同期中に$Gの内容が自動的に$Iに移動されます。この値は、STORAGEプリファレンスのSTAGE_ITAB_MAX_ROWS属性により制御されます。

ノート:

CONTEXT索引にSTAGE_ITAB索引オプションを使用するには、g_index_clauseおよびg_table_clause BASIC_STORAGEプリファレンスを指定する必要があります。

$G表の断片化が進むと、問合せのパフォーマンスが低下します。低下を回避するために、Oracle Databaseリリース18c以降、Oracle Textではすべての索引またはパーティションに対する自動バックグラウンド最適化マージ機能が提供されています。自動バックグラウンド最適化マージを有効にするには、STAGE_ITAB記憶域プリファレンス属性をTRUEに設定し、STAGE_ITAB属性を使用する記憶域プリファレンスで索引を作成する必要があります。

デフォルトでは、Oracle Databaseリリース18cにアップグレードする前に、索引でSTAGE_ITABを有効化した場合、STAGE_ITAB_AUTO_OPTは有効化されません。既存の索引でSTAGE_ITABおよびAUTO_OPTIMIZEが有効になっている場合は、STAGE_ITAB_AUTO_OPTを有効化する前にAUTO_OPTIMIZEを無効にする必要がありますOracle Databaseリリース19c以降では、自動バックグラウンド最適化マージのSTAGE_ITAB_AUTO_OPTは、デフォルトではTRUEに設定されています。STAGE_ITAB_AUTO_OPTをFALSEに設定した場合は、そのマージはSYNC INDEXの一部として実行されます。AUTO_OPTIMIZEを使用するかわりにSTAGE_ITABおよびSTAGE_ITAB_AUTO_OPTをTRUEに設定することをお薦めします。

ノート:

Oracle Databaseリリース21cでは、プロシージャADD_AUTO_OPTIMIZEおよびREMOVE_AUTO_OPTIMIZEと、ビューCTX_AUTO_OPTIMIZE_INDEXES、CTX_USER_AUTO_OPTIMIZE_INDEXESおよびCTX_AUTO_OPTIMIZE_STATUSは非推奨です。

次の例は、mystore基本記憶域プリファレンスを作成し、そのSTAGE_ITAB_AUTO_OPT記憶域属性の値をTRUEに設定します。

exec ctx_ddl.create_preference('mystore', 'basic_storage');
exec ctx_ddl.set_attribute('mystore', 'stage_itab', 'TRUE');
exec ctx_ddl.set_attribute('mystore', 'stage_itab_auto_opt', 'TRUE');
exec ctx_ddl.set_attribute('mystore', 'stage_itab_parallel', 16);

関連トピック

Oracle Textリファレンス

10.2 応答時間短縮のための問合せの最適化

10.2.1 問合せの応答時間に影響するその他の要因

10.2.2 FIRST_ROWS(n)ヒントによるORDER BY問合せの応答時間の短縮

10.2.3 DOMAIN_INDEX_SORTヒントを使用した応答時間の短縮

10.2.4 ローカル・パーティションCONTEXT索引を使用した応答時間の短縮

10.2.5 スコア順のローカル・パーティション索引を使用した応答時間の短縮

10.2.6 問合せフィルタ・キャッシュを使用した応答時間の短縮

10.2.7 CONTEXT索引のBIG_IOオプションを使用した応答時間の短縮

10.2.8 CONTEXT索引のSEPARATE_OFFSETSオプションを使用した応答時間の短縮

10.2.9 CONTEXT索引のSTAGE_ITAB、STAGE_ITAB_MAX_ROWS、STAGE_ITAB_PARALLELオプションを使用した応答時間の短縮