Диалект Oracle SQL: Model в примерах. Часть 2

Это продолжение статьи о использовании расширения оператора SELECT — конструкции Model. Из первой части вы уже имеете представление о предназначении и некоторых особенностях её применения, а также знакомы с половиной синтаксиса. Далее будет разобрано несколько сложных примеров, а также дан анализ области применения и производительности.

Применение конструкции MODEL запрещает использовать агрегатные функции внутри других блоков SELECT (вообще, все выбираемые столбцы должны быть производными от упоминаемых в MODEL) — вместо этого следует объявлять эти функции внутри PARTITION BY, DIMENSION BY или MEASURES.Например, следующему простому запросу

SELECT employee_id, sum(amount)
FROM sales
GROUP BY employee_id;

, будет будет эквивалентен

SELECT employee_id, amt
FROM sales
GROUP BY employee_id
MODEL PARTITION BY (employee_id)
    DIMENSION BY (0 dummy)
    MEASURES (sum(amount) amt)
    RULES ();

Для контроля изменений, вносимыми правилами, существуют три семантики: UPDATE, UPSERT, UPSERT ALL. UPDATE разрешает только обновлять элементы, UPSERT (работает по умолчанию) изменять и добавлять с помощью позиционных ссылок, а UPSERT ALL разрешает создавать элементы используя символические ссылки. Изменять семантику можно как для индивидуального правила (в этом случае директива записывается перед ним), так и на уровне всего блока (тогда директиву нужно указать после RULES).

Важно знать, что UPSERT ALL работает не по аналогии с циклом FOR. Создание новых элиментов в этом случае происходит в четыре шага

1. Находятся все элементы, которые соответствуют символической ссылке.
2. По каждому измерению находится набор уникальных значений индексов.
3. Вычисляется Декартово произведение этих множеств.
4. Все не существующие элементы, присутствующие в произведении, создаются.

Сложные примеры

В завершении пара примеров, которые покажут насколько разные задачи могут решаться с помощью MODEL. Для начала выведем список выпитого кофе для каждого дня одной строкой:

SELECT day, substr(type, 2) listing
FROM coffee
MODEL RETURN UPDATED ROWS
    PARTITION BY (day)
    DIMENSION BY (row_number() OVER (PARTITION BY day ORDER BY type) position)
    MEASURES (type, cnt)
    RULES ITERATE (100500) UNTIL (presentv(type[iteration_number + 2], 1, 0) = 0) (
        type[0] = type[0] || ', ' || cnt[iteration_number + 1] || ' x ' || type[iteration_number + 1]
    )
ORDER BY day;

      DAY LISTING
---------- ----------------------------------------
         1 1 x espresso, 1 x turkish
         2 1 x black, 1 x espresso, 2 x turkish
         3 3 x latte
         4 2 x black, 1 x ice

Из нового здесь функция presentv(ref, arg1, arg2), она возвращает arg1, если ссылка ref указывает на существующий элемент с NOT NULL значением и arg2 в противном случае. Для нумерации строк и сортировки по названию используется аналитическая функция (row_number() OVER (PARTITION BY day ORDER BY type)). Обработка отдельно для каждого дня обеспечивается партиционированием (PARTITION BY) как в модели, так и в аналитической функции.

И, в завершении, геометрический пример. На прямой дан набор пересекаающихся отрезков [p1, p2], требуется вывести отсортированный список диапазонов, целиком покрытых отрезками:

SELECT * FROM lines;

        P1         P2
---------- ----------
         1          6
         5          7
         2          4
         8         20
        11         28
        30         32

Считается, что p2 >= p1, решение для произвольно хранящихся отрезков оставляется для разминки заинтересовавшимся. Итак, код примера:

SELECT p1, max(p2) p2
FROM (
    SELECT p1, p2 FROM lines
    MODEL DIMENSION BY (row_number() OVER (ORDER BY p1, p2) rn)
        MEASURES (p1, p2)
        RULES AUTOMATIC ORDER (
            p1[rn > 1] ORDER BY rn = CASE sign(p2[cv(rn) - 1] - p1[cv(rn)])
                                         WHEN 1 THEN p1[cv(rn) - 1]
                                         ELSE p1[cv(rn)]
                                      END,
            p2[rn > 1] ORDER BY rn = greatest(p2[cv(rn) - 1], p2[cv(rn)])
        )
)
GROUP BY p1
ORDER BY p1;

        P1         P2
---------- ----------
         1          7
         8         28
        30         32

Отрезки в модели сначала сортируются по левым точкам и нумеруются (row_number() OVER (ORDER BY p1, p2)), а затем просматриваются сверху вниз по номеру (директива ORDER BY rn). Обратите внимание, что в левой стороне равенства впервые используется символическая ссылка, именно поэтому необходимо указать порядок, в котором будут обрабатываться элементы. Если точка p1 просматриваемого отрезка принадлежит предыдущему отрезку, то она заменяется на p1 предыдущего отрезка. p2 заменяется на самый правые p2 среди текущего и предыдущего отрезков). Таким образом, спускаясь вниз, мы расширяем диапазон, перекрытый отрезками. Директива AUTOMATIC ORDER используется для того, чтобы p1 и p2 считались по очереди для текущего отрезка. Если убрать директиву, сначала будет выполнено первой правило для всех строк, и только затем второе. В общем случае, данная директива учитывает зависимости между элементами при обработке правил и может нарушать порядок их обхода (о чём было написано выше).

Практическое применение и производительность

Вообще, MODEL применяется невероятно достаточно редко. Происходит так потому, что 90% действительно сложных вещей решаются аналитическими функциями, а те 10%, которые остаются для MODEL традиционно выносятся с уровня БД. Несмотря на это, вот основные сценарии применения:

• необходимо сгенерировать действительно сложный отчёт (читабельность и удобство сопровождения приоритетны);
• выборка неосуществима даже с использованием аналитических функций;
• запрос динамически генерируется на высокоуровневом языке (проще сгенерировать правила в MODEL, чем аналитику);
• используются ресурсоёмкие итерационные вычисления, когда MODEL ещё хватает, а переход PL/SQL ведёт к падению производительности;
• академический интерес (декларативный подход вместо процедурного).

С производительностью всё просто. MODEL в подавляющем большинстве случаев быстрее аналогичного PL/SQL кода, но медленнее, чем использование аналитических функций. С помощью EXPLAIN PLAN можно узнать некоторые подробности об обработке запроса.

• SQL MODEL ORDERED [FAST] — означает, что обработка правил идёт в последовательности, заданной в запросе (по умолчанию);
• SQL MODEL ACYCLIC [FAST] — автоматически просчитываются зависимости ячеек;
• SQL MODEL CYCLIC — самый медленный вариант, при сложной зависимости между ячейками.

Наличие аннотации FAST означает что всё просто отлично. Так бывает при соблюдении условия, что все левые части равенств — позиционные ссылки, либо в левой части — символические ссылки, но при этом в правой — простые арифметические агрегатные функции (sum, avg и т.п.). Соответственно, MODEL FAST по быстродействию близок к использованию аналитических функций, а MODEL CYCLIC может проигрывать реализации на PL/SQL.

Литература

1. Oracle® Database Data Warehousing Guide 11g Release 2 — Chapter 22 «SQL for Modeling».
2. The SQL Model Clause of Oracle Database 10g.