#インデックス付け

Post a job フォロー
0 フォロワー · 15 投稿

データベースのデータ構造にインデックスを付ける方法

Post a job フォロー
記事 Toshihiko Minamoto · 10月 28, 2025 3m read

Pythonダンダーメソッド入門

img

この記事では、マジックメソッドとしても知られるPythonダンダーメソッドについて簡単に解説します。

ダンダーメソッドとは？

ダンダーメソッドは、始めと終わりに2つのアンダースコア（__）が付いているPythonの特殊メソッドです。 このメソッドを使用することで、加算や減算、文字列表現など、組み込みの操作に対するオブジェクトの動作を定義することができます。

よくあるダンダーメソッドには、次が含まれます。

  • __init__(self, ...)：オブジェクトの作成時に呼び出されます。
    • ObjectScriptの %OnNew メソッドに似ています
  • __str__(self)：オブジェクトを文字列として表現するために、str() 組み込み関数と print によって呼び出されます。
  • __repr__(self)：デバッグ用のオブジェクトを表現するために、repr() 組み込み関数によって呼び出されます。
  • __add__(self, other)：+ 演算子が使用される際に呼び出されます。
  • __len__(self)：オブジェクトの長さを返すために、len() 組み込み関数によって呼び出されます。
  • __getitem__(self, key)：インデックス構文を使用してコレクションからアイテムを取得するために呼び出されます。
  • __setitem__(self, key, value)：インデックス構文を使用してコレクション内にアイテムを設定するために呼び出されます。
  • ... 他にもたくさんあります。

IRIS環境において、ダンダーメソッドが重要であり関連性があるのはなぜか？

Objectscriptでは、Pythonのようなシンタックスシュガーはありませんが、ダンダーメソッドを使用して同じような動作を実現できます。

例えば、インポートしたPythonモジュールにPythonのリストを返す関数があり、Objectscriptでそれを使用したいとします。 リスト内のアイテムにアクセスするには、__getitem__ ダンダーメソッドを使用する必要があります。

# src/python/article/dunder_example.py
def get_list():
    return [1, 2, 3, 4, 5]

Class Article.DunderExample Extends %RegisteredObject
{

ClassMethod Run()
{
    Set sys = ##class(%SYS.Python).Import("sys")
    do sys.path.append("/irisdev/app/src/python/article")
    set dunderExample = ##class(%SYS.Python).Import("dunder_example")
    set myList = dunderExample."get_list"()
    for i=0:1:myList."__len__"()-1 {
        write myList."__getitem__"(i), !
    }
}

}

実行しましょう。

iris session iris -U IRISAPP '##class(Article.DunderExample).Run()'

出力は以下のようになります。

1
2
3
4
5

これは、IRIS環境でPythonオブジェクトとやり取りするためにダンダーメソッドを使う方法を具体的に示しており、ObjectScript環境内で作業しながらPythonの機能を活用することができます。

ボーナス

ダンダーの優れた使い方の1つは、スクリプトがモジュールとしてインポートされた際にコードが実行されないようにするために、if __name__ == "__main__": ブロックをPythonスクリプトの最後に配置することです。

最初の記事で、スクリプトをインポートするとコードが実行されることを説明しました。 このブロックにより、スクリプトがインポートされたときではなく、直接実行されたときにのみ実行されるコードを定義できます。

例：

# src/python/article/dunder_example.py
def get_list():
    return [1, 2, 3, 4, 5]

if __name__ == "__main__":
    print(get_list())

まとめ

ダンダーメソッドを使えば、PythonのシンタックスシュガーでできることはObjectScriptでも可能です。

#InterSystems IRIS #ObjectScript #Python #インデックス付け #デバッグ
Open Exchange
0
0 14
InterSystems公式 Seisuke Nakahashi · 10月 4, 2024

近似最近傍探索(ANN)インデックスによるベクトル検索高速化: 早期アクセスプログラムで入手可能

InterSystems IRIS の新バージョンに、 Hierarchical Navigable Small World (HNSW) インデックス・アルゴリズムに基づく新しい近似最近傍探索 (ANN) インデックスが搭載されました。こちらは、ベクトル検索 早期アクセスプログラム で入手いただけます。これにより、大規模なベクトルデータセットに対して非常に効率の良い近似最近傍探索が可能となり、クエリパフォーマンスとスケーラビリティが大幅に向上しました。

HNSW アルゴリズムは、グラフベース構造を利用して高次元データのベクトル検索を最適化するよう設計されており、大規模なベクトル集合における近似近傍探索を高速化します。HNSW によって、レコメンデーションシステム、自然言語処理、その他の機会学習アプリケーションなどすべてにおいて検索時間が大幅に短縮します。

HNSWの主な利点:

    •    データセットサイズ増加後も、より高速な検索が可能
    •    高精度をたもちながら、メモリ使用量を削減
    •    既存の IRIS ベクトル検索とのシームレスな統合

最新バージョンのトライアル

#InterSystems IRIS #InterSystems IRIS for Health #InterSystems公式 #Early Access Program (EAP) #Natural Language Processing #SOAP #Vector Search #インデックス付け #グラフ #パフォーマンス
0
0 186
記事 Mihoko Iijima · 2月 29, 2024 5m read

インデックス再構築が終わるまで新しく定義したインデックスを使用させない方法

これは InterSystems FAQ サイトの記事です。

新しいインデックスを定義した後、インデックスの再構築が完了する前にクエリを実行するとデータが存在しているにもかかわらず「検索結果0件」や検索結果数が徐々に増えるような状況が発生します。

インデックスを永続クラス定義（またはテーブル定義）に追加しコンパイルすることで今まで使用していたクエリ実行経路が削除され、再度同じクエリを実行するタイミングで新しいインデックス定義を含めた実行経路が作成されるためです。（この時にインデックス再構築が完了していないとインデックスデータが存在しない、または不完全であるため0件や徐々に検索結果数が増えるような状況を起こします。）

これを起こさなために、新しいインデックスの再構築が終了するまでクエリオプティマイザにインデックスを使用させないように指定する方法が用意されています。

※ 2024/8/2： 2024.1以降から利用できる方法を追加しました。

2024.1以降

CREATE INDEXのDEFERオプションを使用します（オプションを付けないCREATE INDEX文では、作成時にインデックスの再構築も同時に行われます）。

#Caché #Ensemble #HealthShare #InterSystems IRIS #InterSystems IRIS for Health #SQL #インデックス付け #ヒントとコツ
1
0 319
記事 Toshihiko Minamoto · 12月 8, 2022 5m read

InterSystems IRIS における一意のインデックスと null 値

一意のインデックスにまつわる興味深いパターンが最近持ちあがったので（isc.rest に関する内部ディスカッション）、コミュニティ向けに強調したいと思います。

動機付けのユースケースとして: ツリーを表すクラスがあるとします。各ノードには名前があるため、名前と親ノードでノードを一意にしたいと考えています。 各ルートノードにも一意の名前を持たせます。 この場合の自然な実装は以下のようになります。

