🎱 🛌🏿 ◽️ 非アトミック属性のインデックス作成 👩🏿‍🤝‍👨🏼 🙍🏻 ☝🏼

ウィキペディア（ 1NF ）からの引用：

行と列の各交点には、対応するドメインからの値が1つだけ含まれます（それ以上は含まれません）。

同じ値は、この値の意味に応じて、アトミックまたは非アトミックになります。たとえば、値「4286」は

アトミック 、その意味が「クレジットカードピン」の場合（分割または並べ替えると、意味が失われます）
非アトミック 、その意味が「数字のセット」である場合（パーツに分割または並べ替える場合、意味は失われません）

この記事では、文字列、日付、単純リスト（$ LB形式）、リストコレクション、および配列コレクションのフィールドタイプのSQLクエリを高速化する標準的な方法について説明します。

目次：

はじめに

最初に、電話のリストの例のクラシックバージョンを検討します。

テストデータを作成します。

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

次に、名前ごとに電話のコンマ区切りリストを印刷します。

選択

％exact（ tname ） tname 、

LIST （ phone ） phonestr

FROM cl_phones

GROUP BY tname

または：

選択

異なる％exact（ tname ） tname 、

LIST （ phone ％foreach （ tname ）） phonestr

FROM cl_phones

結果：

tname	phonestr
アンドレイ	867-843-25,830-044-35,530-055-35
ヴァニャ	530-055-31,531-051-32,532-052-33,533-053-35
マキシム	530-055-35,555-011-35

電話でインデックスを作成したので、特定の電話で非常にすばやく検索できます。このソリューションの唯一の欠点は、名前の重複です。リスト内の要素が多いほど、データベースが大きくなります。

したがって、多くの値を一度に1つのフィールドに格納すると便利な場合があります-電話機またはその部品、パスワードなどのリストである場合があります-区切り記号付きの行の形式で、同時に個々の値をすばやく検索できるようにするもちろん、そのようなフィールドに通常のインデックスを作成し、この大きな文字列で部分文字列検索を行うことはできますが、まず、非常に多くの要素が存在する可能性があるため、インデックスの長さが重要になり、次に、そのようなインデックスがありません検索速度を上げる助けにはなりません。

それではどうしますか？

特にこのような場合、コレクションを含むフィールドに特別な種類のインデックスが導入されました。

コレクションは、「実」（ <...>の組み込みリストおよび<...>の 配列）または「仮想」のいずれかです。

ビルトインコレクションの場合、システムはそのようなインデックスを作成する責任があり、プログラマはこのプロセスを変更できません。仮想コレクションの場合、インデックスを作成する責任はプログラマにあります。

セパレータ、日付、単純なリストを含む単純な文字列-これらはそのような「仮想」コレクションの例です。

そのため、コレクションのインデックスの構文は次のとおりです。

INDEX idx1 ON（MyField（ELEMENTS））;

または

INDEX idx1 ON（MyField（KEYS））;

開発者が独自に実装する必要があるpropertyname BuildValueArrayというメソッドは、このようなインデックスを作成するプロセスを担当します。

メソッドの一般的な署名は次のとおりです。

ClassMethod propertynameBuildValueArray（ value 、 ByRef valueArray ） As％ステータス

どこで：

value-要素に分割するためのフィールド値。
valueArrayは、個々の要素を含む結果の配列です。

配列は、次の形式のキー/値セットです。

配列（key1）= value1

配列（key2）= value2

など

前述のように、ビルトインコレクションの場合、このメソッドはシステムによって自動的に生成され、 [Final]属性を持ちます。これにより、開発者はこのメソッドをオーバーライドできません。

そのようなインデックスを構築し、SQLクエリでそれらを使用する方法を見てみましょう。

注：前の例からアーティファクトが残らないように、新しいものが作成される前に、クラスのグローバルとストレージスキームを完全にクリアすることをお勧めします。

区切り文字列

次のクラスを作成します。

クラスdemo.test Extends％Persistent

