New in Caché 2015.1: SQL Index on Array Property Elements
Caché 2015.1 introduced the ability to include projection in a separate column for collection properties. Thus, for SQL access to collection data, you can use not only a child table, but simply as an additional column in which all the values of the collection are located. Read more about it here .
This article tells how and where it can be useful in practice.
Sometimes it is useful (especially for the EAV model) in a class to use array properties and have the ability to quickly search by their elements: both key and value.
After filling (do ## class (User.eav) .RepopulateAll ()) in our tables will be the following data:
Table eav :
Attributes table :
Global with data:
Global with indices:
Now execute the following query:
The request fulfills, but uses a full scan, not our indexes. If we look at our tables in the SMP (System Management Portal), then we will not find idx1 and idx2 in them, although as we saw earlier, the data in them is generated.
This is because the SQL kernel "sees" only those indexes on array collections that are based solely on the fields of the array subtable and must contain the key, i.e. propArray (KEY). Both of our indexes contain an entity field, which is not in the attributes sub-table.
Also, the index idx3 On attributes (ELEMENTS) index will not be visible; since it does not contain attributes (KEYS), but the indices are:
Caché 2015.1 introduced the ability to project a collection as a table field if the collection is projected into a subtable using the SetCollectionProjection / GetCollectionProjection methods .
By default, this feature is disabled.
There are no methods in previous versions of this method, but you can try to enable this feature manually:
After the change, you must necessarily recompile the classes.
So, turn on this option and see what it gave us.
In SMP, our indexes are now visible, and a hidden attr collection field has appeared in the eav table. However, our query still does not see idx1 / idx2 indexes.
To remedy the situation, we will use the well-known predicate FOR SOME% ELEMENT :
Now the idx1 index is involved in the request. We modify it a little:
The last two examples will already use the idx2 index instead of idx1.
This article tells how and where it can be useful in practice.
Sometimes it is useful (especially for the EAV model) in a class to use array properties and have the ability to quickly search by their elements: both key and value.
Let's look at a simple example.
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 ("Pine ^ Siberian cedar ^," ^ ", $ c (769))
set obj = ..% New ()
set obj.entity = "Man"
do obj.attributes.SetAt (22, "Age")
do obj.attributes.SetAt (186, "Height")
do obj .attributes.SetAt ("Vasya", "Name"
set obj = ..% New ()
set obj.entity = "Tree"
do obj.attributes.SetAt (186, "Age")
do obj.attributes.SetAt (22, "Height")
do obj.attributes.SetAt ( "Pine", "Family")
do obj.attributes.SetAt (name, "Name")
do obj.% Save ()
/ *
; or
& sql (insert into eav (entity) select 'Person' union select 'Tree')
& sql (insert into attributes (eav, element_key, attr)
select 1, 'Age', 22 union
select 1, 'Height', 186 union
select 1, 'Name', 'Vasya' union
select 2, 'Age', 186 union
select 2, 'Height', 22 union
select 2, '
}
/// 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")
}
}
{
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 ("Pine ^ Siberian cedar ^," ^ ", $ c (769))
set obj = ..% New ()
set obj.entity = "Man"
do obj.attributes.SetAt (22, "Age")
do obj.attributes.SetAt (186, "Height")
do obj .attributes.SetAt ("Vasya", "Name"
set obj = ..% New ()
set obj.entity = "Tree"
do obj.attributes.SetAt (186, "Age")
do obj.attributes.SetAt (22, "Height")
do obj.attributes.SetAt ( "Pine", "Family")
do obj.attributes.SetAt (name, "Name")
do obj.% Save ()
/ *
; or
& sql (insert into eav (entity) select 'Person' union select 'Tree')
& sql (insert into attributes (eav, element_key, attr)
select 1, 'Age', 22 union
select 1, 'Height', 186 union
select 1, 'Name', 'Vasya' union
select 2, 'Age', 186 union
select 2, 'Height', 22 union
select 2, '
}
/// 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")
}
}
After filling (do ## class (User.eav) .RepopulateAll ()) in our tables will be the following data:
Table eav :
| ID | entity |
|---|---|
| 1 | Human |
| 2 | Tree |
Attributes table :
| eav | ID | attr | element_key |
|---|---|---|---|
| 1 | 1 || Age | 22 | Age |
| 1 | 1 || Name | Vasya | Name |
| 1 | 1 || Growth | 186 | Height |
| 2 | 2 || Age | 186 | Age |
| 2 | 2 || Height | 22 | Height |
| 2 | 2 || Name | Pine Siberian cedar | Name |
| 2 | 2 || Family | Pine | Family |
Global with data:
USER>zw ^User.eavD
^User.eavD=2
^User.eavD(1)=$lb("","Человек")
^User.eavD(1,"attributes","Возраст")=22
^User.eavD(1,"attributes","Имя")="Вася"
^User.eavD(1,"attributes","Рост")=186
^User.eavD(2)=$lb("","Дерево")
^User.eavD(2,"attributes","Возраст")=186
^User.eavD(2,"attributes","Высота")=22
^User.eavD(2,"attributes","Имя")="Сосна́ сиби́рская кедро́вая"
^User.eavD(2,"attributes","Семейство")="Сосновые"
Global with indices:
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("","Дерево")
Now execute the following query:
select entity from eav where attributes->attr = 22
entity
Человек
Дерево
The request fulfills, but uses a full scan, not our indexes. If we look at our tables in the SMP (System Management Portal), then we will not find idx1 and idx2 in them, although as we saw earlier, the data in them is generated.
This is because the SQL kernel "sees" only those indexes on array collections that are based solely on the fields of the array subtable and must contain the key, i.e. propArray (KEY). Both of our indexes contain an entity field, which is not in the attributes sub-table.
Also, the index idx3 On attributes (ELEMENTS) index will not be visible; since it does not contain attributes (KEYS), but the indices are:
- Index idx4 On (attributes (KEYS), attributes (ELEMENTS));
- Index idx5 On (attributes (ELEMENTS), attributes (KEYS));
So how, with minimal efforts, to achieve the visibility of indexes on elements of a collection-array by the SQL kernel?
Caché 2015.1 introduced the ability to project a collection as a table field if the collection is projected into a subtable using the SetCollectionProjection / GetCollectionProjection methods .
By default, this feature is disabled.
There are no methods in previous versions of this method, but you can try to enable this feature manually:
%SYS>set ^%SYS("sql","sys","collection projection")=1
After the change, you must necessarily recompile the classes.
So, turn on this option and see what it gave us.
In SMP, our indexes are now visible, and a hidden attr collection field has appeared in the eav table. However, our query still does not see idx1 / idx2 indexes.
To remedy the situation, we will use the well-known predicate FOR SOME% ELEMENT :
select entity from eav where for some %element(attr) (%value = 22)
entity
Человек
Дерево
Now the idx1 index is involved in the request. We modify it a little:
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
Дерево
The last two examples will already use the idx2 index instead of idx1.