В 1913 году Генри Форд запустил первый сборочный конвейер по сборке генераторов. Сборка была разложена на 85 независимых друг от друга операций, при этом выполнялись они условно параллельно. Как итог: сокращение сборки генератора с 20 до 5 минут.
Принцип "готовый промежуточный результат передается дальше по конвейеру" был применен в специальных функциях Oracle.
Конвейерная функция или же pipelined-функция — это табличная функция, которая поставляет свои результаты в вызывающую среду по мере их подготовки, не материализуя весь результирующий набор. Тем самым, как плюсы можно выделить:
-Ускорение SQL-запросов и PL/SQL-кода (в некоторых случаях)
-Уменьшение затрат ресурсов PGA при материализации результата
Синтаксис
От обычной функции отличается ключевой конструкцией Pipelined, обязательно должна возвращаться коллекция, её заполнение происходит по мере выполнения через pipe row, в котором указывается элемент коллекции.
Важный момент: результаты должны отдаваться итеративно в цикле. Это может быть, например, проход по курсору (так обычно бывает при обработке входных данных), либо просто цикл.
Как только программа достигает участка с pipe row результат передается в вызывающую среду, при этом вызывающая сторона уже может выполнять свои действия с этой строкой. Проведем аналогию с конвейером Генри Форда. Пока готовится новая деталь, предыдущая деталь уже отдана по конвейеру в вызывающую среду и уже принимает участие в последующей сборке.
Перейдём к практике. Посмотрим, как создаются конвейерные функции и как получить из них результат. Создаём объект из 2 полей, он будет элементом коллекции, саму коллекцию t_person и конвейерную функцию get_persons. Заметим, что в качестве результата функции указана коллекция t_persons, а в инструкции создается элемент коллекции t_person, который сразу же отдается в вызывающую среду. Используются эти функции точно так же, как обычные табличные функции oracle (до 12 версии с указанием ключевого слова table, после 12 - включительно).
Далее создадим коллекцию чисел t_numbers, через нее мы будем возвращать результат. И 2 функции: обычную delay_simple и конвейерную delay_pipelined. Они обе генерируют n строчек в обычном цикле. На каждой строчке функции спят указанное время (по умолчанию 1 сек.) для этого используется пакет dbms_session (dbms_lock для oracle 11). Теперь рассмотрим отличия. В обычной функции результат сначала добавляется в коллекцию, в конце коллекции возвращается как результат выполнения. В конвейерной функции результат возвращается через команду pipe row, сразу как только оно было получено. Это легко проверить если вызвать её в sql запросе для получения строки с результатом.
На 2 примере продемонстрирую основное свойство возврата результата по мере его подготовки.
Создаём вспомогательную функцию get_actual_dtime. Функционал простой: в автономной транзакции получается текущее время на момент вызова и возвращается в вызывающую среду. Почему же нельзя использовать sysdate? Потому что sysdate вычисляется в момент старта запроса. А нам нужно актуальное время на момент получения строчки, а это можно сделать только через автономную транзакцию.
Выполним запрос для простой функции:
Функция delay_simple формирует коллекцию и за один раз возвращает её.
Выполним запрос для конвейерной функции:
Время возврата результата для каждой строчки разное и различается на 1 секунду.
Посмотрим, как это можно использовать в sql.
Пример 3.1
Пример 3.1 Простой вызов функции без фильтрации.
Простая функция:
Конвейерная функция:
Разница не очень очевидна.
Пример 3.2 Вызов функции с ограничением количества строк.
Обычная функция:
Конвейерная функция:
Теперь разница более очевидна. Конвейерной функции нет необходимости выдавать все 5 строчек, если мы уже получили результат.
Пример 3.3 Вызов функции с фильтрацией результата с более сложным условием.
Простая функция:
Конвейерная функция:
В этом кейсе конвейерная функция вела себя как обычная. Все просто: чтобы отфильтровать по условию in (1,3) нужно получить весь результат работы функции. Т.к мы не знаем какие элементы в коллекции будут до того как будет получен результат.
Теперь посмотрим пример с PGA при генерации больших коллекций. Для замера использования памяти создадим функцию get_session_pga. Выполним 2 теста для 2 процедур. Входные параметры: миллион элементов коллекции и 0 сек. задержки.
Код:
Результат:
Это небольшие цифры, но когда мы будем работать с объектами, которые будут содержать большие текстовые поля, то разница будет большой. С преимуществом Pipelined-функции.