{

電話でのiPhoneのインデックス作成（要素）;

プロパティ Phones As％String ;

ClassMethod PhonesBuildValueArray（

価値

ByRef array ） As％Status

{

値 = ""の場合 {

セット配列（0）= 値

} else {

リスト = $ ListFromString （ value 、 "、" ）、 ptr = 0を設定します

while $ ListNext （ list 、 ptr 、 item ） {

配列の設定（ ptr ）= アイテム

}

}

$$$ OKを終了OK

}

ClassMethod Fill（）

{

＆sql（ truncate table demo。test ）

＆sql（デモに挿入します。テスト（電話）

NULLユニオンをすべて選択

'a' union allを選択します

'b、a' union allを選択します

「b、b」を選択し、すべてを結合します

「a、c、b」を選択します

「、、」を選択します

）

ZWrite ^ demo.testD

ZWrite ^ demo.testI

}

}

ターミナルでFill （）メソッドを実行します。

USER>do ##class(demo.test).Fill()

^demo.testD=6

^demo.testD(1)=$lb("","")

^demo.testD(2)=$lb("","a")

^demo.testD(3)=$lb("","b,a")

^demo.testD(4)=$lb("","b,b")

^demo.testD(5)=$lb("","a,c,b")

^demo.testD(6)=$lb("",",,")

^demo.testI("iPhones"," ",1)=""

^demo.testI("iPhones"," ",6)=""

^demo.testI("iPhones"," A",2)=""

^demo.testI("iPhones"," A",3)=""

^demo.testI("iPhones"," A",5)=""

^demo.testI("iPhones"," B",3)=""

^demo.testI("iPhones"," B",4)=""

^demo.testI("iPhones"," B",5)=""

^demo.testI("iPhones"," C",5)=""

ご覧のとおり、行はインデックス全体ではなく、個々の部分に入力されています。したがって、1つの大きな行をサブストリングにストリング化する方法は、ユーザー自身が決定します。区切られた行に加えて、これはxml、json、またはその他のものです。

テーブルの内容は次のとおりです。

ID	携帯電話
1	（null）
2	a
3	b、a
4	b、b
5	a、c、b
6	、、