Class DC.Demo.Node Extends%Persistent
{

Property Parent As DC.Demo.Node;Property Name As%String [ Required ];
Index ParentAndName On (Parent, Name) [ Unique ];
Storage Default
{
<Data name="NodeDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>Parent
</Value>
<Value name="3">
<Value>Name
</Value>
</Data>
<DataLocation>^DC.Demo.NodeD</DataLocation>
<DefaultData>NodeDefaultData</DefaultData>
<IdLocation>^DC.Demo.NodeD</IdLocation>
<IndexLocation>^DC.Demo.NodeI</IndexLocation>
<StreamLocation>^DC.Demo.NodeS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

}

以上です！

ただし、落とし穴があります。現状のこの実装では、複数のルートノードに同じ名前を付けることができます。 なぜでしょうか？ Parent は必須プロパティでない（また、そうすべきではない）ため、IRIS は null を一意のインデックスの個別の値として処理しないからです。 一部のデータベース（SQL Server など）はそうしていますが、SQL 標準では、それらが誤っていると述べています（出典が必要です。このことについては StackOverflow のどこかで見かけましたが、それは出典になりません。以下にある、これについてとインデックスと制約の違いについての @Dan Pasco のコメントをご覧ください）。

これを回避する方法は、参照されたプロパティが null である場合に、非 null 値に設定される計算プロパティを定義し、そのプロパティに一意のインデックスを設定することです。 以下に、例を示します。

Property Parent As DC.Demo.Node;Property Name As%String [ Required ];Property ParentOrNUL As%String [ Calculated, Required, SqlComputeCode = {Set {*} = $Case({Parent},"":$c(0),:{Parent})}, SqlComputed ];
Index ParentAndName On (ParentOrNUL, Name) [ Unique ];

これにより、$c(0) を ParentAndNameOpen/Delete/Exists に渡して、親（存在しない）と名前でルートノードを一意に識別することもできます。

この動作が非常に役立つ動機付けの例として、https://github.com/intersystems/isc-rest/blob/main/cls/_pkg/isc/rest/resourceMap.cls をご覧ください。 多くの行は、2 つのフィールド（DispatchOrResourceClass と ResourceName）に対して同じセットの値を持つことができますが、最大でもその内 1 つを「デフォルト」として扱いたいと考えており、一意のインデックスはこれを行う上で最適です。「デフォルト」フラグを 1 または null に設定できると言う場合は、フラグと他の 2 つのフィールドに一意のインデックスを付けることができます。

@Dan Pasco さんのコメント

Nullや一意制約について、私の意見と、可能性のあるいくつかの事実を記載します。

IRISユニークインデックス - これは単なるインデックスだけでなく、インデックスキーに一意性制約を定義する構文的なショートカットです。多くのSQLの実装は、この2つの概念を融合せず、SQL標準はインデックスを定義していません。標準SQLは一意制約を定義しています。IDKEYとPRIMARYKEYの両方が一意制約の修飾子であることに留意してください（そして、我々の世界では、IDKEYとして定義されたインデックスもまた特別なものである）。IDKEYとしてフラグが立てられたインデックスとPRIMARYKEYとしてフラグが立てられたインデックスは、最大で1つずつ存在することができます。インデックスはPRIMARYKEYとIDKEYの両方になることができます。

かつて、"ユニークインデックス "と "ユニーク制約 "の構文を異なる規則で定義したSQL実装がありました。両者の違いは単純で、インデックスが完全に入力されていない場合（テーブルのすべての行がインデックスに表示されていない場合、これを条件付きインデックスと呼びます）、一意インデックスはインデックスで表される行の一意性をチェックするだけです。一意制約はすべての行に適用されます。

また、インデックスは、クエリのサブセットのパフォーマンスを向上させるという、唯一の目的のために存在することに留意してください。どのようなSQL制約もクエリとして表現することができます。

標準SQLは、NULLの動作に関して、少し一貫性がありません。フレームワークのドキュメントでは、次のように定義されています。

一意制約とは、テーブルの1つまたは複数の列を一意列として指定するものです。一意制約は、テーブル内の2つの行が一意列の同じ非NULL値を持たない場合にのみ満たされます。

基本文書では、F291とF292という2つのオプション機能があります。これらの機能は、ユニークな述語(291)とユニークなNULL処理(292)を定義しています。これらの機能は、ユーザがNULLの "区別性 "を定義できる構文を提供するように見えます。どちらもオプションの機能で、比較的最近(2003年?2008年?)のものです。これらの機能がサポートされていない場合のルールは、実装者に委ねられています。

IRISは、フレームワーク文書ステートメントと一致しており、すべての制約は、NULLでないキーにのみ適用されます。「NULL」キーは、すべてのキーカラム値がNULLであるキーと認識されます。

#Caché #InterSystems IRIS #SQL #インデックス付け
0
0 314
記事 Toshihiko Minamoto · 5月 18, 2021 12m read

インデックス処理

第2部: インデックス処理

クラスにどのようなインデックスが必要であるのか、それをどのように定義するのかについて理解できたので、 次に、どのように処理するのかについて確認しましょう。

クエリプラン

（注意: クラスに変更を適用する場合と同様に、ライブシステムにインデックスを追加する場合にもリスクが伴います。インデックスが入力されているときに、ユーザーがデータにアクセスしたり更新したりすると、クエリ結果が空になったり誤った結果が生じることがあります。また、構築中のインデックスが破損する場合もあります。 ライブシステムでインデックスを定義したり使用したりするには追加の手順があり、それについてはこのセクションで触れていますが、詳細はドキュメントに記載されています。）

新しいインデックスの準備ができたら、SQLオプティマイザが、クエリを実行する上で最も効率的に読み取れるインデックスであると判断するかどうかを確認できます。 プランを確認するために実際にクエリを実行する必要はありません。 クエリがあれば、プランをプログラムで確認することができます。

            Set query = 1

     Set query(1) = “SELECT SSN,Name FROM Sample.Person WHERE Office_State = 'MA'”

D $system.SQL.ShowPlan(.query)

また、システムエクスプローラー -> SQLより、システム管理ポータルのインターフェースに従って確認することもできます。

ここから、どのインデックスが、テーブルデータ（または「マスターマップ」）をロードする前に使用されているのかを確認できます。 プランの新しいクエリは期待どおりに動作しているのか、 プランのロジックが合理的であるのかを検討しましょう。

SQLオプティマイザが作成したインデックスを使用していることを確認できたら、これらのインデックスが適切に機能しているかどうかを確認できます。

インデックスの構築

（現時点では計画しているだけの段階であり、データが存在しない場合は、ここに説明されている手順は必要ありません。）

インデックスを定義しても、テーブルのデータが自動的に入力または「構築」されるわけではありません。 まだ構築されていない新しいインデックスをクエリプランに使用すると、クエリ結果が誤りとなったり空になったりするリスクがありますが、 マップの選択可能性を0に設定すると、インデックスの使用準備が整う前にそのインデックスを「無効」にすることができます。0に設定するというのは、基本的に、SQLオプティマイザに、クエリの実行にはそのインデックスを使用できないことを指示していることになります。

write $SYSTEM.SQL.SetMapSelectability("Sample.Person","QuickSearchIDX",0) ; Set selectability of index QuickSearchIDX false

上記の呼び出しは、新しいインデックスを追加する前でも使用できることに注意してください。 SQLオプティマイザは、この新しいインデックスの名前と、それが非アクティブであることを認識するため、クエリには使用しません。

インデックスの入力は、 %BuildIndices メソッド（##class(<class>).%BuildIndices($lb("MyIDX"))）を使用するか、システム管理ポータルのSQLページ（アクションドロップダウン）で行うことができます。

インデックスの構築にかかる時間は、テーブル内の行数とインデックスの種類によって異なります。通常、bitsliceインデックスの作成には時間が掛かります。

このプロセスが完了したら、もう一度SetMapSelectivityメソッドを使用して、新たに入力されたインデックスを有効にできます（この場合は 1 に設定）。

インデックスを作成するというのは、基本的に、KILLコマンドとSETコマンドを発行して、インデックスを入力することです。 そのため、このプロセスを実施する間は、ディスク領域がいっぱいにならないように、ジャーナリングを無効にすることを検討してください。  

新しいインデックスと既存のインデックスを作成する詳細な手順について、特にライブシステムでの手順については、以下にリンクされているドキュメントをご覧ください。

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_indices#GSQLOPT_indices_build_readwrite

インデックスの管理

ようやく、実際にインデックスを使用できるようになりました。 クエリがどれほど効率的に実行するのか、ある特定のインデックスがどれくらいの頻度で使用されるのか、そしてインデックスの一貫性が崩れた場合にどのように対処するのかといったことを考慮する必要があります。

パフォーマンス

まず、このインデックスがクエリのパフォーマンスにどのような影響を与えたのかを検討できます。 SQLオプティマイザがクエリに新しいインデックスを使用していることがわかっている場合は、クエリを実行して、そのパフォーマンスの統計（グローバル参照数、読み取られた行数、クエリの準備時間と実行時間、およびディスクで費やされた時間）を収集することができます。

前の例に戻りましょう。

SELECT SSN,Name,DOB FROM Sample.Person WHERE Name %STARTSWITH 'Smith,J'

このクエリのパフォーマンスを支援するために、次のインデックスがあります。

Index QuickSearchIDX On Name [ Data = (SSN, DOB, Name) ];

NameIDXインデックスは、すでにNameプロパティにあります。

直感的には、クエリはQuickSearchIDXを使って実行していることがわかります。NameIDXは、Nameプロパティに基づいていますが、SSNまたはDOBのデータ値が含まれていないため、2番目の選択肢である可能性があります。

QuickSearchIDXを使ったこのクエリのパフォーマンスは、実行するだけで簡単に確認できます。

（余談ですが、パフォーマンスの違いを分かりやすくするために、クエリを実行する前に、クエリキャッシュを消去しています。 SQLクエリが実行されると、次回実行時のパフォーマンスを改善できるように、実行に使用されたプランが保存されます。この記事の目的から外れてしまうため詳しい説明はしませんが、SQLパフォーマンスに関するその他の考慮事項として、この記事の最後に資料を記載しておきます。）

Row count: 31 Performance: 0.003 seconds  154 global references 3264 lines executed 1 disk read latency (ms)

(率直に)クエリの実行に必要なのは、QuickSearchIDXのみです。

QuickSearchIDXではなくNameIDXを使ったパフォーマンスを比較して見ましょう。これは、クエリキーワードである%IGNOREINDEXを追加して行います（SQLオプティマイザが特定のインデックスを選択できないようにします）。

クエリを次のように記述します。

SELECT SSN,Name,DOB FROM %IGNOREINDEX QuickSearchIDX Sample.Person WHERE Name %STARTSWITH 'Smith,J'

クエリプランはNameIDXを使用するようになったので、インデックスを介して見つかった関連する行IDを使って、Sample.Personのデータグローバル（または「マスターマップ」）から読み取る必要があることがわかります。

Row count: 31 Performance: 0.020 seconds  137 global references 3792 lines executed 17 disk read latency (ms) 

実行に必要な時間、実行される行数、およびディスクのレイテンシが増加しているのがわかります。

次に、インデックスを全く使用せずに、このクエリを実行してみましょう。 クエリを次のように調整します。

SELECT SSN,Name,DOB FROM %IGNOREINDEX * Sample.Person WHERE Name %STARTSWITH 'Smith,J'

インデックスを使用しない場合、条件を満たすかどうか、データの行を確認する必要があります。

Row count: 31 Performance: 0.765 seconds  149999 global references 1202681 lines executed 517 disk read latency (ms)

特殊なインデックスであるQuickSearchIDXが、インデックスを使用しない場合よりも100倍以上速く、また一般的なNameIDXを使用した場合よりもほぼ10倍速く、クエリを実行できています。さらに、NameIDXを使用した場合は、インデックスを全く使用しない場合よりも30倍以上速く実行しています。

この特定の例では、QuickSearchIDXとNameIDXのパフォーマンスの差はわずかですが、数百万行に対して実行するクエリを1日に数百回実行するのであれば、貴重な時間を節約できることがわかるでしょう。

SQLUtilitiesを使った既存のインデックスの分析

%SYS.PTools.SQLUtilitiesには、IndexUsage、JoinIndices、TablesScans、TempIndicesなどのプロシージャが含まれています。 これらは、特定のネームスペースにある既存のクエリを分析し、特定のインデックスが使用される頻度、テーブルの各行で反復を選択しているクエリ、そしてインデックスをシミュレートする一時ファイルを生成しているクエリに関する情報を報告します。

これらのプロシージャを使用することで、インデックスが対処できる可能性があるギャップと、使用されていないまたは非効率であるために削除することを検討した方がよいインデックスを特定することができます。

これらのプロシージャと使用例についての詳細は、以下のクラスに関するドキュメントをご覧ください。

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_optquery_indexanalysis

インデックスに関する問題？

インデックスを検証することで、インデックスが存在し、クラスの各行で正しく定義されているのかを確認できます。 インデックスが破損した状態になるクラスはありませんが、クエリが空の結果セットや誤った結果セットを返しているようであれば、クラスの既存のインデックスが現在有効であるかを確認することをお勧めします。

インデックスは、プログラムで次のように検証できます。

                Set status = ##class(<class>).%ValidateIndices(indices,autoCorrect,lockOption,multiProcess)  

ここで、インデックスパラメーターはデフォルトで空の文字列です。つまり、すべてのインデックスかインデックスの名前を含む$listbuildオブジェクトを検証します。

autoCorrectは、デフォルトで0になることに注意してください。 1である場合、検証プロセスで発生するすべてのエラーは修正されます。 機能的にはインデックスを再構築することに変わりませんが、ValidateIndicesのパフォーマンスは比較的に遅くなります。

詳細は、%Library.Storageクラスのドキュメントをご覧ください。

https://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?PAGE=CLASS&LIBRARY=%25SYS&CLASSNAME=%25Library.Storage#%ValidateIndices

インデックスの削除

あるインデックスが不要になった場合、またはテーブルに大規模な変更を行って、後で関連するインデックスを構築する際にパフォーマンスへの影響がないようにする場合は、Studioでインデックスの定義をクラスから取り除き、該当するインデックスグローバルノードを削除することができます。  または、DDLを介してDROP INDEXコマンドを実行することもできます。このコマンドでも、インデックスの定義とデータを消去することができます。 その後、キャッシュ済みクエリをパージすることで、確実に、削除されたインデックスが既存のプランによって使用されなくなります。

さて今度は？

インデックスは、SQLパフォーマンスの一部にしかすぎません。 この流れに並行し、インデックスのパフォーマンスと使用状況を監視するオプションは他にもあります。 SQLパフォーマンスを理解するには、次のことについても学ぶことをお勧めします。

Tune Tables - テーブルに代表的なデータが入力されてから、またはデータの分布が大幅に変化した場合に実行するユーティリティです。 このユーティリティは、クラスの定義に、フィールドの長さや1つフィールドに含まれる一意の値の数など、SQLオプティマイザが効率的に実行するためにクエリプランを選択しやすくするメタデータを提供します。

Kyle Baxterが、これに関する記事を執筆しています（https://community.intersystems.com/post/one-query-performance-trick-you-need-know-tune-table）。 

クエリプラン - 基盤のコードがSQLクエリをどのように実行するか論理的に表現したプランです。 クエリが遅い場合、どのクエリプランが生成されているのか、クエリに適しているのか、このクエリをさらに最適化するために何を行えるのかを検討することができます。

キャッシュドクエリ - 準備済みの動的SQLステートメント - キャッシュドクエリは、基本的にクエリプランの下にあるコードです。

参考文献

インデックスの定義と構築に関するドキュメント。 読み取り/書き込みのライブシステムで検討する必要のある追加手順が含まれています。 https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_indices

ISC SQLコマンド – DDLを介したインデックス処理の構文関連資料について、CREATE INDEXとDROP INDEXを参照してください。 これらのコマンドを実行するための適切なユーザー権限が含まれています。 https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_COMMANDS

ISCクラスでのSQL照合に関する詳細。 デフォルトでは、文字列の値は、インデックスグローバルにSQLUPPER (“ STRING”) として格納されます。

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_collation

［最終更新日: 2020年5月6日 - インデックス構築パフォーマンスとDROP INDEXコマンドの修正。］

#Caché #InterSystems IRIS #SQL #インデックス付け #パフォーマンス #ベストプラクティス
0
0 365
記事 Toshihiko Minamoto · 5月 12, 2021 12m read

インデックスを理解する

これは、SQLインデックスに関する2部構成の記事の前半です。

第1部 - インデックスを理解する

インデックスとは？

最後に図書館に行った時のことを思い出してください。 通常そこには、分野別（そして作者順と題名順）に整理された本が並び、それぞれの棚には、本の分野を説明したコードが記載された本立てがあります。 特定の分野の本を収集する場合、すべての通路を歩いて一冊ずつ本の表紙を読む代わりに、目的の分野の本棚に直接向かって選ぶことができるでしょう。

SQLインデックスにもこれと同じ機能があります。テーブルの各行にフィールドの値へのクイック参照を提供することで、パフォーマンスを向上させています。

インデックスの設定は、最適なSQLパフォーマンスを得られるようにクラスを準備する際の主なステップの1つです。

この記事では、次のことについて説明します。

  1. インデックスとは何か。いつ、なぜそれを使用するか。
  2. どのようなインデックスが存在するか、どのようなシナリオに適しているのか。
  3. インデックスの例
  4. 作成方法
  • インデックスが存在する場合、どのように扱うのか。

    • この記事では、Sampleスキーマのクラスを参照します。 このスキーマは以下に示すGitHubリポジトリにあります。また、CachéとEnsembleでインストールされるSamplesネームスペースでも提供されています。

    https://github.com/intersystems/Samples-Data

    基本

    永続プロパティと、永続データから確実に計算されるプロパティにインデックスを作成できます。

    Sample.CompanyのTaxIDプロパティにインデックスを作成するとしましょう。 StudioまたはAtelierで、以下のコードをクラス定義に追加します。

                    Index TaxIDIdx On TaxID;

    これに相当するDDL SQLステートメントは、次のようになります。

                    CREATE INDEX TaxIDIdx ON Sample.Company (TaxID);

    デフォルトのグローバルインデックス構造は、次のようになります。

                    ^Sample.CompanyI("TaxIDIdx",<TaxIDValueAtRowID>,<RowID>) = ""

    通常のデータグローバルのフィールドより、読み取るサブスクリプトが少ないところに注目してください。

    「SELECT Name,TaxID FROM Sample.Company WHERE TaxID = 'J7349'」というクエリを見てみましょう。 論理的に単純なクエリです。このクエリを実行するためのクエリプランは、これを反映しています。

    このプランは基本的に、指定されたTaxID値を持つ行のインデックスグローバルをチェックし、データグローバル（「マスターマップ」）を参照して一致する行を取得するように指定しています。

    ここで、同じクエリを、TaxIDXにインデックスを使わずに考察してみましょう。 クエリプランの効率は、予想どおり、低下します。

    インデックスがない場合、IRISの基盤のクエリ実行は、メモリを読み取って、テーブルの各行にWHERE句の条件を適用します。論理的に言って、TaxIDを共有する会社はないと思うため、この作業をたった1行のためだけに行っているのです！

    もちろん、インデックスを使用するということは、インデックスと行データがディスクにあるということですので、 条件の内容とテーブルに含まれるデータの量によっては、インデックスを作成してデータを入力する際に、それ固有の問題が生じる可能性もあります。

    では、プロパティにはいつインデックスを追加すればよいのでしょうか。

    一般的なケースとしては、あるプロパティを頻繁に条件とする場合が挙げられます。 例として個人のSSN（社会保障番号）または銀行口座番号といった識別情報があります。 また、生年月日や口座の資金も考慮できるでしょう。  Sample.Companyに戻ると、高収益の組織に関するデータを収集する場合は、およらくRevenueプロパティのインデックスを作成するとメリットがあるかもしれません。 逆に、条件を付ける可能性が低いプロパティは、インデックス作成にあまり適していません。会社のスローガンや説明などです。

    インデックスの種類も検討する必要がある場合を除けば、「シンプル イズ ベスト」なのです！

    インデックスの種類

    ここでは、6つの主要なインデックスの種類について説明します。標準、ビットマップ、複合、コレクション、ビットスライス、およびデータです。 また、ストリームに基づくiFindインデックスについても簡単に説明します。 上記の例で、標準のインデックスについてすでに触れているため、内容が重複するかもしれません。

    クラス定義にインデックスを作成する方法の例をいくつか紹介しますが、新しいインデックスをクラスに追加するというのは、クラス定義に行を追加するだけではありません。 その他の考慮事項については、この記事の第2部で説明します。

    それでは、例としてSample.Personを使用しましょう。 PersonにはサブクラスのEmployeeがあることに注目してください。いくつかの例を理解する上で関わってくることです。 EmployeeはデータグローバルストレージをPersonと共有し、PersonのすべてのインデックスはEmployeeに継承されます。つまり、Employeeはこれらの継承されたインデックスに、Personのインデックスグローバルを使用しているということです。

    これらのクラスにあまり詳しくない場合のために簡単に説明すると、Personには、SSN、DOB、Name、Home（StateとCityを含む埋め込みAddressオブジェクト）、およびOffice（Address）プロパティがあり、FavoriteColorsというリストコレクションがあります。 Employeeには、さらに私が定義したSalaryプロパティもあります。

    標準

    Index DateIDX On DOB;

    ここでは、「標準」を大まかに使用して、プロパティのプレーンな値（2進数表現ではなく）を格納するインデックスを参照しています。 値が文字列である場合、照合順序（デフォルトではSQLUPPER）で格納されます。

    ビットマップやビットスライスのインデックスに比べ、標準のインデックスは人間が読み取れる形式であり、比較的簡単に管理することができます。 グローバルノードは、テーブルの各行に1つずつあります。

    以下に、DateIDXがグローバルレベルでどのように格納されるのかを示しています。

    ^Sample.PersonI("DateIDX",51274,100115)="~Sample.Employee~" ; Date is 05/20/81

    インデックスの名前の後にある最初のサブスクリプトは日付値で、最後のサブスクリプトはそのDOBを持つPersonのID、そしてグローバルインデックスに格納された値は、このPersonがサブクラスSimple.Employeeのメンバーであることを示していることに注目してください。 このPersonがどのサブクラスのメンバーでもない場合、ノードの値は空の文字列になります。

    この基本構造は、ほとんどの非ビットインデックスと一致します。この場合、複数のプロパティのインデックスはグローバルでサブスクリプトをさらに作成し、ノードに複数の値が格納されると、$listbuildオブジェクトが生成されます。以下はその例です。

                    ^Package.ClassI(IndexName,IndexValue1,IndexValue2,IndexValue3,RowID) = $lb(SubClass,DataValue1,DataValue2)

    ビットマップ - プロパティの値に対応するIDセットのビット単位の表現。

    Index HomeStateIDX On Home.State [ Type = bitmap];

    ビットマップインデックスは、行ごとに格納される標準のインデックスとは対照的に、ユニーク値ごとに格納されます。

    上記の例をさらに詳しく見てみましょう。ID 1のPersonがマサチューセッツ州（MA）に、ID 2がニューヨーク州（NY）に、ID 3がマサチューセッツ州（MA）に、そしてID 4がロードアイランド州（RI）に住んでいるとします。 HomeStateIDXは基本的に次のように格納されます。

    ID

    1

    2

    3

    4

    (…)

    (…)

    0

    0

    0

    0

    -

    MA

    1

    0

    1

    0

    -

    NY

    0

    1

    0

    0

    -

    RI

    0

    0

    0

    1

    -

    (…)

    0

    0

    0

    0

    -

    ニューイングランド州にする人のデータを返すクエリが必要な場合、システムはビットマップインデックスの関連する行にビット単位のORをを実行します。 少なくとも、ID 1、3、および4のPersonオブジェクトをメモリに読み込む必要があることにすぐに気づくでしょう。

    ビットマップは、WHERE句のAND、RANGE、またはOR演算子に対して効率的です。 

    ビットマップインデックスの効率が標準インデックスより低くなる前にプロパティに指定できるユニーク値の数に、公式の上限はありませんが、一般的には、最大10,000個程度の値が経験則とされています。 そのため、ビットマップインデックスは、米国の州において効果的であっても、米国の市または郡のビットマップインデックスにはあまり有用とは言えません。

    また、ストレージの効率についても、考慮する必要があります。 テーブルへの行の追加や行の削除を頻繁に行う予定であれば、ビットマップインデックスのストレージはあまり効果的ではありません。 上記の例を考察してみましょう。何らかの理由で多数の行を削除し、ワイオミング州やノースダコタ州といった人口の少ない州に住む人がテーブルから消えたとします。 つまり、ビットマップには、ゼロのみの行がいくつか存在することになります。 一方で、ビットマップストレージが大きければ、より多くのユニーク値を格納しなければならなくなるため、大型のテーブルに新しい行を作成していけば、いずれは減速していく可能性があります。

    これらの例では、Sample.Personに約150,000行があります。 各グローバルノードには、最大64,000個のIDが格納されるため、ビットマップインデックスグローバルは、MAで3つの部分に分割されます。

          ^Sample.PersonI("HomeStateIDX"," MA",1)=$zwc(135,7992)_$c(0,(...))

    ^Sample.PersonI("HomeStateIDX"," MA",2)=$zwc(404,7990,(…))

    ^Sample.PersonI("HomeStateIDX"," MA",3)=$zwc(132,2744)_$c(0,(…))

    特殊ケース: エクステントビットマップ 

    $<ClassName>とされることの多いエクステントビットマップは、クラスのIDにおけるビットマップインデックスです。IRISはこれを使って行が存在するのかをすばやく検出し、COUNTクエリまたはサブクラスのクエリに役立てています。 これらのインデックスは、ビットマップインデックスがクラスに追加される際に自動的に生成されますが、次のように、クラス定義にビットマップエクステントインデックスを手動で作成することも可能です。


    Index Company [ Extent, SqlName = "$Company", Type = bitmap ];

    DDLのBITMAPEXTENTキーワードを使うこともできます。

    CREATE BITMAPEXTENT INDEX "$Company" ON TABLE Sample.Company

    複合 - 2つ以上のプロパティに基づくインデックス

    Index OfficeAddrIDX On (Office.City, Office.State);

    複合インデックスは通常、2つ以上のプロパティを条件とするクエリが頻繁に発生する場合に使用できます。

    インデックスはグローバルレベルで格納されるため、複合インデックスでは、プロパティの順序が重要になります。 インデックスグローバルの最初のディスク読み取りは保存されるため、選択する頻度の高いプロパティを最初に指定すると、高いパフォーマンス効率を得ることができます。この例では、米国の州の数より都市の数の方が多いため、Office.Cityが最初に指定されています。

    あまり選択しないプロパティを最初に指定すると、スペースの効率性が高くなります。 グローバル構造に焦点を当てれば、Stateを最初に指定すると、インデックスツリーのバランスがより高まります。 考えてみれば、各州には多数の市がありますが、1つの州にしか存在しない市もあるのです。

    また、いずれかのプロパティのみを条件としたクエリを頻繁に実行するのかどうかを検討することもお勧めします。別のインデックスを定義する手間を省けるからです。

    複合インデックスのグローバル構造の例を以下に示します。

    ^Sample.PersonI("OfficeAddrIDX"," BOSTON"," MA",100115)="~Sample.Employee~"

    余談: 複合インデックスかビットマップインデックスか

    複数のプロパティで条件付けするクエリの場合、個別のビットマップインデックスを使った方が1つの複合インデックスよりも効果的かどうかを検討することもできます。

    ビットマップインデックスが各プロパティに適切に適合するのであれば、2つの異なるインデックスに対してビット演算した方が効率的になる可能性があります。

    複合ビットマップインデックスを作成することもできます。これらはユニーク値が、インデックスを作成している複数のプロパティの共通した値となるビットマップインデックスです。 前のセクションで示したテーブルを考察してみましょう。ただし、州の代わりに、州と市のすべての可能な組み合わせ（マサチューセッツ州ボストン、マサチューセッツ州ケンブリッジ、マサチューセッツ州ロサンゼルスなど）を用いたテーブルです。両方の値に適合する行のセルは1となります。

    コレクション - コレクションプロパティに基づくインデックス

    次のように定義されたFavoriteColorsプロパティがあります。

    Property FavoriteColors As list Of %String;

    実演の目的で、インデックスは次のように定義されています。

    Index fcIDX1 On FavoriteColors(ELEMENTS);
    Index fcIDX2 On FavoriteColors(KEYS);

    ここでは、複数の値を含む単一セルのプロパティをより広く参照するために、「コレクション」を使用しています。 ここでは、List OfとArray Ofプロパティが重要で、必要に応じて区切り付きの文字列も指定できます。

    コレクションプロパティは自動的に解析され、インデックスが構築されます。 電話番号などの区切り付きのプロパティでは、このメソッドを明示的に <PropertyName>BuildValueArray(value, .valueArray) と定義する必要があります。

    上記のFavoriteColorsの例で考えると、お気に入りの色が青と白であるPersonのfcIDX1は、次のようになります。

    ^Sample.PersonI("fcIDX1"," BLUE",100115)="~Sample.Employee~"

    (…)

    ^Sample.PersonI("fcIDX1"," WHITE",100115)="~Sample.Employee~"

    そしてfcIDX2は次のようになります。

             ^Sample.PersonI("fcIDX2",1,100115)="~Sample.Employee~"      

    ^Sample.PersonI("fcIDX2",2,100115)="~Sample.Employee~"

    この場合、FavoriteCoslorsはListコレクションであるため、キーに基づくインデックスの有用性は、要素に基づくインデックスよりも低くなります。

    コレクションプロパティのインデックスの作成と管理に関するより詳しい考慮事項については、ドキュメントをご覧ください。

    https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_indices#GSQLOPT_indices_collections

    ビットスライス - 数値データのビット文字列表現のビットマップ表現


    Index SalaryIDX On Salary [ Type = bitslice ]; //In Sample.Employee

    ビットスライスインデックスは、どの行に特定の値が含まれるかを表すフラグを含むビットマップインデックスとは異なり、最初に数値を10進数から2進数に変換し、その後で2進数値の各桁にビットマップを作成します。

    上記の例を見てみましょう。現実的に考えられるよう、Salaryを$1000単位として単純化します。つまり、従業員の給与が65であれば、65,000ドルということになります。

    ID 1のEmployeeのSalaryを15、ID 2のSalaryを40、ID 3のSalaryを64、ID 4のSalaryを130とします。 この場合、対応するビット値は次のようになります。

    15

    0

    0

    0

    0

    1

    1

    1

    1

    40

    0

    0

    1

    0

    1

    0

    0

    0

    64

    0

    1

    0

    0

    0

    0

    0

    0

    130

    1

    0

    0

    0

    0

    0

    1

    0

    ビット文字列は8桁を超えています。 対応するビットマップ表現（ビットスライスインデックス値）は、基本的に次ように格納されます。

    ^Sample.PersonI("SalaryIDX",1,1) = "1000" ; Row 1 has value in 1’s place

    ^Sample.PersonI("SalaryIDX",2,1) = "1001" ; Rows 1 and 4 have values in 2’s place

    ^Sample.PersonI("SalaryIDX",3,1) = "1000" ; Row 1 has value in 4’s place

    ^Sample.PersonI("SalaryIDX",4,1) = "1100" ; Rows 1 and 2 have values in 8’s place

    ^Sample.PersonI("SalaryIDX",5,1) = "0000" ; etc…

    ^Sample.PersonI("SalaryIDX",6,1) = "0100"

    ^Sample.PersonI("SalaryIDX",7,1) = "0010"

    ^Sample.PersonI("SalaryIDX",8,1) = "0001"

    Sample.Employeeまたはその行の給与を変更する演算（INSERT、UPDATES、DELETE）では、これらの各グローバルノードまたはビットスライスを更新する必要があることに注意してください。 ビットスライスインデックスをテーブルの複数のプロパティまたは頻繁に変更されるプロパティに追加すると、パフォーマンスにリスクが生じる可能性があります。 一般的に、ビットスライスインデックスの管理には、標準またはビットマップインデックスの管理よりもコストがかかります。

    ビットスライスインデックスは非常に特殊であるため、ユースケースも特殊であり、SUM、COUTN、またはAVGなどの集計計算を実行する必要のあるクエリで使用します。

    さらに、数値に対してのみ効果を発揮するため、文字列は2進数の0に変換されます。

    クエリの条件をチェックするために、インデックスではなくデータテーブルを読み取る必要がある場合、クエリの実行にビットスライスインデックスは選択されません。 Sample.PersonのNameにインデックスがないとします。 Smithという姓の従業員の平均給与を計算する場合（SELECT AVG(Salary) FROM Sample.Employee WHERE Name %STARTSWITH 'Smith,' ）、WHERE条件を適用するためにデータ行を読み取る必要があるため、ビットスライスは実際には使用されません。

    行が頻繁に作成または削除されるテーブルのビットスライスとビットマップインデックスについても、同様のストレージに関する懸念があります。

    データ - グローバルノードに格納されているデータのインデックス。

    Index QuickSearchIDX On Name [ Data = (SSN, DOB, Name) ];

    前のいくつのかの例で、「~Sample.Employee~」という文字列がノード自体に値として格納されていることに気づいたかもしれません。 Sample.Employeeは、Sample.Personからインデックスを継承していることを思い出してください。 特にEmployeesをクエリする場合、プロパティ条件に一致するインデックスノードの値を読み取り、そのPersonがEmployeeでもあることを確認します。

    また、格納する値を明示的に定義することもできます。 インデックスグローバルノードでデータを定義すると、データグローバルの読み取りも保存できます。頻繁な選択クエリや順序付けクエリに役立ちます。

    上記のインデックスを例とすると、 名前の全部または一部を指定された人物に関する識別情報を取得する場合（フロントデスクアプリケーションでクライアントの情報を検索する場合など）、「SELECT SSN, Name, DOB FROM Sample.Person WHERE Name %STARTSWITH 'Smith,J' ORDER BY Name」というクエリを実行できます。 Nameをクエリ条件としており、取得しようとしている値はすべてQuickSearchIDXグローバルノード内に格納されているため、このクエリを実行するには、グローバルのみを読み取る必要があります。

    データ値は、ビットマップまたはビットスライスインデックスと保存できないことに注意してください。

    ^Sample.PersonI("QuickSearchIDX"," LARSON,KIRSTEN A.",100115)=$lb("~Sample.Employee~","555-55-5555",51274,"Larson,Kirsten A.")

    iFindインデックス

    このようなインデックスを聞いたことがあるでしょうか？ 私にもありません。iFindインデックスは、ストリームプロパティで使用されますが、これを使用するには、クエリにキーワードで名前を指定する必要があります。

    もう少し説明することもできますが、このことについては、Kyle Baxterがすでに有用な記事を執筆しています。

    フリーテキスト検索：SQL開発者が秘密にしているテキストフィールドの検索方法 

    ［最終更新日: 2020年4月16日 - 可読性を調整。］

    #Caché #InterSystems IRIS #SQL #インデックス付け #パフォーマンス #ベストプラクティス
    0
    0 579
    記事 Toshihiko Minamoto · 4月 19, 2021 8m read

    配列プロパティ要素のための SQL インデックス

    クラスの中で配列プロパティを使い、その要素 (キーと値の両方) によってスピーディに検索を実行できると非常に便利な場合があります (EAV モデルの場合は特に重宝します)。

    それでは、簡単な例を見てみましょう。

    <FONT COLOR="#000080">Class User.eav Extends %Persistent
</FONT><FONT COLOR="#000000">{

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx1 On attributes(ELEMENTS) [ </FONT><FONT COLOR="#000080">Data </FONT><FONT COLOR="#000000">= entity ];
    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx2 On (attributes(KEYS), attributes(ELEMENTS)) [ </FONT><FONT COLOR="#000080">Data </FONT><FONT COLOR="#000000">= entity ];
    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">entity;
    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">attributes </FONT><FONT COLOR="#000080">As array Of %String</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">SQLTABLENAME </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"attributes"</FONT><FONT COLOR="#000000">) [ </FONT><FONT COLOR="#000080">SqlFieldName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">attr </FONT><FONT COLOR="#000000">];
    </FONT><FONT COLOR="#000080">/// d ##class(User.eav).RepopulateAll()
ClassMethod </FONT><FONT COLOR="#000000">RepopulateAll()
{
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%DeleteExtent</FONT><FONT COLOR="#000000">()
  
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">name</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$TR</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Sibe^rian pi^ne ce^dar"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"^"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(769))
  
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">=..</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">entity</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Human"
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(22,</FONT><FONT COLOR="#008000">"Age"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(186,</FONT><FONT COLOR="#008000">"Height"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Jack"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Name"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()
      </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">=..</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">entity</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Tree"
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(186,</FONT><FONT COLOR="#008000">"Age"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(22,</FONT><FONT COLOR="#008000">"Height"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"Pines"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Family"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">attributes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">SetAt</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">name</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"Name"</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()
  
  </FONT><FONT COLOR="#008000">/*
  
  ;or
  
  &sql(insert into eav(entity) select 'Human' union select 'Tree')
  &sql(insert into attributes(eav,element_key,attr)
  select 1,'Age',22 union
  select 1,'Height',186 union
  select 1,'Name','Jack' union
  select 2,'Age',186 union
  select 2,'Height',22 union
  select 2,'Family','Pines' union
  select 2,'Name',:name)
  */
  
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">Reindex</FONT><FONT COLOR="#000000">()
}
    </FONT><FONT COLOR="#000080">/// d ##class(User.eav).Reindex()
ClassMethod </FONT><FONT COLOR="#000000">Reindex()
{
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%BuildIndices</FONT><FONT COLOR="#000000">(,1)
      </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TuneTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"SQLUser.eav"</FONT><FONT COLOR="#000000">,1)
  </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TuneTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"SQLUser.attributes"</FONT><FONT COLOR="#000000">,1)
  </FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Compile</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#008000">"cu/multicompile=1"</FONT><FONT COLOR="#000000">)
}
    }</FONT>
    データを読み込んだ後に、以下を実行します。
    USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">User.eav</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">RepopulateAll</FONT><FONT COLOR="#000000">()</FONT>
    すると、テーブルに以下のデータが表示されます。
    IDentity
    eav テーブル
    1Human
    2Tree
    eavIDattrelement_key
    attributes テーブル
    11||Age22Age
    11||NameJackName
    11||Height186Height
    22||Age186Age
    22||Height22Height
    22||NameSibérian píne cédarName
    22||FamilyPinesFamily
    データを持つグローバル
    USER>zw ^User.eavD
^User.eavD=2
^User.eavD(1)=$lb("","Human")
^User.eavD(1,"attributes","Age")=22
^User.eavD(1,"attributes","Height")=186
^User.eavD(1,"attributes","Name")="Jack"
^User.eavD(2)=$lb("","Tree")
^User.eavD(2,"attributes","Age")=186
^User.eavD(2,"attributes","Family")="Pines"
^User.eavD(2,"attributes","Height")=22
^User.eavD(2,"attributes","Name")="Sibérian píne cédar"
    インデックスを持つグローバル
    USER>zw ^User.eavI
^User.eavI("idx1"," 186",1)=$lb("","Human")
^User.eavI("idx1"," 186",2)=$lb("","Tree")
^User.eavI("idx1"," 22",1)=$lb("","Human")
^User.eavI("idx1"," 22",2)=$lb("","Tree")
^User.eavI("idx1"," JACK",1)=$lb("","Human")
^User.eavI("idx1"," PINES",2)=$lb("","Tree")
^User.eavI("idx1"," SIBÉRIAN PÍNE CÉDAR",2)=$lb("","Tree")
^User.eavI("idx2","Age"," 186",2)=$lb("","Tree")
^User.eavI("idx2","Age"," 22",1)=$lb("","Human")
^User.eavI("idx2","Family"," PINES",2)=$lb("","Tree")
^User.eavI("idx2","Height"," 186",1)=$lb("","Human")
^User.eavI("idx2","Height"," 22",2)=$lb("","Tree")
^User.eavI("idx2","Name"," JACK",1)=$lb("","Human")
^User.eavI("idx2","Name"," SIBÉRIAN PÍNE CÉDAR",2)=$lb("","Tree")

    それでは、以下のクエリを実行しましょう。

    entity
    select entity from eav where attributes->attr = 22
    Human
    Tree

    クエリは実行されるものの、インデックスではなくフルスキャンが使われています。 SMP (System Management Portal) のテーブルを見ると、そこには生成されたはずの idx1 と idx2 がありません。

    これが起るのは、SQL エンジンには、サブテーブルの配列のフィールドだけを基に作成された配列プロパティで、キー (すなわち propArray(KEY)) を持つもののインデックスしか「見えない」ためです。 両方のインデックスに「entity」フィールドがあるにもかかわらず、「attribute」サブテーブルにはそれが見当たりません。

    また、attributes(KEYS) を持たない <FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx3 On attributes(ELEMENTS);</FONT> も表示されません。但し、こちらのインデックス

    • <FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx4 On (attributes(KEYS), attributes(ELEMENTS));</FONT>
    • <FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">idx5 On (attributes(ELEMENTS), attributes(KEYS));</FONT>
    は表示され、クエリに考慮されます。 しかし、これらはあらゆる種類のクエリに最適という訳ではありません。

    では、SQL エンジンが配列プロパティ要素のインデックスを見えるようにするには、どのメソッドを使うのが一番簡単なのでしょうか？

    Caché 2015.1 では、SetCollectionProjection/GetCollectionProjection メソッドを使ってサブテーブルに表示できるコレクションであれば、それをテーブルフィールドとして表示することができます。

    ですが、この機能はデフォルトで無効になっています。

    これらのメソッドは、Caché の過去のバージョンにはありませんが、手動で有効化できるかを試すことはできます。

    %SYS><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#000000">^%SYS(</FONT><FONT COLOR="#008000">"sql"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"sys"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"collection projection"</FONT><FONT COLOR="#000000">)=1</FONT>

    この変更を実行した後は、必ずクラスをもう一度コンパイルしてください。

    それでは、このパラメーターをオン (1) にして、その動作を見てみましょう。

    SMP にインデックスが表示されるようになりました。また、「eav」テーブルには「attr」という隠れたコレクションフィールドがあります。 しかし、それでも私たちが実行したクエリはインデックス idx1/idx2 を見つけられません。

    それでは、お馴染みの述語 FOR SOME %ELEMENT を使って、この状況を解決しましょう。

    entity
    select entity from eav where for some %element(attr) (%value = 22)
    Human
    Tree

    インデックス idx1 がクエリで使用されています。 これを少しだけ変更します。

    entity
    select entity from eav where for some %element(attr) (%value = 22 and %key= 'Age')
    Human
    entity
    select entity from eav where for some %element(attr) (%value = 22 and %key= 'Height')
    Tree
    最後の 2 つの例では、インデックス idx1 の代わりにインデックス idx2 が使用されています。

    UPD: SQLPROJECTION を使えば、これと同じことができます。つまり、以下を実行します。

    <FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">attributes </FONT><FONT COLOR="#000080">As array Of %String</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">SQLPROJECTION </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"table/column"</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SQLTABLENAME </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"attributes"</FONT><FONT COLOR="#000000">) [ </FONT><FONT COLOR="#000080">SqlFieldName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">attr </FONT><FONT COLOR="#000000">];</FONT>

    この記事は、こちらの記事を翻訳したものです。 [@Evgeny Shvarov]、翻訳作業にご協力いただきありがとうございました。

    この記事は、Habrahabr露 でもお読みいただけます。

    本記事を書くきっかけとなったサイト: 17383689露

    WRC のフレームワークについてヒントをくださった [@Alexander.Koblov] に深く感謝します。

    #Caché #ObjectScript #SQL #インデックス付け #パフォーマンス
    0
    0 405
    記事 Toshihiko Minamoto · 4月 12, 2021 24m read

    アトミックでない属性のインデックス作成

    (1NF/2NF/3NF)露 からの引用

    行と列で特定される位置には、それぞれアプリケーションドメインの値が 1 つだけあります (それ以外は何もない)。 その目的によって、同じ値がアトミックであったり、なかったりします。 例えば、「4286」という値は、
    • 「クレジットカードの PIN コード」を意味するのであれば、アトミックとなります (破損している場合や並び替えられている場合は、使用できません)。
    • 単に「連続する番号」であれば、非アトミックとなります (いくつかに分割されていたり、並び替えられていても、値は意味を成します)。

    この記事では、文字列や日付、($LB 形式の) 単純なリスト、「list of <...>」、「array of <...>」といったフィールドの型を伴う SQL クエリのパフォーマンスを向上させる標準的な方法にして検証します。


    はじめに


    それでは、お馴染みの「電話番号の一覧」から見てみましょう。 以下のように、テストデータを作成します。

    create table cl_phones(tname varchar2(100), phone varchar2(30));
insert into cl_phones(tname,phone) values ('Andrew','867-843-25');
insert into cl_phones(tname,phone) values ('Andrew','830-044-35');
insert into cl_phones(tname,phone) values ('Andrew','530-055-35');
insert into cl_phones(tname,phone) values ('Max','530-055-35');
insert into cl_phones(tname,phone) values ('Max','555-011-35');
insert into cl_phones(tname,phone) values ('Josh','530-055-31');
insert into cl_phones(tname,phone) values ('Josh','531-051-32');
insert into cl_phones(tname,phone) values ('Josh','532-052-33');
insert into cl_phones(tname,phone) values ('Josh','533-053-35');

    では、各人物が持つ電話番号をコンマで区切ってまとめたリストを表示します。

    SELECT
   %exact(tname) tname,
   LIST(phone) phonestr
FROM cl_phones
GROUP BY tname

    または以下のコードを使います。

    SELECT
   distinct %exact(tname) tname,
   LIST(phone %foreach(tname)) phonestr
FROM cl_phones
    tnamephonestr
    結果
    Andrew 867-843-25,830-044-35,530-055-35
    Josh 530-055-31,531-051-32,532-052-33,533-053-35
    Max 530-055-35,555-011-35

    インデックスを電話番号別に作成すれば、特定の番号を使って素早く検索することができます。 このソリューションの唯一の欠点は、名前が重複する可能性があるということです。リストの要素が多くなれば、その分データベースも大きくなります。

    従い、同じフィールドに複数の値 (電話番号の一覧やその一部だけを集めた一覧、パスワードなど) をコンマで区切った文字列として格納しておくと、値別に素早く検索できるので、重宝することがあります。 もちろん、そのようなフィールドに対し普通のインデックスを作成し、長い文字列の中の部分文字列を検索することは可能です。しかし、第 1 にそのような要素が数多く含まれる可能性があり、またインデックスも非常に長くなり得るということ、そして第 2 に、そのようなインデックスを使用しても検索処理をまったく加速化できないという点を考慮する必要があります。

    そうでは、どうすれば良いのでしょう。

    このように、コレクションを持つフィールドを扱う状況に対処するために、インデックスの特殊な型が導入されています。 コレクションは「実在するもの」(組み込みの list of <...> および array of <...>) でも「仮想のもの」でも構いません。

    組み込みコレクションの場合、それ専用にこのようなインデックスを作成するのはシステムの仕事であり、開発者はそのプロセスに干渉できません。 但し、仮想コレクションであれば、インデックスの作成は開発者が担います。

    そのような「仮想」コレクションには、シンプルなコンマ区切りの文字列、日付、単純なリストなどがあります。

    コレクションのインデックスには、以下の構文を用います。

    INDEX idx1 ON (MyField(ELEMENTS));
    もしくは
    INDEX idx1 ON (MyField(KEYS));

    インデックスは、propertynameBuildValueArray メソッドを使って作成しますが、これは開発者が自分で実装しなければいけません。

    通常、このメソッドのシグネチャは以下のようになります。 

    ClassMethod propertynameBuildValueArray(value, ByRef valueArray) As %Status

    引数の意味

    • value – 複数の要素に分割されるフィールド値
    • valueArray – 個別の要素を含む結果の配列。 配列は、キー / 値の組み合わせで、以下の形式が使われます。 array(key1)=value1 array(key2)=value2 などなど。

    先ほども触れましたが、このメソッドはシステムにより組み込みコレクション用に自動的に生成されるものであり、属性が [Final] であるため、開発者は再定義できません。

    それでは、これらのインデックスを作成し、SQL での活用方法を見てみましょう。

    注意:
     前の例で作られた構造が残らないよう、新しいインデックスを作成する前にグローバルやクラスのストレージスキーマを空にしておくことをおすすめします。

    コンマ区切りの文字列


    では、次のクラスを作成しましょう。 

    Class demo.test Extends %Persistent
{

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iPhones On Phones(ELEMENTS);
    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">Phones </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">PhonesBuildValueArray(
  </FONT><FONT COLOR="#ff00ff">value</FONT><FONT COLOR="#000000">,
  </FONT><FONT COLOR="#000080">ByRef </FONT><FONT COLOR="#ff00ff">array</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">As %Status
</FONT><FONT COLOR="#000000">{
  </FONT><FONT COLOR="#0000ff">i </FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"" </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(0)=</FONT><FONT COLOR="#800000">value
  </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">else</FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">list</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$lfs</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">","</FONT><FONT COLOR="#000000">),</FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">=0
    </FONT><FONT COLOR="#0000ff">while $listnext</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">list</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">item</FONT><FONT COLOR="#000000">)</FONT><FONT COLOR="#800080">{
      </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#800000">item
    </FONT><FONT COLOR="#800080">}
  }
  </FONT><FONT COLOR="#0000ff">q $$$OK
</FONT><FONT COLOR="#000000">}
    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill()
{
  </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT>
  <FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">phones</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">null union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'a' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'b,a' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'b,b' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'a,c,b' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">',,'
  </FONT><FONT COLOR="#800080">)
  </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI
}
}</FONT>

    ターミナルで Fill() メソッドを呼び出します。

    USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">demo.test</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Fill</FONT><FONT COLOR="#000000">()</FONT>
