Табличной функцией называется функция, которая может быть использована в запросе в качестве источника данных. Например, вы можете поместить табличную функцию в предложение FROM оператора SELECT в программе на PL/SQL. Еще более важно, что табличная функция может возвращать записи. (Фактически она возвращает коллекцию объектов.) Уже эти две возможности делают табличные функции очень полезными в ситуациях, требующих сокрытия сложностей обработки за одним оператором SELECT, например в отчетах и компонентах API. Добавьте сюда возможности конвейерной и параллельной обработки, и вы получите мощнейший инструмент для работы с ETL-процессами (Extraction, Transformation, Loading - извлечение, преобразование и загрузка данных) в хранилищах данных.
Понятно, что табличные функции полезны для тех, кто разрабатывает отчеты и имеет дело с хранилищами данных, но у вас может возникнуть вопрос, зачем они нужны администраторам баз данных. В двух словах ответ таков: вам надо знать о них потому, что остальные сотрудники вашей организации могут быть не в курсе. Многие разработчики даже не слышали о существовании табличных функций, не говоря уже о том, чтобы использовать их для повышения производительности приложений - вот тут-то и нужен администратор базы данных. Представьте себе отчет, в котором запрос вслед за изменяющимися требованиями стал настолько сложным, что производительность стала неприемлемо низкой. Разработчик может перепробовать все комбинации подзапросов и внешних соединений - и все безрезультатно. Очевидно, что обработка стала намного сложнее того, что можно ожидать от одного оператора SELECT, но интерфейс отчета требует, чтобы она была сосредоточена в единственном операторе. В таких ситуациях администраторы баз данных часто бросаются в бой и стремятся помочь разработчику, строя планы выполнения и различным образом секционируя таблицы в надежде достичь ускорения. Ничего не получается, вы в тупике и мечтаете о возможности сымитировать механизмы запроса Oracle, чтобы самостоятельно сформировать результирующее множество. Табличные функции позволяют это сделать!
В этой главе рассказывается, как работают табличные функции, и каким образом вы можете воспользоваться их преимуществами в рабочей среде. В ней также описываются механизм взаимодействия табличных функций с курсорами и способы конвейеризации, вложения и распараллеливания этих функций для достижения еще большей производительности. В завершение обсуждается использование табличных функций в ряде реальных приложений.
Начнем с простого примера, показывающего, как выглядят табличные функции и что они могут делать.
Выше уже высказывалась мысль о том, чтобы обращаться к табличной функции с помощью оператора SELECT. Приведем пример.
На первый взгляд это выглядит как обычный запрос, но посмотрите внимательно: company_balance_sheet - это функция. Представим себе, что эта функция может анализировать миллионы пространных бухгалтерских записей о потенциальных приобретениях на предмет того, как они повлияют на итоговую прибыльность родительской компании. Огромный объем данных и строгие правила бухгалтерского учета требуют использования отдельной программы, но что делать, если результат должен быть доступен при помощи простого запроса, выданного с веб-страницы? На помощь приходят табличные функции.
Вот пример использования табличной функции в программе на PL/SQL. Заметьте, здесь она используется, как и любой другой курсор. Однако эта функция может просматривать множество детальных записей о транзакциях, вычисляя в реальном времени итоги по регионам, что дает возможность менеджерам принимать решения, связанные с планированием объемов продаж.
Следующий пример показывает, что табличная функция способна принимать параметры, которые могут использоваться для управления процессом обработки данных. Здесь функция просматривает многочисленные результаты сложных анализов в поисках аномалий, прежде чем представить результаты, которые должны быть достаточно точными для оформления в реальном времени заявок на гранты, направляемые на финансирование важных исследований в области борьбы с раком:
В чуть менее грандиозных (но не менее важных для вашего собственного бизнеса) масштабах вы можете использовать табличную функцию в запросе, использующем сложную логику для поиска в течение трех секунд всех заявок на внеплановую работу, чтобы в вызывающем приложении не произошел тайм-аут. Или вы можете использовать табличную функцию с целью исключить появление простого экрана с запросом в среде .NET - того, на котором выводятся данные о наличии запасных частей для неисправного водопровода, - так как сведения нельзя считать надежными в силу того, что расчет по формулам для их поиска выполнялся слишком долго.
В области преобразования данных табличные функции позволяют передавать результат промежуточного преобразования последовательно от одной функции к другой. Часто большие задачи по преобразованию данных вынуждены ждать сами себя из-за того, что последующие преобразования не могут начаться до окончания формирования полного результирующего множества начального преобразования - хотя логика программы позволяет им начать работу, как только станет доступна первая запись. В такой ситуации вы можете применить табличные функции в режиме, называемом конвейеризацией, также вы можете использовать распараллеливание табличных функций для достижения еще большего эффекта.
Вы еще не уверены, сделают ли табличные функции проще жизнь администратора базы данных? Тогда обратите внимание на тот факт, что табличные функции позволяют встраивать бизнес-логику в запросы, гарантируя тем самым, что эти запросы выполнят в точности те действия, для которых они предназначены. Например, вместо того, чтобы беспокоиться о размере сегмента отката для часто выполняющихся длинных запросов, вы, зная, что нужные данные обновляются с периодичностью раз в час, можете закэшировать их в памяти. Кроме того, в табличных функциях вы можете использовать такие механизмы повышения производительности, как массовая выборка и ассоциативные массивы (ранее называвшиеся индекс-таблицами), уменьшая общую нагрузку на базу данных.
Рассмотрению этих и других примеров посвящена данная глава. Для начала обратимся к основам.
Большинству администраторов БД известно, что Oracle позволяет вызывать функции в запросах, как в следующем примере:
Oracle разрешает запросы такого типа, так как структура возвращаемого множества определена: в единственной записи будет возвращен единственный столбец типа DATE.
Большинству администраторов баз данных также известно, что структура результирующего множества должна быть определена для любого входящего в оператор SELECT объекта (т. е. таблицы, представления и др.). В противном случае база данных не сможет определить формат возвращаемых данных. Тем не менее непривычно, что функции, исторически возвращавшие единственное скалярное значение, могут возвращать результирующие множества, состоящие из нескольких записей и нескольких столбцов, например такие:
| < Предыдущая | Следующая > |
|---|
