Цитаты из википедии (1NF):

Каждое пересечение строки и столбца содержит ровно одно значение из соответствующего домена (и больше ничего).

Одно и то же значение может быть атомарным или неатомарным в зависимости от смысла этого значения. Например, значение «4286» является

атомарным, если его смысл — «пин-код кредитной карты» (при разбиении на части или переупорядочивании смысл теряется)

неатомарным, если его смысл — «набор цифр» (при разбиении на части или переупорядочивании смысл не теряется)

В данной статье будут рассмотрены стандартные способы ускорения SQL-запросов по следующим типам полей: строка, дата, простой список (в формате $LB), коллекции-cписки и коллекции-массивы.

Оглавление:

Строка с разделителем
Скрытие чувствительных данных
Скрытие чувствительных данных (продолжение)
Дата (время и т.д)
Простой список
Коллекция-список
Коллекция-массив
В заключение

Введение

Рассмотрим вначале классический вариант на примере списка телефонов.
Создадим тестовые данные:

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');

Теперь выведем список телефонов, разделённых запятой, для каждого имени:

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

Результат:

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

Построив индекс по phone, мы можем делать очень быстрый поиск по конкретному телефону. Единственный минус такого решения — в дублировании имён: чем больше элементов в списке, тем больше будет пухнуть наша БД.

Поэтому иногда бывает полезно хранить в одном поле сразу множество значений — это может быть список телефонов или его частей, паролей и т.д, — в виде строки с разделителем, и иметь при этом возможность делать быстрый поиск по отдельным значениям. Конечно, можно создать обычный индекс на такое поле и делать поиск подстроки в этой большой строке, но, во-первых, так как элементов может быть довольно много, то и длина индекса будет существенна, а во-вторых, такой индекс всё равно никак нам не поможет ускорить наш поиск.

Так как же быть?