<FONT COLOR="#000000">^demo.testD=6
^demo.testD(1)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">)
^demo.testD(2)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"a"</FONT><FONT COLOR="#000000">)
^demo.testD(3)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"b,a"</FONT><FONT COLOR="#000000">)
^demo.testD(4)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"b,b"</FONT><FONT COLOR="#000000">)
^demo.testD(5)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"a,c,b"</FONT><FONT COLOR="#000000">)
^demo.testD(6)=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">",,"</FONT><FONT COLOR="#000000">)
^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" "</FONT><FONT COLOR="#000000">,1)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" "</FONT><FONT COLOR="#000000">,6)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" A"</FONT><FONT COLOR="#000000">,2)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" A"</FONT><FONT COLOR="#000000">,3)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" A"</FONT><FONT COLOR="#000000">,5)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" B"</FONT><FONT COLOR="#000000">,3)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" B"</FONT><FONT COLOR="#000000">,4)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" B"</FONT><FONT COLOR="#000000">,5)=</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">
^demo.testI(</FONT><FONT COLOR="#008000">"iPhones"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" C"</FONT><FONT COLOR="#000000">,5)=</FONT><FONT COLOR="#008000">""</FONT>

    ご覧のとおり、インデックスには文字列全体ではなく、その特定の部分だけが含まれています。 従って開発者は好きなように長い文字列を複数の部分文字列に分割できます。 コンマ区切りの文字列以外にも、XML や JSON ファイルなども使えます。

    ID電話番号
    私たちのテーブルには以下が含まれます。
    1 (null)
    2 a
    3 b,a
    4 b,b
    5 a,c,b
    6 ,,

    それでは、「a」を含む部分文字列をすべて見つけましょう。 このためには、'%xxx%' や ['xxx' といった述語を使うことがルールになっています。 

    select * from demo.test where Phones [ 'a'select * from demo.test where Phones like '%a%'

    しかし、この場合、iPhones インデックスは使用されません。 使用するには、特殊な述語を使う必要があります。 

    FOR SOME %ELEMENT(ourfield) (%VALUE = elementvalue)

    これを考慮して、以下のようなクエリを使います。 

    select * from demo.test where for some %element(Phones) (%value = 'a')

    特殊なインデックスを使ったおかげで、このクエリの処理速度は最初のバージョンを使った場合よりも大幅に速くなります。

    もちろん、以下のようにもっと複雑な条件を使うこともできます。(%Value %STARTSWITH 'а')(%Value [ 'a' and %Value [ 'b')(%Value in ('c','d'))(%Value is null)

    ここでさっと魔法をかけます。。。


    機密情報を隠す


    通常、BuildValueArray メソッドでは、value を使って array 配列を作成します。

    でも、このルールに従わなかったらどうなるのでしょう？

    次の例を試してみましょう。

    Class demo.test Extends %Persistent
{

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iLogin On Login(ELEMENTS);
    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">Login </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">LoginBuildValueArray(
  </FONT><FONT COLOR="#ff00ff">value</FONT><FONT COLOR="#000000">,
  </FONT><FONT COLOR="#000080">ByRef </FONT><FONT COLOR="#ff00ff">array</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">As %Status
</FONT><FONT COLOR="#000000">{
  </FONT><FONT COLOR="#0000ff">i </FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Jack" </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(0)=</FONT><FONT COLOR="#008000">"test1"
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(1)=</FONT><FONT COLOR="#008000">"test2"
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(2)=</FONT><FONT COLOR="#008000">"test3"
  </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">elseif </FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Pete" </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"-"</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#008000">"111"
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"5.4"</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#008000">"222"
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"fg"</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#008000">"333"
  </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">else</FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"key"</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#008000">"value"
  </FONT><FONT COLOR="#800080">}
  </FONT><FONT COLOR="#0000ff">q $$$OK
</FONT><FONT COLOR="#000000">}
    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill()
{
  </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT><FONT COLOR="#800080">
  &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">login</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Jack' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Jack' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Pete' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'Pete' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'John' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'John'
  </FONT><FONT COLOR="#800080">)
  </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI
}
}</FONT>
    IDLogin
    配列を作成した後、テーブルの中身は以下のようになります。
    1Jack
    2Jack
    3Pete
    4Pete
    5John
    6John

    そして、肝心なのはここです！ 次のクエリを実行してみましょう。

    select * from demo.test where for some %element(Login) (%value = '111')
    IDLogin
    実行結果は以下のようになります。
    3Pete
    4Pete

    結果、一部のデータだけがテーブルに表示されています。また、インデックスでは非表示になったデータがいくつかありますが、それでも検索はできます。 これはどのように使えばいいのでしょう？

    例えば、特定のユーザーがアクセスできる一連のパスワードを、インデックスから (1 つだけではなく) すべて非表示にすることができます (通常 1 つだけ非表示にすることはできません)。 また、SQL を使って開かれては困るという機密情報があれば、それをすべて非表示にすることもできます。 もちろん、これは、GRANT column-privilege を実行するなど、他の方法でも行えます。 しかし、その場合は、protected フィールドにアクセスするためのストアドプロシージャを使う必要があります。


    機密情報を隠す(続き)


    私たちのテーブルのデータとインデックスを含むグローバルを見れば、「5.4」や「fg」といったキーの値が表示されていないのが分かります。

    ^demo.testD=6
^demo.testD(1)=$lb("","Jack")
^demo.testD(2)=$lb("","Jack")
^demo.testD(3)=$lb("","Pete")
^demo.testD(4)=$lb("","Pete")
^demo.testD(5)=$lb("","John")
^demo.testD(6)=$lb("","John")
^demo.testI("iLogin"," 111",3)=""
^demo.testI("iLogin"," 111",4)=""
^demo.testI("iLogin"," 222",3)=""
^demo.testI("iLogin"," 222",4)=""
^demo.testI("iLogin"," 333",3)=""
^demo.testI("iLogin"," 333",4)=""
^demo.testI("iLogin"," TEST1",1)=""
^demo.testI("iLogin"," TEST1",2)=""
^demo.testI("iLogin"," TEST2",1)=""
^demo.testI("iLogin"," TEST2",2)=""
^demo.testI("iLogin"," TEST3",1)=""
^demo.testI("iLogin"," TEST3",2)=""
^demo.testI("iLogin"," VALUE",5)=""
^demo.testI("iLogin"," VALUE",6)=""

    そもそも、なぜこれらを定義しているのか？

    その疑問に答えるには、インデックスを少し変更して、テーブルを作成しなおします。 

    Index iLogin On (Login(KEYS), Login(ELEMENTS));

    これで、グローバルは見た目が変わります (インデックス付きのグローバルのみ表示しています)。

    ^demo.testI("iLogin"," -"," 111",3)=""
^demo.testI("iLogin"," -"," 111",4)=""
^demo.testI("iLogin"," 0"," TEST1",1)=""
^demo.testI("iLogin"," 0"," TEST1",2)=""
^demo.testI("iLogin"," 1"," TEST2",1)=""
^demo.testI("iLogin"," 1"," TEST2",2)=""
^demo.testI("iLogin"," 2"," TEST3",1)=""
^demo.testI("iLogin"," 2"," TEST3",2)=""
^demo.testI("iLogin"," 5.4"," 222",3)=""
^demo.testI("iLogin"," 5.4"," 222",4)=""
^demo.testI("iLogin"," FG"," 333",3)=""
^demo.testI("iLogin"," FG"," 333",4)=""
^demo.testI("iLogin"," KEY"," VALUE",5)=""
^demo.testI("iLogin"," KEY"," VALUE",6)=""

    これで、見事にキーの値と要素の値の両方が格納されています。 これは、今後どのように活用できるのでしょう？

    例えば、先ほどのパスワードを使った例では、パスワードをその有効期限や他の情報と一緒に保存することができます。 クエリでは、次のようにして行います。

    select * from demo.test where for some %element(Login) (%key='-' and %value = '111')

    データをどこに保存するかは、あなた次第です。但し、キーは一意ですが、値はそうでないことを覚えておきましょう。

    また、「コレクション」インデックスは、普通のインデックスと同様、追加データを保存するのに使用できます。

    Index iLogin On (Login(KEYS), Login(ELEMENTS)) [ Data = (Login, Login(ELEMENTS)) ];

    この場合、上のクエリはデータにアクセスせずに、インデックスからすべてのデータを取得してくれるので、時間を節約できます。


    日付 (時刻など)


    日付はコレクションにどう関連しているのか、と疑問に思う方がいると思います。 答えはズバリ「ダイレクトに関連している」です。それは、日付や月、年だけによって検索する必要のあることが頻繁にあるためです。 通常の検索では効果はありません。ここで必要なのは、まさに「コレクションベース」の検索なのです。

    ここで、次の例を見てみましょう。

    Class demo.test Extends %Persistent
{

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iBirthDay On (BirthDay(KEYS), BirthDay(ELEMENTS));
    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">BirthDay </FONT><FONT COLOR="#000080">As %Date</FONT><FONT COLOR="#000000">;
    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">BirthDayBuildValueArray(
  </FONT><FONT COLOR="#ff00ff">value</FONT><FONT COLOR="#000000">,
  </FONT><FONT COLOR="#000080">ByRef </FONT><FONT COLOR="#ff00ff">array</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">As %Status
</FONT><FONT COLOR="#000000">{
  </FONT><FONT COLOR="#0000ff">i </FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"" </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(0)=</FONT><FONT COLOR="#800000">value
  </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">else</FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">d</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$zd</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">,3)
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"yy"</FONT><FONT COLOR="#000000">)=+</FONT><FONT COLOR="#0000ff">$p</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"-"</FONT><FONT COLOR="#000000">,1)
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"mm"</FONT><FONT COLOR="#000000">)=+</FONT><FONT COLOR="#0000ff">$p</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"-"</FONT><FONT COLOR="#000000">,2)
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"dd"</FONT><FONT COLOR="#000000">)=+</FONT><FONT COLOR="#0000ff">$p</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"-"</FONT><FONT COLOR="#000000">,3)
  </FONT><FONT COLOR="#800080">}
  </FONT><FONT COLOR="#0000ff">q $$$OK
