New in Caché 2015.1: SQL Index on Array Property Elements
In Caché 2015.1, it became possible to include a projection for collection properties into a separate column. Thus, for SQL data access to the collection, you can use not only the child table, but simply as an additional column in which all values of the collection are located. Read more about it here .
How and where it can be useful in practice, says this article.
Sometimes it is useful (especially for the EAV model) in a class to use properties-arrays and to be able to quickly search by their elements: both the key and the value.
After filling ( do ## class ( User.eav ). RepopulateAll () ) the following data will appear in our tables:
Eav table:
Table attributes :
Global with data:
Global with indexes:
Now execute the following query:
The query fulfills, but uses a full scan, not our indexes. If you 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 happens because the SQL core “sees” only those indexes on array collections that are based solely on the fields of the subtables array and necessarily contain the key, i.e. propArray (KEY). Both of our indexes contain an entity field that is not listed 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:
In Caché 2015.1, it was possible to project a collection as a table field if this collection is projected into a sub-table using the methods SetCollectionProjection / GetCollectionProjection .
This feature is off by default.
In previous versions of these methods there is no, but you can try to enable this feature manually:
After the change it is necessary to recompile the classes.
So, turn on this parameter and see what it gave us.
In SMP, our indexes are now visible, and in the eav table there is a hidden field-collection attr. However, our query still does not see idx1 / idx2 indices.
To remedy the situation, we use the already known FOR SOME% ELEMENT predicate:
The query now uses the idx1 index. Few modify it:
In the last two examples, idx2 will be used instead of idx1.
How and where it can be useful in practice, says this article.
Sometimes it is useful (especially for the EAV model) in a class to use properties-arrays and to be able to quickly search by their elements: both the key and the value.
Consider 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 ^ Sibi ^ psk cedar ^" , "^" , $ c (769))
set obj = .. % New ()
set obj . entity = "Man"
do obj . attributes . SetAt (22, "Age" )
do obj . attributes . SetAt (186, "Growth" )
do obj . attributes . SetAt ( "Vasya" , "Name" )
do obj . % Save ()
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, 'Growth', 186 union
select 1, 'Name', 'Vasya' union
select 2, 'Age', 186 union
select 2, 'Height', 22 union
select 2, 'Family', 'Pine' union
select 2, 'Name',: 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" )
}
}
{
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 ^ Sibi ^ psk cedar ^" , "^" , $ c (769))
set obj = .. % New ()
set obj . entity = "Man"
do obj . attributes . SetAt (22, "Age" )
do obj . attributes . SetAt (186, "Growth" )
do obj . attributes . SetAt ( "Vasya" , "Name" )
do obj . % Save ()
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, 'Growth', 186 union
select 1, 'Name', 'Vasya' union
select 2, 'Age', 186 union
select 2, 'Height', 22 union
select 2, 'Family', 'Pine' union
select 2, 'Name',: 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" )
}
}
After filling ( do ## class ( User.eav ). RepopulateAll () ) the following data will appear in our tables:
Eav table:
| ID | entity |
|---|---|
| one | Person |
| 2 | Tree |
Table attributes :
| eav | ID | attr | element_key |
|---|---|---|---|
| one | 1 || Age | 22 | Age |
| one | 1 || Name | Vasya | Name |
| one | 1 || Growth | 186 | Growth |
| 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 indexes:
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 query fulfills, but uses a full scan, not our indexes. If you 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 happens because the SQL core “sees” only those indexes on array collections that are based solely on the fields of the subtables array and necessarily contain the key, i.e. propArray (KEY). Both of our indexes contain an entity field that is not listed 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:
- Index idx4 On (attributes (KEYS), attributes (ELEMENTS));
- Index idx5 On (attributes (ELEMENTS), attributes (KEYS));
So how can minimal efforts to achieve the visibility of indexes on the elements of the collection-array of the SQL-core?
In Caché 2015.1, it was possible to project a collection as a table field if this collection is projected into a sub-table using the methods SetCollectionProjection / GetCollectionProjection .
This feature is off by default.
In previous versions of these methods there is no, but you can try to enable this feature manually:
%SYS>set ^%SYS("sql","sys","collection projection")=1 After the change it is necessary to recompile the classes.
So, turn on this parameter and see what it gave us.
In SMP, our indexes are now visible, and in the eav table there is a hidden field-collection attr. However, our query still does not see idx1 / idx2 indices.
To remedy the situation, we use the already known FOR SOME% ELEMENT predicate:
select entity from eav where for some %element(attr) (%value = 22) entity The query now uses the idx1 index. Few modify it:
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 In the last two examples, idx2 will be used instead of idx1.
Source: https://habr.com/ru/post/255203/
All Articles