Специально для таких случаев был введён особый вид индекса для полей, содержащих коллекции.
Коллекции могуть быть либо «настоящими» (встроенные 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
{

Index iPhones On Phones(ELEMENTS);

Property Phones As %String;

ClassMethod PhonesBuildValueArray(
  value,
  ByRef array) As %Status
{
  If value="" {
    Set array(0)=value
  }else{
    Set list=$ListFromString(value,","),ptr=0
    While $ListNext(list,ptr,item){
      Set array(ptr)=item
    }
  }
  Quit $$$OK
}

ClassMethod Fill()
{
  &sql(truncate table demo.test)
  &sql(insert into demo.test(phones)
    select null union all
    select 'a' union all
    select 'b,a' union all
    select 'b,b' union all
    select 'a,c,b' union all
    select ',,'
  )

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

Как видим, в индекс попала строка не целиком, а отдельные её части. Таким образом, вы сами решаете как разбить на подстроки одну большую строку. Помимо строк с разделителем это могут быть и xml, и json или что-либо другое.

Содержимое нашей таблицы будет следующим:

ID	Phones
1	(null)
2	a
3	b,a
4	b,b
5	a,c,b
6	,,

Теперь попытаемся найти все строки, содержащие значение «a». Для этого обычно используют предикаты like '%xxx%' или [ 'xxx', например:

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

Но в таком случае не будет использоваться наш индекс iPhones. Чтобы его задействовать, необходимо использовать специальный предикат

FOR SOME %ELEMENT(нашеполе) (%VALUE = значениеэлемента)

С учётом сказанного наш запрос примет следующий вид:

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 мы обычно заполняем массив array на основе значения в value.

Но что будет, если мы не будем следовать этому правилу?

Давайте попробуем такой пример:

Class demo.test Extends %Persistent
{

Index iLogin On Login(ELEMENTS);

Property Login As %String;

ClassMethod LoginBuildValueArray(
  value,
  ByRef array) As %Status
{
  If value="Вася" {
    Set array(0)="test1"
    Set array(1)="test2"
    Set array(2)="test3"
  }ElseIf value="Петя" {
    Set array("-")="111"
    Set array("5.4")="222"
    Set array("fg")="333"
  }else{
    Set array("key")="value"
  }
  Quit $$$OK
}

ClassMethod Fill()
{
  &sql(truncate table demo.test)
  &sql(insert into demo.test(login)
    select 'Вася' union all
    select 'Вася' union all
    select 'Петя' union all
    select 'Петя' union all
    select 'Иван' union all
    select 'Иван'
  )

  ZWrite ^demo.testD
  ZWrite ^demo.testI
}

}

После заполнения, содержимое таблицы окажется таким:

ID	Login
1	Вася
2	Вася
3	Петя
4	Петя
5	Иван
6	Иван

А теперь — внимание! — попробуем выполнить следующий запрос:

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

Нам вернётся:

ID	Login
3	Петя
4	Петя

В итоге мы видим, что часть данных видна в таблице, а часть — запрятана в индекс, но по которым мы, тем не менее, можем делать поиск.

Где же это нам может пригодиться?

Например, в индексе можно скрыть не один, как общепринято, а целый набор доступных заданному пользователю паролей или любую другую чувствительную информацию, которую нежелательно открывать посредством 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)=""

Тогда зачем же мы их задавали?

Чтобы ответить на этот вопрос, немного модифицируем наш индекс и перезаполним таблицу.

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
{

Index iBirthDay On (BirthDay(KEYS), BirthDay(ELEMENTS));

Property BirthDay As %Date;

ClassMethod BirthDayBuildValueArray(
  value,
  ByRef array) As %Status
{
  If value="" {
    Set array(0)=value
  }else{
    Set d=$zd(value,3)
    Set array("yy")=+$p(d,"-",1)
    Set array("mm")=+$p(d,"-",2)
    Set array("dd")=+$p(d,"-",3)
  }
  Quit $$$OK
}

ClassMethod Fill()
{
  &sql(truncate table demo.test)
  &sql(insert into demo.test(birthday)
    select {d '2000-01-01'} union all
    select {d '2000-01-02'} union all
    select {d '2000-02-01'} union all
    select {d '2001-01-01'} union all
    select {d '2001-01-02'} union all
    select {d '2001-02-01'}
  )

  ZWrite ^demo.testD
  ZWrite ^demo.testI
}

}

Содержимое нашей таблицы после заполнения примет следующий вид:

ID	BirthDay
1	01.01.2000
2	02.01.2000
3	01.02.2000
4	01.01.2001
5	02.01.2001
6	01.02.2001

Теперь очень просто, и главное очень быстро, можно делать поиск по конкретным частям даты, например вывести всех февральских именинников:

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

Результат:

ID	BirthDay
3	01.02.2000
6	01.02.2001

Простой список

В СУБД Caché существует специальный тип данных для простого списка (%List), который можно использовать вместо строки, если с выбором разделителя есть затруднения.
Использование такого поля не намного отличается от работы со строкой.

Рассмотрим небольшой пример:

Class demo.test Extends %Persistent
{

Index iList On List(ELEMENTS);

Property List As %List;

ClassMethod ListBuildValueArray(
  value,
  ByRef array) As %Status
{
  If value="" {
    Set array(0)=value
  }else{
    Set ptr=0
    While $ListNext(value,ptr,item){
      Set array(ptr)=item
    }
  }
  Quit $$$OK
}

ClassMethod Fill()
{
  &sql(truncate table demo.test)
  &sql(insert into demo.test(list)
    select null union all
    select $LISTBUILD('a') union all
    select $LISTBUILD('b','a') union all
    select $LISTBUILD('b','b') union all
    select $LISTBUILD('a','c','b') union all
    select $LISTBUILD('a,,',null,null)
  )

  ZWrite ^demo.testD
  ZWrite ^demo.testI
}

}

Содержимое таблицы в режиме отображения ODBC будет выглядеть так:

Примечание: в Caché существует три режима представления данных: логический, ODBC и режим отображения (Data Display Options)

ID	List
1	(null)
2	a
3	b,a
4	b,b
5	a,c,b
6	«a,,»,,

В данном случае разделитель элементов не участвует, поэтому мы можем использовать любые символы в элементах.

При выводе поля типа %List в режиме ODBC в качестве разделителя используется параметр ODBCDELIMITER, который по умолчанию равен ",".

Например, при таком поле наша таблица будет выглядеть так:

Property List As %List(ODBCDELIMITER = "^");

ID	List
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,,')

ID	List
6	«a,,»,,

Особо стоит отметить, что вариант с %INLIST пока не использует «коллекционные» индексы, а потому будет медленне вышепредложенного:

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

Коллекция-список

Перепишем пример выше, но вместо простого списка воспользуемся коллекцией-списком:

Class demo.test Extends %Persistent
{

Index iListStr On ListStr(ELEMENTS);

Property ListStr As list Of %String;

ClassMethod Fill()
{
  &sql(truncate table demo.test)
  &sql(insert into demo.test(liststr)
    select null union all
    select $LISTBUILD('a') union all
    select $LISTBUILD('b','a') union all
    select $LISTBUILD('b','b') union all
    select $LISTBUILD('a','c','b') union all
    select $LISTBUILD('a,,',null,null)
  )

  ZWrite ^demo.testD
  ZWrite ^demo.testI
}

}

В этом примере почти всё то же самое, да не совсем. Особо следует обратить внимание на следующие вещи:

на значения COLLATION наших полей, ключей и элементов индекса в array, которые перед сохранением в глобал претерпевают соответствующие трансформации.
Сравните значения в глобале-индексе в обоих примерах, особенно представление значения NULL;
метод BuildValueArray отсутствует, поэтому мы не можем использовать ключи, а только значения элементов;
типом нашего поля является специальный класс-коллекция (%ListOfDataTypes).

Коллекция-массив

Как выше было отмечено, коллекция-список не даёт нам возможности использовать ключи. Коллекция-массив исправляет этот недостаток.

Создадим следующий класс:

Class demo.test Extends %Persistent
{

Index iArrayStr On (ArrayStr(KEYS), ArrayStr(ELEMENTS));

Property str As %String;

Property ArrayStr As array Of %String;

ClassMethod Fill()
{
  &sql(truncate table demo.test)
  &sql(insert into demo.test(str)
  select null union all
  select 'aaa' union all
  select 'bbb' union all
  select 'bbb' union all
  select 'ccc' union all
  select null
  )
  &sql(insert into demo.test_ArrayStr(test,element_key,arraystr)
    select 1,'0','test1' union all
    select 1,'1','test2' union all
    select 1,'2','test3' union all
    select 2,'0','test1' union all
    select 2,'1','test2' union all
    select 2,'2','test3' union all
    select 3,'-','111' union all
    select 3,'5.4','222' union all
    select 3,'fg','333' union all
    select 4,'-','111' union all
    select 4,'5.4','222' union all
    select 4,'fg','333' union all
    select 5,'key','value' union all
    select 6,'key','value'
  )

  ZWrite ^demo.testD
  ZWrite ^demo.testI
}

}

Здесь нужно сделать несколько пояснений:

наши данные по прежнему хранятся в двух глобалах: ^имя классаD (собственно данные) и ^имя классаI (индексы);
имея один класс, тем не менее мы имеем уже две таблицы: как обычно demo.test и одну вспомогательную 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	test	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\|\|key	5	key	value
6\|\|key	6	key	value

Кажется, что имея вместо одной таблицы две, мы теперь вынуждены делать JOIN между ними, но это не так.

Учитывая предоставляемые СУБД Caché объектные расширения для SQL, наш тестовый запрос, выводящий поле str из demo.test для строк с ключом "-" и значением элемента «111», примет следующий вид:

select test ID,test->str from demo.test_ArrayStr where element_key='-' and arraystr='111'

или такой

select %ID, str from demo.test where test_ArrayStr->element_key='-' and test_ArrayStr->arraystr='111'

Результат:

ID	str
3	bbb
4	bbb

Как видим, ничего сложного и никаких JOIN, поскольку все наши данные фактически хранятся в одном глобале и Caché знает о «родственности» этих таблиц. Поэтому и на поля можно ссылаться из обеих таблиц. Поля test_ArrayStr в таблице demo.test на самом деле не существует, хотя мы и можем через него получить доступ к родственной таблице.

В заключение

Описанный здесь механизм индексации широко используется и в некоторых системных классах, таких, например, как %Stream.GlobalCharacterSearchable, который обеспечивает индексацию текстового потока и поиск по нему через SQL. В статье специально не освещены примеры индексации коллекций на классы, в виду их большого разнообразия: встраиваемые, хранимые, потоки, пользовательские, с коллекциями коллекций и другие. Кроме того, со многими из них не всегда удобно и эффективно работать через SQL, потому автор не видит особой надобности в таких коллекциях кроме каких-то уж очень редких случаев. Также здесь не освещён полнотекстовый поиск, так как это другая область со своими индексами и подходом в работе через SQL. Кроме того были опущены примеры использования у свойств таких параметров как SqlListType и SqlListDelimiter, но пытливый читатель, надеюсь, cможет самостоятельно попробовать их в действии.

Полезные ссылки:

Индексация неатомарных атрибутов