</FONT><FONT COLOR="#000000">}
    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill()
{
  </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT><FONT COLOR="#800080">
  &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">birthday</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2000-01-01'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2000-01-02'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2000-02-01'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2001-01-01'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2001-01-02'</FONT><FONT COLOR="#000000">} </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2001-02-01'</FONT><FONT COLOR="#000000">}
  </FONT><FONT COLOR="#800080">)
  </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI
}
}</FONT>
    IDBirthDay
    配列を作成した後、テーブルの中身は以下のようになります。
    101.01.2000
    202.01.2000
    301.02.2000
    401.01.2001
    502.01.2001
    601.02.2001

    これで、簡単に、とてもスピーディに 日付の特定の部分を検索できます。 例えば、以下のようにすれば 2 月生まれの人を全員選択できます。

    select * from demo.test where for some %element(BirthDay) (%key='mm' and %value = 2)
    IDBirthDay
    結果
    301.02.2000
    601.02.2001

    単純なリスト


    Caché DBMS では、単純なリストを対象とした特殊なデータ型 (%List) が備えられています。どの区切り記号を使うかを決められなくて困っている開発者は、文字列の代わりに使えるので便利です。

    このフィールドの使い方は、文字列の使い方にとても似ています。

    それでは、簡単な例を見てみましょう。

    Class demo.test Extends %Persistent
{

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iList On List(ELEMENTS);
    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">List </FONT><FONT COLOR="#000080">As %List</FONT><FONT COLOR="#000000">;
    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">ListBuildValueArray(
  </FONT><FONT COLOR="#ff00ff">value</FONT><FONT COLOR="#000000">,
  </FONT><FONT COLOR="#000080">ByRef </FONT><FONT COLOR="#ff00ff">array</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">As %Status
</FONT><FONT COLOR="#000000">{
  </FONT><FONT COLOR="#0000ff">i </FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"" </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(0)=</FONT><FONT COLOR="#800000">value
  </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">else</FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">=0
    </FONT><FONT COLOR="#0000ff">while $listnext</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">value</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">item</FONT><FONT COLOR="#000000">)</FONT><FONT COLOR="#800080">{
      </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">array</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">ptr</FONT><FONT COLOR="#000000">)=</FONT><FONT COLOR="#800000">item
    </FONT><FONT COLOR="#800080">}
  }
  </FONT><FONT COLOR="#0000ff">q $$$OK