ここで、値「a」を含むすべての行を見つけてください。このために、 '％xxx％'や['xxx'などの述語が通常使用されます。次に例を示します。

デモから*を選択します。電話 [[ a]

デモから*を選択します。「％a％」などの電話の場所をテストする

ただし、この場合、 iPhoneのインデックスは使用されません。それを使用するには、特別な述語を使用する必要があります

FOR SOME %ELEMENT() (%VALUE = )

上記に基づいて、リクエストは次の形式になります。

デモから*を選択します。 some％要素の場所（ Phones ）（％value = 'a' ）をテストします

その結果、特殊なインデックスを使用するため、このクエリの速度は以前のオプションに比べて大幅に高速になります。

もちろん、より複雑な条件も受け入れられます。例えば：

（％値％STARTSWITH 'a' ）

（％値 [ 'a' および％値 [ 'b' ）

（％値（（ 'c' 、 'd' ））

（％値は null ）

そして今、少し魔法...

機密データの隠蔽

BuildValueArrayメソッドでは、通常、valueの値に基づいて配列を設定します。

しかし、この規則に従わないとどうなりますか？

このような例を試してみましょう：

クラスdemo.test Extends％Persistent

{

ログイン時のiLoginのインデックス（要素）;

プロパティ Login As％String ;

ClassMethod LoginBuildValueArray（

価値

ByRef array ） As％Status

{

値 = "Bob"の場合 {

Set array （0）= "test1"

Set array （1）= "test2"

Set array （2）= "test3"

} ElseIf 値 = "Petya" {

配列の設定（ "-" ）= "111"

配列の設定（ "5.4" ）= "222"

配列の設定（ "fg" ）= "333"

} else {

Set array （ "key" ）= "value"

}

$$$ OKを終了OK

}

ClassMethod Fill（）

{

＆sql（ truncate table demo。test ）

＆sql（デモに挿入します。test （ログイン）

'Vasya' union allを選択します

'Vasya' union allを選択します

「Petya」を選択します

「Petya」を選択します

「Ivan」をすべて選択します

「Ivan」を選択します

）

ZWrite ^ demo.testD

ZWrite ^ demo.testI

}

}

記入後、テーブルの内容は次のようになります。

ID	ログイン
1	ヴァシャ
2	ヴァシャ
3	ペティア
4	ペティア
5	イワン
6	イワン

そして今-注意！ -次のリクエストを実行してみてください。

デモから*を選択します。 some％要素の場所をテスト（ Login ）（％value = '111' ）

戻ります：

ID	ログイン
3	ペティア
4	ペティア

その結果、データの一部はテーブルに表示され、一部はインデックスに表示されませんが、それでも検索を実行できます。

これはどこで便利ですか？

たとえば、インデックスでは、通常のように1つではなく、特定のユーザーが使用できるパスワードのセット全体、またはSQLを使用して開くのが望ましくないその他の機密情報を非表示にできます。もちろん、これには他の可能性、たとえばGRANT column-privilegeがあります。ただし、この場合、ストアドプロシージャを使用して保護フィールドにアクセスする必要があります。

機密データの非表示（続き）

テーブルのデータとインデックスが保存されているグローバルを見ると、「5.4」、「fg」などのキーの値は表示されません。

^demo.testD=6

^demo.testD(1)=$lb("","")

^demo.testD(2)=$lb("","")

^demo.testD(3)=$lb("","")

^demo.testD(4)=$lb("","")

^demo.testD(5)=$lb("","")

^demo.testD(6)=$lb("","")

^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)=""

では、なぜ彼らに尋ねたのですか？

この質問に答えるために、インデックスをわずかに変更し、テーブルに再入力します。

インデックス iLogin On（ログイン（KEYS）、ログイン（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)=""

さて、キー値と要素値の両方を保存しました。これは将来どのように役立つでしょうか？

たとえば、以前に提案されたパスワード付きバージョンでは、パスワード自体に加えて、このパスワードが有効になるまでの日付などを保存できます。そして、私たちのリクエストでは、この事実を次のように使用できます。

デモから*を選択します。 some％要素（ Login ）のテスト（％key = '-' および％value = '111' ）

どこに何を保存するかはあなた次第です。覚えておく必要があるのは、キーは一意ですが、値は一意ではないということです。

さらに、「コレクション」インデックスと標準インデックスには、追加のデータを保存できます。

インデックス iLogin On（ログイン（KEYS）、ログイン（ELEMENTS））[ データ =（ログイン、ログイン（ELEMENTS））];

このオプションを使用すると、上記のリクエストはデータにアクセスできなくなりますが、インデックスからすべてを取得するため、時間も節約されます。

日付（時刻など）

日付はコレクションと何の関係があるのでしょうか？ほとんどの場合、日、月、または年のみで検索する必要があるためです。ここでの通常のインデックスは役に立ちませんが、「コレクション」インデックスは非常に適切です。

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

クラスdemo.test Extends％Persistent

{

インデックス iBirthDay On（BirthDay（KEYS）、BirthDay（ELEMENTS））;

プロパティ BirthDay As％Date ;

ClassMethod BirthDayBuildValueArray （

価値

ByRef array ） As％Status

{

値 = ""の場合 {

セット配列（0）= 値

} else {

d = $ zd （値、3）を設定します

Set array （ "yy" ）= + $ p （ d 、 "-" 、1）

Set array （ "mm" ）= + $ p （ d 、 "-" 、2）

Set array （ "dd" ）= + $ p （ d 、 "-" 、3）

}

$$$ OKを終了OK

}

ClassMethod Fill（）

{

＆sql（ truncate table demo。test ）

＆sql（デモに挿入します。テスト（誕生日）

{ d '2000-01-01' } union allを選択します

選択 { d '2000-01-02' } すべてを結合

選択 { d '2000-02-01' } すべてを結合

選択 { d '2001-01-01' } ユニオンすべて

選択 { d '2001-01-02' } すべてを結合

{ d '2001-02-01' }を選択します

）

ZWrite ^ demo.testD

ZWrite ^ demo.testI

}

}

記入後のテーブルの内容は次の形式になります。

ID	誕生日
1	2000年1月1日
2	2000年1月2日
3	2000年2月1日
4	2001年1月1日
5	2001年1月2日
6	2001年2月1日

これは非常にシンプルで、最も重要なことは非常に高速です。たとえば、2月の誕生日をすべて表示するなど、日付の特定の部分を検索できます。

デモから*を選択します。 some％要素（ BirthDay ）のテスト（％key = 'mm' および％value = 2）

結果：

ID	誕生日
3	2000年2月1日
6	2001年2月1日

簡単なリスト

CachéDBMSには、単純なリスト（ ％List ）用の特別なデータ型があり、区切り文字の選択に問題がある場合は、文字列の代わりに使用できます。

このようなフィールドを使用することは、文字列を操作することと大差ありません。

小さな例を考えてみましょう：

クラスdemo.test Extends％Persistent

{

リスト上のiListのインデックス（要素）;

プロパティリスト As％リスト ;

ClassMethod ListBuildValueArray（

価値

ByRef array ） As％Status

{

値 = ""の場合 {

セット配列（0）= 値

} else {

ptr = 0に設定

while $ ListNext （ value 、 ptr 、 item ） {

配列の設定（ ptr ）= アイテム

}

}

$$$ OKを終了OK

}

ClassMethod Fill（）

{

＆sql（ truncate table demo。test ）

＆sql（デモに挿入します。テスト（リスト）

NULLユニオンをすべて選択

$ LISTBUILD （ 'a' ）すべてを結合する

$ LISTBUILD （ 'b' 、 'a' ）すべてを結合する

$ LISTBUILD （ 'b' 、 'b' ）すべてを結合

$ LISTBUILD （ 'a' 、 'c' 、 'b' ）を選択します。

select $ LISTBUILD （ 'a ,,' 、 null 、 null ）

）

ZWrite ^ demo.testD

ZWrite ^ demo.testI

}

}

ODBC表示モードのテーブルの内容は次のようになります。

Note：Cachéには、データを表示するための論理、ODBC、およびデータ表示オプションの 3つのモードがあります。

ID	一覧
1	（null）
2	a
3	b、a
4	b、b
5	a、c、b
6	「A 、、」、、

この場合、要素の区切り文字は関係ないため、要素内の任意の文字を使用できます。

ODBCモードでタイプ％Listのフィールドを出力する場合、 ODBCDELIMITERパラメーターはセパレーターとして使用されます。デフォルトは「、」です。

たとえば、このようなフィールドでは、テーブルは次のようになります。

プロパティリスト As％リスト（ ODBCDELIMITER = "^" ）;

ID	一覧
1	（null）
2	a
3	b ^ a
4	b ^ b
5	a ^ c ^ b
6	、、 ^^

アイテムの検索は、区切られた文字列と同じです。

デモから*を選択します。 some％要素（ List ）のテスト（％value = 'a ,,' ）

ID	一覧
6	「A 、、」、、

％INLISTのオプションはまだ「コレクション」インデックスをまだ使用していないため、上記よりも遅いことに注意してください。

デモから*を選択します。ここで 'a ,,' ％inlist リストをテストします

コレクションリスト

上記の例を書き換えますが、単純なリストを使用する代わりに、リストコレクションを使用します。

クラスdemo.test Extends％Persistent

{

インデックス iListStr on ListStr（ELEMENTS）;

Property ListStr As list Of％String ;

ClassMethod Fill（）

{

＆sql（ truncate table demo。test ）

＆sql（デモに挿入します。test （ liststr ）

NULLユニオンをすべて選択

$ LISTBUILD （ 'a' ）すべてを結合する

$ LISTBUILD （ 'b' 、 'a' ）すべてを結合する

$ LISTBUILD （ 'b' 、 'b' ）すべてを結合

$ LISTBUILD （ 'a' 、 'c' 、 'b' ）を選択します。

select $ LISTBUILD （ 'a ,,' 、 null 、 null ）

）

ZWrite ^ demo.testD

ZWrite ^ demo.testI

}

}

この例では、ほとんどすべてが同じですが、完全ではありません。次のことに特に注意してください。

arrayのフィールド、キー、インデックス要素のCOLLATION値により、グローバルに保存する前に対応する変換を行います。

両方の例のグローバルインデックスの値、特にNULL値の表現を比較してください。
BuildValueArrayメソッドが欠落しているため、キーを使用できず、要素の値のみを使用できます。
フィールドタイプは特別なコレクションクラス（ ％ListOfDataTypes ）です。

配列コレクション

上記のように、コレクションリストではキーを使用できません。配列コレクションはこの欠陥を修正します。

次のクラスを作成します。

クラスdemo.test Extends％Persistent

{

インデックス iArrayStr On（ArrayStr（KEYS）、ArrayStr（ELEMENTS））;

プロパティ str As％String ;

プロパティ ArrayStr As Of Of String String ;

ClassMethod Fill（）

{

＆sql（ truncate table demo。test ）

＆sql（デモに挿入します。test （ str ）

NULLユニオンをすべて選択

'aaa' union allを選択します

'bbb' union allを選択します

'bbb' union allを選択します

'ccc' union allを選択します

nullを選択

）

＆sql（デモに挿入します。test_ArrayStr （ test 、 element_key 、 arraystr ）

選択 1、 '0' 、 'test1' すべてを結合

選択 1、 '1' 、 'test2' すべてを結合

選択 1、 '2' 、 'test3' すべてを結合

select 2、 '0' 、 'test1' すべてを結合

選択 2、 '1' 、 'test2' すべてを結合

2、 '2' 、 'test3'をすべて選択します。

3、「-」、「111」をすべて選択します。

select 3、 '5.4' 、 '222' union all

select 3、 'fg' 、 '333' union all

選択 4、 '-' 、 '111' すべてを結合

「4」、「5.4」、「222」をすべて選択します

4、「fg」、「333」をすべて選択します

5、「キー」、「値」のすべてを選択します

6、「キー」、「値」を選択します

）

ZWrite ^ demo.testD

ZWrite ^ demo.testI

}

}

ここで、いくつかの明確化を行う必要があります。

データは引き続き2つのグローバルに保存されます。^ クラス Dの名前（実際のデータ）と^ クラス Iの名前（インデックス）。
ただし、クラスが1つある場合、すでに2つのテーブルがあります。通常どおり、 demo.testと1つの補助demo.test_ArrayStrです。
demo.test_ArrayStrテーブルは、配列データへの便利なSQLアクセスを提供し、次のフィールドがあります。それらの名前のいくつかは事前定義されています。
- element_key-キー値（フィールド名は事前定義されています）;
- ArrayStr-要素の値。
- test-親テーブルdemo.testへのリンク。
- ID - test || element_key形式のサービス主キー（フィールド名は事前定義されています）;
フィールドタイプは特別なコレクションクラス（ ％ArrayOfDataTypes ）です。

したがって、 Fill （）メソッドの後のテーブルの内容は次のようになります。

テーブルdemo.test

ID	str
1	（null）
2	aaa
3	bbb
4	bbb
5	ccc
6	（null）

テーブルdemo.test_ArrayStr

ID	テストする	element_key	Arraystr
1 \|\| 0	1	0	test1
1 \|\| 1	1	1	test2
1 \|\| 2	1	2	test3
2 \|\| 0	2	0	test1
2 \|\| 1	2	1	test2
2 \|\| 2	2	2	test3
3 \|\| 5.4	3	5.4	222
3 \|\|-	3	-	111
3 \|\| fg	3	fg	333
4 \|\| 5.4	4	5.4	222
4 \|\|-	4	-	111
4 \|\| fg	4	fg	333
5 \|\|キー	5	鍵	価値
6 \|\|キー	6	鍵	価値

1つではなく2つのテーブルがあるため、それらの間でJOINを強制するようになりましたが、そうではありません。

SQL用のCachéDBMSによって提供されるオブジェクト拡張を考慮すると、「-」キーと要素「111」の値を持つ文字列のdemo.testからstrフィールドを表示するテストクエリは、次の形式を取ります。

テストIDを選択し、テストから test- > str を選択します。 test_ArrayStr ここで element_key = '-' および arraystr = '111'

またはそのような

％ID 、デモから strを選択します。 test_ArrayStr- > element_key = '-' および test_ArrayStr- > arraystr = '111'のテスト

結果：

ID	str
3	bbb
4	bbb

ご覧のとおり、すべてのデータは実際には1つのグローバルに格納されており、Cachéはこれらのテーブルの「関連性」を認識しているため、複雑なものもJOINもありません。したがって、フィールドは両方のテーブルから参照できます。 demo.testテーブルのtest_ArrayStrフィールドは実際には存在しませんが、関連するテーブルにアクセスできます。

結論として

ここで説明するインデックスメカニズムは、％Stream.GlobalCharacterSearchableなどの一部のシステムクラスで広く使用されています。これは、テキストストリームのインデックス付けとSQLによる検索を提供します。この記事では、埋め込み、保存、ストリーム、ユーザー定義、コレクションのコレクションを使用するなど、さまざまな観点から、コレクションをクラスにインデックス付けする例を特に取り上げていません。さらに、それらの多くでは、SQLを介して作業することが常に便利で効率的ではないことがあります。また、全文検索については、ここでは説明しません。これは、独自のインデックスと、SQLを介した作業へのアプローチがある別の領域であるためです。さらに、 SqlListTypeやSqlListDelimiterなどのプロパティの使用例は省略されましたが、好奇心reader 盛な読者であれば、それらを独立して実際に試すことができます。

便利なリンク：

非アトミック属性のインデックス作成