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

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


    Иногда бывает полезным (особенно для модели 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.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","Семейство")="Сосновые"
    


    Глобал с индексами:
    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.
    По умолчанию эта возможность выключена.

    В предыдущих версиях данных методов нет, но Вы можете попробовать включить эту фичу вручную:
    %SYS>set ^%SYS("sql","sys","collection projection")=1
    

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

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

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

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

    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
    87,00
    Вендор: СУБД Caché, OLAP DeepSee, шина Ensemble
    Поделиться публикацией

    Похожие публикации

    Комментарии 0

    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

    Самое читаемое