Компилятор PL/SQL достаточно интеллектуален, чтобы определить тип результирующего множества для таблицы или представления. Он позволяет нам не беспокоиться о типах данных благодаря использованию атрибутов %TYPE и %ROWTYPE, как показано ниже.
Однако PL/SQL столкнется с трудностями, расшифровывая структуру возвращаемых табличной функцией данных, так как у него нет основы, на которую он мог бы опереться. Вы должны дать ему эту точку опоры явно, сославшись на объекты или коллекции Oracle. Сказанное проиллюстрировано в следующем примере, где объявлен объект, а затем коллекция таких объектов.
Именно свойство коллекции иметь в себе много записей позволяет использовать ее в качестве результирующего множества функции.
Все, что делает эта функция, - собирает три записи в коллекцию и возвращает их. Теперь функция может быть вызвана из оператора SELECT при помощи ключевого слова TABLE, сообщающего Oracle, что возвращаемую коллекцию следует интерпретировать как набор записей.
К возвращаемому табличной функцией PL/SQL результирующему множеству может быть приложена вся мощь Oracle SQL, как если бы запрос выполнялся к таблице или представлению.
Табличная функция может выполнять любые действия, доступные обычной функции, включая запросы и логические выражения.
Тема табличных функций довольно обширна, поэтому в этой главе мы не сможем обсудить в подробностях все возможные варианты их применения. Однако все упоминаемые здесь приложения подчиняются одному общему требованию: в них используется временное хранение данных в оперативной памяти (в коллекции). Рассмотрим, например, уже упоминавшееся приложение, применяющееся в исследовании рака. Каждая экспериментально полученная запись может рассматриваться на предмет включения в окончательный результат только после проверки на полноту; также необходимо проверить на полноту каждый отдельный эксперимент, прежде чем переходить к следующим шагам обработки. Если надо проверить 1000 экспериментов, это займет более получаса (2000 секунд), и только затем станет возможной дальнейшая обработка. Это приложение стало бы значительно эффективнее, если бы данные каждого эксперимента передавались для последующей обработки по мере прохождения проверки. Табличные функции, как показано в следующем разделе, предоставляют такую возможность.
К этому моменту у вас, наверное, сложилось впечатление, что табличные функции - это универсальное средство повышения производительности, позволяющее решить любую проблему. Однако рассматриваемые далее возможности: применение курсоров, конвейеризация и вложение табличных функций - настолько эффективны, что могут побудить вас даже переписать код, лишь бы их использовать.
Вездесущий курсор появляется в табличной функции в двух ролях: как тип данных параметра и как SQL-функция, позволяя передавать оператор SELECT для выполнения непосредственно в табличную функцию.
Конвейеризация
Данная особенность позволяет табличной функции возвращать строки результирующего множества по одной, не накапливая его целиком. Благодаря этому последующая обработка может начаться гораздо раньше. Возвращаясь к уже упоминавшемуся примеру с раковыми исследованиями, посмотрим, что произойдет, если функции надо выполнить 100 проверок, каждая из которых занимает 3 секунды. Это означает, что следующий этап обработки сможет начаться только через 5 минут. При конвейерной обработке он начнется через 3 секунды.
Для выполнения нескольких операций над данными можно использовать вложение табличных функций. Этот способ особенно полезен при работе с ETL-процессами хранилищ данных.
Вероятно, лучший способ продемонстрировать перечисленные возможности - это привести пример ETL-процесса в хранилище данных, извлекающего информацию о нарядах на работу. Рассмотрим функцию, извлекающую сведения о дате создания, выдачи и закрытия нарядов. Затем эти компоненты передаются для дальнейшей обработки процессу ETL.
Мы уже рассматривали подробно курсоры в главе 2, и вот опять они здесь! Как взаимодействуют курсоры и табличные функции? Для начала рассмотрим табличную функцию без конвейеризации.
Вот результат запроса для трех нарядов.
ORDER_NUMBER
|
D
|
YEAR
|
QUARTER
|
MONTH
|
1
|
O
|
2005
|
3
|
8
|
1
|
A
|
2005
|
3
|
8
|
1
|
C
|
2005
|
3
|
8
|
2
|
O
|
2005
|
4
|
10
|
2
|
A
|
2005
|
4
|
10
|
2
|
C
|
2005
|
4
|
10
|
3
|
O
|
2005
|
4
|
12
|
3
|
A
|
2005
|
4
|
12
|
3
|
C
|
2005
|
4
|
12
|
Далее, если надо ограничить выборку определенными регионами, добавим следующее ограничение в оператор SELECT:
Заметьте, я сказал, что в запросе было три наряда, но предусмотрительно не сообщил, откуда выполнялась выборка. Дело в том, что выборка осуществляется из переданного функции курсора, имеющего такой вид:
Ключевое слово TABLE означает, что выполняется вызов функции date_parse. Ключевое слово CURSOR означает, что следующий за ним текст (в скобках) должен использоваться как курсор в табличной функции. Он неявно открывается, и функция извлекает из него записи. Когда табличная функция выходит из области видимости, он неявно закрывается. Способность передавать в функцию текст курсора - очень мощный инструмент, так как требует всего лишь корректного SQL. Функция может быть вызвана (и, следовательно, повторно использована) в разных местах. Например, если надо обработать только сегодняшние записи, то вызов должен быть таким:
Единственное требование к этому оператору SELECT заключается в том, что должны выбираться все столбцы таблицы ORDERS, так как в табличной функции переменная типа запись, в которую помещается результат, объявлена как orders%ROWTYPE. Однако данное требование не всегда обязательно, есть много способов установить соответствие между операторами SELECT, типами параметров-курсоров и локальными переменными. Мы рассмотрим их далее в этой главе.
Конвейеризованная табличная функция - это функция, которая возвращает результирующее множество в виде коллекции, но делает это итеративно. Другими словами, Oracle не ждет, когда выполнение функции закончится, накапливая все полученные строки в коллекции PL/ SQL, прежде чем вернуть их. Вместо этого записи по мере их готовности к включению в коллекцию «выкачиваются» из функции. Давайте посмотрим на конвейеризованную табличную функцию в действии.
Ключевое слово PIPELINED добавляется в заголовок функции с целью сообщить Oracle о необходимости возвращать результат немедленно, а не накапливать предварительно все результирующее множество.
Одинокое ключевое слово RETURN осталось, но не делает ничего, осуществляя лишь выход из функции. Все результаты уже были переданы по конвейеру командой PIPE ROW.
Возвращаемый тип данных (order_date_o) отличается от типа, указанного в объявлении функции (order_date_t). Несмотря на такое синтаксическое несоответствие, эти типы должны быть связаны между собой, как объясняется в следующем абзаце.
Oracle не позволит вернуть из произвольной табличной функции любой из обычных типов данных. Указанная в качестве возвращаемого типа функции коллекция должна иметь в качестве элемента объектный тип. В нашем примере возвращаемые типы данных были объявлены так:
Чтобы показать, как много дает конвейеризация в такой ситуации, увеличим количество нарядов до 10 000 и выполним следующий запрос с использованием обеих версий (с конвейеризацией и без) нашей функции.
Такой запрос отлично подходит для нашей цели, так как точно показывает, сколько времени необходимо функции для того, чтобы вернуть десятую по счету запись. Победителем становится...
Второе место с достойным результатом в 2,73 секунды занимает версия без конвейеризации.
Первое место с невероятным результатом в 0,07 секунды достается конвейеризованной версии.
В конвейеризованном варианте запрос выполняется быстрее на 2,66 секунды или на 93%. Более существенным с точки зрения администратора БД является то, что база данных тратит при выполнении запроса на 93% меньше времени на поддержание согласованной по чтению копии таблицы ORDERS, а это на 93% уменьшает вероятность возникновения избыточных операций физического чтения, вызванных продолжительностью запроса. Можно продолжать еще и еще.
Еще более приятно для администратора то, что при выполнении конвейеризованной версии расходуется меньше оперативной памяти, выделяемой Oracle сеансу. Здесь, для простоты, мы ограничим концепцию памяти сеанса понятиями областей UGA (User Global Area - глобальная область пользователей) и PGA (Program Global Area - программная глобальная область). В табл. 3.1 приведено сравнение объемов UGA и PGA, расходуемых при выполнении обеих версий функции. Эти данные получены после входа в систему и однократного выполнения конвейеризованной и неконвейеризованной функций.
Таблица 3.1. Затраты памяти сеанса Oracle на выполнение
конвейеризованной и неконвейеризованной функций
Неконвейеризованная
|
Конвейеризованная
|
Разность
|
|
Максимально в UGA
|
7105168
|
90284
|
7014884
|
Максимально в PGA
|
12815736
|
242708
|
12573028
|
В обеих областях (UGA и PGA) сокращение составляет 98% - это сокращает нагрузку на базу данных и позволяет ей работать быстрее.
| < Предыдущая | Следующая > |
|---|