</FONT><FONT COLOR="#000000">}
    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill()
{
  </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT><FONT COLOR="#800080">
  &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">list</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">null union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'c'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a,,'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">null</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">null</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#800080">)
  </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI
}
}</FONT>
    IDList
    テーブルの中身は、ODBC のディスプレイモードで表示すると以下のようになります。
    1 (null)
    2 a
    3 b,a
    4 b,b
    5 a,c,b
    6 "a,,",,
    注意
     Caché では、論理、ODBC、ディスプレイモード (まとめてデータディスプレイオプション) の 3 種類のデータプレゼンテーションモードがサポートされています。

    ここでは、要素の区切り記号が使われていないため、要素の中では好きな文字を使うことができます。

    ODBC モードで %List 型のフィールドを表示するときは、ODBCDELIMITER パラメーターが区切り記号として使用されます (デフォルトで「,」と同じ)。

    例えば、そのようなフィールドを表示する場合

    Property List As %List(ODBCDELIMITER = "^");
    IDList
    を実行すると、テーブルは以下のようになります。
    1 (null)
    2 a
    3 b^a
    4 b^b
    5 a^c^b
    6 a,,^^

    要素の検索は、コンマ区切りの文字列を検索する場合と同じです。

    select * from demo.test where for some %element(List) (%value = 'a,,')
    IDList
    6 a,,^^

    %INLIST のオプションは、未だ「コレクション」インデックスを使用できないため、上に紹介した例よりも処理速度が遅くなります。

    select * from demo.test where 'a,,' %inlist List

    コレクション


    それでは、上の例を書き直しましょう。単純なリストの代わりにコレクションのリストを使います。

    Class demo.test Extends %Persistent
{

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iListStr On ListStr(ELEMENTS);
    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">ListStr </FONT><FONT COLOR="#000080">As list Of %String</FONT><FONT COLOR="#000000">;
    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill()
{
  </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT><FONT COLOR="#800080">
  &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">liststr</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">null union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'c'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'b'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">$LISTBUILD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'a,,'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">null</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#000080">null</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#800080">)
  </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI
}
}</FONT>

    この例は、中身はほぼ同じですが、微妙に違う点がいくつかあります。 以下にご注意ください。

    • フィールドの COLLATION 値や array のキーおよびインデックス値は、グローバルに保存される前に適切な形に変換されます。 両方の例のグローバルインデックスの値、特に、NULL 値の表記を見比べてください。
    • BuildValueArray メソッドがないため、要素の値しか使えません (キーは使用できない)。
    • フィールドの型に特殊なコレクションクラス (%ListOfDataTypes) が使われている。

    配列コレクション


    上述のとおり、リストではキーを使用できませんが、 配列を使えば、この欠点を解決できます。

    では、次のクラスを作成しましょう。

    Class demo.test Extends %Persistent
{

    </FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">iArrayStr On (ArrayStr(KEYS), ArrayStr(ELEMENTS));
    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">str </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
    </FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">ArrayStr </FONT><FONT COLOR="#000080">As array Of %String</FONT><FONT COLOR="#000000">;
    </FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Fill()
{
  </FONT><FONT COLOR="#0000ff">k </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI</FONT><FONT COLOR="#800080">
  &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">str</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">null union all
  </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'aaa' </FONT><FONT COLOR="#000080">union all
  </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'bbb' </FONT><FONT COLOR="#000080">union all
  </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'bbb' </FONT><FONT COLOR="#000080">union all
  </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'ccc' </FONT><FONT COLOR="#000080">union all
  </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000080">null
  </FONT><FONT COLOR="#800080">)
  &sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test_ArrayStr</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">element_key</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">arraystr</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">1,</FONT><FONT COLOR="#008080">'0'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test1' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">1,</FONT><FONT COLOR="#008080">'1'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test2' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">1,</FONT><FONT COLOR="#008080">'2'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test3' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">2,</FONT><FONT COLOR="#008080">'0'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test1' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">2,</FONT><FONT COLOR="#008080">'1'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test2' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">2,</FONT><FONT COLOR="#008080">'2'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'test3' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">3,</FONT><FONT COLOR="#008080">'-'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'111' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">3,</FONT><FONT COLOR="#008080">'5.4'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'222' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">3,</FONT><FONT COLOR="#008080">'fg'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'333' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">4,</FONT><FONT COLOR="#008080">'-'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'111' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">4,</FONT><FONT COLOR="#008080">'5.4'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'222' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">4,</FONT><FONT COLOR="#008080">'fg'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'333' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">5,</FONT><FONT COLOR="#008080">'key'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'value' </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">6,</FONT><FONT COLOR="#008080">'key'</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'value'
  </FONT><FONT COLOR="#800080">)
  </FONT><FONT COLOR="#0000ff">zw </FONT><FONT COLOR="#000000">^demo.testD,^demo.testI
}
}</FONT>

    説明が必要と思われる点を列挙しておきます。

    • データは、以前と同じように classD の ^name (データ) と classI の ^name (インデックス) という 2 つのグローバルに格納されています。
    • クラスは 1 つですが、テーブルは既に 2 つあります (お馴染みの demo.test と追加でもう 1 つ demo.test_ArrayStr)。
    • demo.test_ArrayStr では、SQL を使って簡単に配列データにアクセスできるほか、以下のフィールドが設けられています。それぞれの名前は一部事前定義されています。 element_key – キーの値 (事前定義されているフィールド名)。 ArrayStr – 要素の値。 test – 親テーブル demo.test へのリンク。 ID – test||element_key 形式のサービスプライマリキー (事前定義されているフィールド名)。
    • フィールドの型には特殊なコレクションクラス (%ArrayOfDataTypes) が使われています。

    以上を踏まえ、Fill() メソッドを実行すると、テーブルの中身は以下のとおりになります。

    IDstr
    demo.test テーブル
    1 (null)
    2 aaa
    3 bbb
    4 bbb
    5 ccc
    6 (null)
    IDtestelement_keyArrayStr
    demo.test_ArrayStr テーブル
    1||010test1
    1||111test2
    1||212test3
    2||020test1
    2||121test2
    2||222test3
    3||5.435.4222
    3||-3-111
    3||fg3fg333
    4||5.445.4222
    4||-4-111
    4||fg4fg333
    5||key5keyvalue
    6||key6keyvalue

    テーブルが 1 つから 2 になった結果、テーブル間で JOIN を使うことを余儀なくされているように思えますが、そうではありません。

    Caché DBMS が提供する SQL のオブジェクト拡張機能について考慮すると、

    demo.test テーブルの str フィールドにある文字列のうち、キーが「-」で要素の値が「111」であるものを表すテストクエリは、以下のようになります。

    <FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">test ID</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">-></FONT><FONT COLOR="#008000">str </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test_ArrayStr </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">element_key</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'-' </FONT><FONT COLOR="#000000">and </FONT><FONT COLOR="#008000">arraystr</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'111'</FONT>
    もしくはこちら
    <FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">%ID</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">str </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">demo</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">test_ArrayStr</FONT><FONT COLOR="#000000">-></FONT><FONT COLOR="#008000">element_key</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'-' </FONT><FONT COLOR="#000000">and </FONT><FONT COLOR="#008000">test_ArrayStr</FONT><FONT COLOR="#000000">-></FONT><FONT COLOR="#008000">arraystr</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008080">'111'</FONT>
    IDstr
    結果
    3bbb
    4bbb

    ご覧のとおり、ここは至って単純で、JOIN も使われていません。これは、すべてのデータが 1 つのグローバルに格納され、Caché がこれらのテーブルの「関係」を把握しているためです。

    これらのフィールドを両方のテーブルから参照できるのは、まさにこれが理由です。 実際、demo.test テーブルに test_ArrayStr フィールドはありませんが、それを使って関連するテーブルにアクセスすることができます。


    結論


    この記事で説明したインデックス作成メカニズムは、一部のシステムクラスで広範に使用されています。それには、テキストストリームのインデックスを作成し、SQL による検索を可能にする %Stream.GlobalCharacterSearchable クラスなどが含まれます。 クラスコレクションのインデックス作成に関するトピックは、非常に多岐にわたるため、意図的に割愛しています (組み込み、格納、ストリーム、ユーザー定義、コレクションのコレクションなど)。 また、SQL による操作が不便な場合もあります。これを踏まえ、著者はそのようなコレクションが必要になることは、一部の極めて稀な状況を除き、ほぼないであろうと判断しました。 フルテキスト検索もまた別のトピックであり、SQL を使う場合には独自のインデックスと操作法を伴うものであるため、本記事ではカバーしておりません。 最後に、著者は、SqlListType や SqlListDelimiter といったプロパティパラメーターの使用例も省いておりますが、好奇心旺盛の方は実際に試す方法を見い出していただけると思います。

    役に立つリンク

    これは、こちらの記事を英訳したものです。 [@Evgeny Shvarov]、翻訳作業にご協力いただきありがとうございました。

    この記事は、Habrahabr露 でもお読みいただけます。

    #Caché #ObjectScript #SQL #インデックス付け #オブジェクトデータモデル #パフォーマンス
    1
    0 170
    記事 Mihoko Iijima · 3月 5, 2021 1m read

    ID を指定してインデックスを再構築する方法

    これは InterSystems FAQ サイトの記事です。

    永続クラス（＝テーブル）定義に提供される %BuildIndices() メソッドの引数に、インデックスを再構築したい ID の開始値と終了値を指定することにより、その範囲内のインデックスのみが再構築できます。
     

    例えば、Sample.Person クラスにある NameIDX インデックスと ZipCode インデックスを ID=10～20 のみ再構築する場合は、以下のように実行します（ID の範囲は、第5引数、第6引数に指定してます）。

     set status = ##class(Sample.Person).%BuildIndices($LB("NameIDX","ZipCode"),1,,1,10,20) 

     

    $LB() は $ListBuild() 関数で、%BuildIndices() メソッドでは、インデックス名を指定するために使用しています。

    インデックスの再構築方法については、ドキュメントもご参照ください。

    2018.1 以下はこちらのドキュメントをご参照ください。

    #Caché #InterSystems IRIS #InterSystems IRIS for Health #SQL #インデックス付け #オブジェクトデータモデル #ヒントとコツ #リレーショナルテーブル
    0
    0 454
    記事 Mihoko Iijima · 12月 20, 2020 3m read

    揮発性テーブルのビットマップ・インデックスの圧縮（維持管理）方法

    これは InterSystems FAQ サイトの記事です

    揮発性テーブル（多数のINSERT、DELETEが行われるテーブル）では、ビットマップ・インデックス用ストレージは徐々に効率が低下する可能性があります。

    例えば、以下の定義からなるデータが数千件あり、一定期間保持した後 TRUNCATE TABLE で一括削除を行うオペレーションが繰り返し行われているとします。

    Class MyWork.MonthData Extends (%Persistent, %Populate)
{
/// 満足度
Property Satisfaction As %String(VALUELIST = ",満足,やや満足,やや不満,不満,");
/// 年齢
Property Age As %Integer(MAXVAL = 70, MINVAL = 20);
Index AgeIdx On Age [ Type = bitmap ];
}

     

    INSERT によってできたビットマップ・インデックスのストレージのイメージ（一部）は以下の通りです。

    #Caché #Ensemble #InterSystems IRIS #InterSystems IRIS for Health #SQL #インデックス付け #ヒントとコツ
    0
    0 299
    記事 Mihoko Iijima · 11月 10, 2020 4m read

    大量データのロードを高速化する方法

    これはInterSystems FAQ サイトの記事です。

    インデックスが複数定義されているクラス／テーブルへ csv 形式等のシーケンシャルファイルから大量データをデータベースに登録する際、推奨される登録方法として、データ登録時インデックスを生成させず、登録完了後に一括でインデックスを生成する 方法があります。

    この方法は、新規に大量のレコードを一括登録する際に最も有効な手段となります。

    ＜メモ＞
    大量のデータを追加登録する際には、既存のデータ量と新規データ量のバランスにより、この手法が有効でないケースもあります。その場合は、インデックスの再構築を範囲指定で行うこともできます。

    説明に使用するクラス定義例は以下の通りです。

    #Caché #Ensemble #InterSystems IRIS #InterSystems IRIS for Health #SQL #インデックス付け #ヒントとコツ
    0
    0 526
    記事 Mihoko Iijima · 11月 6, 2020 4m read

    外れ値について

    これはInterSystems FAQ サイトの記事です。

    テーブルチューニングを行った際に、フィールドに値がほとんど登録されていない（Null）場合や、特定の値がほとんどを占める場合、その値を[外れ値] として除外して選択性計算を行います。 また、外れ値が全レコードの何 % を占めているかの値は [外れ値の選択性] として記録されます。

    InterSystems 製品のクエリオプティマイザは、選択性数値とエクステントサイズを使用してクエリの経路を決定しますが、クラスクエリ、埋め込み SQL に使用しているクエリに外れ値が含まれる場合は、外れ値の選択性が自動的に考慮され、インデックスの使用有無を決定しています。

    ダイナミック SQL 、ODBC／JDBC 経由でのクエリについては、外れ値が Null である場合、自動的に外れ値の選択性が考慮されますが、Null 以外の特定の値が外れ値に検出される場合は、明示的に指示を与えるまで考慮しません。

    詳細は、ドキュメント（異常値に対する述語条件【IRIS】／異常値に対する述語条件【Caché／Ensemble】）をご参照ください。

    #Caché #Ensemble #InterSystems IRIS #InterSystems IRIS for Health #SQL #インデックス付け #ヒントとコツ
    0
    0 552
    記事 Tomoko Furuzono · 9月 15, 2020 14m read

    Cachéでのカスタムインデックスタイプの作成

    Cachéデータベースのオブジェクトおよびリレーショナルデータモデルは、標準、ビットマップ、ビットスライスの3種類のインデックスをサポートします。 これら3つのネイティブタイプに加えて、開発者は独自のカスタムタイプのインデックスを宣言し、バージョン2013.1以降の任意のクラスで使用できます。 たとえば、iFindテキストインデックスは、そのメカニズムを使用しています。

    カスタムインデックスタイプは、挿入、更新、削除を実行するための%Library.FunctionalIndexインターフェースのメソッドを実装するクラスです。 新しいインデックスを宣言するときに、そのようなクラスをインデックスタイプとして指定できます。

    例：

    Property A As %String;
    Property B As %String;
    Index someind On (A,B) As CustomPackage.CustomIndex;

     CustomPackage.CustomIndex クラスは、カスタムインデックスを実装するまさにそのクラスです。

    #Caché #InterSystems IRIS #オブジェクトデータモデル #SQL #インデックス付け #データベース
    0
    0 315
    記事 Tomoko Furuzono · 9月 7, 2020 7m read

    フリーテキスト検索：SQL開発者が秘密にしているテキストフィールドの検索方法*

    アプリケーションに、効率的に検索したいフリーテキストを含むフィールドがありますか？これまで複数の方法を試してみたものの、顧客が要求するパフォーマンスを満たせなかった経験はありませんか？私は変わった手段を使ってあらゆる問題を解決できると思っていませんか。もうご存じですよね。私ができるのは、パフォーマンス低下に対処する優れたソリューションを提供することです。

    いつものように、要約版が必要な場合は記事の最後まで飛ばしてください。ただ、それだと私はがっかりしてしまいますが。

    最近の（2015.1以降の）バージョンのCaché/Ensemble/HealthShareのSAMPLESネームスペースでSample.Companyのバージョンを開くと、擬似ランダムに生成されたテキストであるMissionフィールドが表示されます。このテキストフィールドを検索してみましょう。 私はこの演習のために約256,246社データを生成しましたが、ご自身で必要な数の会社を生成してから同じ手順に従ってください。例えば、次のクエリを実行するとしましょう。

    SELECT * FROM Sample.Company WHERE Mission LIKE ‘% agile %’

    #Caché #InterSystems IRIS #iFind #オブジェクトデータモデル #ObjectScript #SQL #インデックス付け
    0
    0 370
    記事 Minoru Horita · 6月 3, 2020 10m read

    グローバルはデータを保存するための魔法の剣です パート3 - 疎な配列 

    前のパート（1、2）では、ツリーとしてのグローバルを話題に取り上げました。 この記事では、それらを疎な配列と見なします。 

    疎な配列は、ほとんどの値が同一であると想定される配列の種類です。 

    疎な配列は実際には非常に大きいため、同一の要素でメモリを占有することには意味がありません。 したがって、疎な配列を整理し、重複した値の格納にメモリが浪費されないようにすることには意味があります。 

    疎な配列は、J、MATLABなど一部のプログラミング言語では言語の一部になっています。 他の言語では、疎な配列を使用できるようにする特別なライブラリが存在します。 C++の場合は、Eigenなどがあります。 

    次の理由により、グローバルは疎な配列を実装するのに適した候補であると言えます。 

    1. 特定のノード値のみを保存し、未定義のノード値を保存しないこと。 
    1. ノード値のアクセスインターフェースが、多くのプログラミング言語が多次元配列の要素にアクセスするために提供しているものとよく似ていること。 
    Set ^a(1, 2, 3)=5 
Write ^a(1, 2, 3) 
    1. グローバルはデータを格納するためにかなり低レベルの構造を採用しているため、優れたパフォーマンス特性を備えていること（ハードウェアによっては毎秒数十万から数千万のトランザクションを処理可能、1をご覧ください）。 
    #Caché #InterSystems IRIS #キーバリュー #インデックス付け #グローバル #データモデル #パフォーマンス #リレーショナルテーブル #初心者
    0
    0 785