Новое в Caché 2015.1: SQL-индекс по элементам свойства-массива

В Caché 2015.1 появилась возможность включить для свойств коллекций проекцию в отдельную колонку. Таким образом для SQL доступа к данным коллекции можно использовать не только дочернюю таблицу, но и просто как дополнительную колонку, в которой лежат все значения коллекции. Подробнее об этом здесь ^[1].
О том, как и где это может быть полезно на практике, рассказывает эта статья.

Иногда бывает полезным (особенно для модели EAV) в классе использовать свойства-массивы и иметь возможность быстрого поиска по их элементам: как ключу, так и значению.

Рассмотрим простой пример

Class User.eav Extends %Persistent
{

Index idx1 On attributes(ELEMENTS) [ Data = entity ];

Index idx2 On (attributes(KEYS), attributes(ELEMENTS)) [ Data = entity ];

Property entity;

Property attributes As array Of %String(SQLTABLENAME = "attributes") [ SqlFieldName = attr ];

/// do ##class(User.eav).RepopulateAll()
ClassMethod RepopulateAll()
{
  do ..%DeleteExtent()

  set name=$TR("Сосна^ сиби^рская кедро^вая","^",$c(769))

  set obj=..%New()
  set obj.entity="Человек"
  do obj.attributes.SetAt(22,"Возраст")
  do obj.attributes.SetAt(186,"Рост")
  do obj.attributes.SetAt("Вася","Имя")
  do obj.%Save()

  set obj=..%New()
  set obj.entity="Дерево"
  do obj.attributes.SetAt(186,"Возраст")
  do obj.attributes.SetAt(22,"Высота")
  do obj.attributes.SetAt("Сосновые","Семейство")
  do obj.attributes.SetAt(name,"Имя")
  do obj.%Save()

  /*

  ; или

  &sql(insert into eav(entity) select 'Человек' union select 'Дерево')
  &sql(insert into attributes(eav,element_key,attr)
  select 1,'Возраст',22 union
  select 1,'Рост',186 union
  select 1,'Имя','Вася' union
  select 2,'Возраст',186 union
  select 2,'Высота',22 union
  select 2,'Семейство','Сосновые' union
  select 2,'Имя',:name)
  */

  do ..Reindex()
}

/// do ##class(User.eav).Reindex()
ClassMethod Reindex()
{
do ..%BuildIndices(,1)

  do $system.SQL.TuneTable("SQLUser.eav",1)
  do $system.SQL.TuneTable("SQLUser.attributes",1)
  do $system.OBJ.Compile($classname(),"cu")
}

}

После заполнения (do ##class(User.eav).RepopulateAll()) в наших таблицах окажутся следующие данные:

Таблица eav:

ID	entity
1	Человек
2	Дерево

Таблица attributes:

eav	ID	attr	element_key
1	1\|\|Возраст	22	Возраст
1	1\|\|Имя	Вася	Имя
1	1\|\|Рост	186	Рост
2	2\|\|Возраст	186	Возраст
2	2\|\|Высота	22	Высота
2	2\|\|Имя	Сосна́ сиби́рская кедро́вая	Имя
2	2\|\|Семейство	Сосновые	Семейство

Глобал с данными:

USER>zw ^User.eavI
^User.eavI("idx1"," 186",1)=$lb("","Человек")
^User.eavI("idx1"," 186",2)=$lb("","Дерево")
^User.eavI("idx1"," 22",1)=$lb("","Человек")
^User.eavI("idx1"," 22",2)=$lb("","Дерево")
^User.eavI("idx1"," ВАСЯ",1)=$lb("","Человек")
^User.eavI("idx1"," СОСНА́ СИБИ́РСКАЯ КЕДРО́ВАЯ",2)=$lb("","Дерево")
^User.eavI("idx1"," СОСНОВЫЕ",2)=$lb("","Дерево")
^User.eavI("idx2","Возраст"," 186",2)=$lb("","Дерево")
^User.eavI("idx2","Возраст"," 22",1)=$lb("","Человек")
^User.eavI("idx2","Высота"," 22",2)=$lb("","Дерево")
^User.eavI("idx2","Имя"," ВАСЯ",1)=$lb("","Человек")
^User.eavI("idx2","Имя"," СОСНА́ СИБИ́РСКАЯ КЕДРО́ВАЯ",2)=$lb("","Дерево")
^User.eavI("idx2","Рост"," 186",1)=$lb("","Человек")
^User.eavI("idx2","Семейство"," СОСНОВЫЕ",2)=$lb("","Дерево")

Глобал с индексами:

USER>zw ^User.eavI
^User.eavI("idx1"," 186",1)=$lb("","Человек")
^User.eavI("idx1"," 186",2)=$lb("","Дерево")
^User.eavI("idx1"," 22",1)=$lb("","Человек")
^User.eavI("idx1"," 22",2)=$lb("","Дерево")
^User.eavI("idx1"," ВАСЯ",1)=$lb("","Человек")
^User.eavI("idx1"," СОСНА́ СИБИ́РСКАЯ КЕДРО́ВАЯ",2)=$lb("","Дерево")
^User.eavI("idx1"," СОСНОВЫЕ",2)=$lb("","Дерево")
^User.eavI("idx2","Возраст"," 186",2)=$lb("","Дерево")
^User.eavI("idx2","Возраст"," 22",1)=$lb("","Человек")
^User.eavI("idx2","Высота"," 22",2)=$lb("","Дерево")
^User.eavI("idx2","Имя"," ВАСЯ",1)=$lb("","Человек")
^User.eavI("idx2","Имя"," СОСНА́ СИБИ́РСКАЯ КЕДРО́ВАЯ",2)=$lb("","Дерево")
^User.eavI("idx2","Рост"," 186",1)=$lb("","Человек")
^User.eavI("idx2","Семейство"," СОСНОВЫЕ",2)=$lb("","Дерево")

Теперь выполним следующий запрос:

select entity from eav where attributes->attr = 22

entity
Человек
Дерево

Запрос отрабатывает, но использует полное сканирование, а не наши индексы. Если посмотреть в SMP (Портал управления системой) на наши таблицы, то мы в них не найдём idx1 и idx2, хотя как мы ранее видели, данные в них сгенерированы.

Это происходит потому, что SQL-ядро «видит» только те индексы по коллекциям-массивам, которые базируются исключительно на полях подтаблицы-массива и обязательно содержат ключ, т.е. propArray(KEY). Оба наших индекса содержат поле entity, которое отсутствует в подтаблице attributes.

Также не будет виден индекс Index idx3 On attributes(ELEMENTS); поскольку он не содержит attributes(KEYS), а вот индексы:

Index idx4 On (attributes(KEYS), attributes(ELEMENTS));
Index idx5 On (attributes(ELEMENTS), attributes(KEYS));

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

Так как же минимальными усилиями добиться видимости индексов на элементы коллекции-массива SQL-ядром?

В версии Caché 2015.1 появилась возможность проецировать коллекцию как поле таблицы, если эта коллекция проецируется в подтаблицу, используя методы SetCollectionProjection/GetCollectionProjection ^[1].
По умолчанию эта возможность выключена.

В предыдущих версиях данных методов нет, но Вы можете попробовать включить эту фичу вручную:

%SYS>set ^%SYS("sql","sys","collection projection")=1

После изменения обязательно необходимо перекомпилировать классы.

Итак, включим этот параметр и посмотрим, что это нам дало.

В SMP теперь стали видны наши индексы, а в таблице eav появилось скрытое поле-коллекция attr. Тем не менее наш запрос по прежнему не видит индексы idx1/idx2.

Для исправления ситуации воспользуемся уже известным предикатом FOR SOME %ELEMENT ^[2]:

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

entity
Человек
Дерево

Теперь в запросе задействован индекс idx1. Немного его видоизменим:

select entity from eav where for some %element(attr) (%value = 22 and %key= 'Возраст')

entity
Человек

select entity from eav where for some %element(attr) (%value = 22 and %key= 'Высота')

entity
Дерево

В последних двух примерах уже будет задействован индекс idx2 вместо idx1.

Автор: intersystems

Источник ^[3]

Сайт-источник PVSM.RU: https://www.pvsm.ru

Путь до страницы источника: https://www.pvsm.ru/programmirovanie/88335

Ссылки в тексте:

[1] здесь: http://docs.intersystems.com/cache20151/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_queries#GSQL_queries_collection

[2] FOR SOME %ELEMENT: http://docs.intersystems.com/cache20151/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_forsomeelement

[3] Источник: http://habrahabr.ru/post/255203/

Нажмите здесь для печати.