С помощью табличных функций, кроме всего прочего, можно, используя знания о приложении, помочь выполнению запросов. Классический пример запроса с несколькими условиями OR EXISTS показан в следующем примере.
Запрос вернет запись, если соответствующая запись будет найдена в любой из трех других таблиц. Полученный в режиме AUTOTRACE план выполнения показывает, что оптимизатор Oracle использует просмотр всех использованных в запросе таблиц для получения единственной результирующей записи.
Секция статистики показывает объем работы, проделанной Oracle для получения результирующего множества, включая 13 операций согласованного чтения.
Но как быть, если нам известно, что для таблицы OR_TABLE_ONE условие выполняется с вероятностью 90%, а для остальных таблиц вероятность составляет 10%? Надо выполнить поиск в таблице OR_TABLE_ONE и, только если там запись не найдена, приступать к поиску в других таблицах. Одно из решений заключается в использовании в табличной функции вложенных курсоров, с тем чтобы вся операция по-прежнему выполнялась в виде запроса.
Statistics
|
|
13
|
recursive calls
|
0
|
db block gets
|
3
|
consistent gets
|
0
|
physical reads
|
0
|
redo size
|
397
|
bytes sent via SQL*Net to client
|
511
|
bytes received via SQL*Net from client
|
2
|
SQL*Net roundtrips to/from client
|
0
|
sorts (memory)
|
0
|
sorts (disk)
|
1
|
rows processed
|
Рассчитанная оптимизатором стоимость выполнения такой функции намного выше, чем у запроса, - в 25 раз. Но базе данных приходится выполнять гораздо меньше работы, всего 3 операции согласованного чтения вместо 13.
Небольшое предостережение: используйте такой способ, только если вы обнаружили узкое место и только после тщательного тестирования. Выигрыш в производительности может не стоить написания, отладки и сопровождения дополнительного кода.
В завершение дадим несколько советов, которые помогут вам полнее использовать преимущества табличных функций.
Функция Oracle SYS_REFCURSOR позволяет быстро объявить слабо типизированный параметр REF CURSOR, которому можно сопоставить практически любой курсор. Используя SYS_REFCURSOR для объявления типа данных параметра, в функцию можно передавать любой оператор SELECT при условии, что над курсором в теле функции не выполняется никаких действий. Например, эта функция может принять любой оператор SELECT.
Ограничения появляются, когда внутри функции, что весьма вероятно, надо извлекать записи. Для сохранения записей потребуются локальные переменные.
Теперь в функцию могут быть переданы только те операторы SELECT, которые выбирают все столбцы таблицы ORDERS. Любые другие вызовут ошибку ORA-01007.
Таким образом, в функцию можно передать любой оператор SELECT, но если в нем запрашивается таблица, отличная от ORDERS, функция завершится с ошибкой. Гибкость SYS_REFCURSOR и слабо типизированных курсоров REF CURSOR в общем случае оказывается сомнительной.
и в дальнейшем использовать их в табличных функциях.
В силу сказанного, мы рекомендуем полностью избавиться от иллюзии гибкости и использовать курсоры REF CURSOR со строгим контролем типа, объявленные в централизованном пакете, как показано ниже:
Дополнительное преимущество такому подходу дает привязка типа данных локальной переменной, в которую извлекаются записи, непосредственно к типу строго типизированного курсора. Благодаря этому экономится время выполнения, так как Oracle не придется заниматься выяснением структуры возвращаемых данных. Помимо этого, появляется возможность координировать операторы SELECT и параметры курсоров с помощью централизованного пакета - в том случае, если возникнет желание что-либо изменить. Не придется менять каждую функцию для сохранения согласованности.
Табличные функции возвращают коллекции, поэтому нет простого способа объявить строго типизированный REF CURSOR, чтобы использовать его при вложении. Поэтому приходится объявлять тип RECORD с той же структурой, что и у коллекции, а затем связывать с ней REF CURSOR таким способом:
Во втором примере значение передается прямо в функцию и там используется для формирования результирующего множества.
Не забывайте о производительности, когда применяете условия к функциям, особенно когда решаете, передать ли значения параметрами в функцию, чтобы они там использовались для формирования результирующего множества, или применить их к возвращаемому результирующему множеству. Вот два примера, поясняющие эту мысль. В первом из них условие (col1 = A) применяется к возвращаемому множеству записей после того, как оно было сформировано функцией.
Оцените сложность алгоритма и объем данных, чтобы решить, какой из подходов предпочтительнее в вашем случае.
Теперь REF CURSOR со строгим контролем типа можно использовать во вложенной табличной функции.
Разработав несколько приложений с использованием табличных функций, я с тревогой заметил, как растет количество повторяющихся типов объектов и коллекций. Например, я создал два таких объекта:
Затем создал на их основе коллекции с соответствующими именами, заменив суффикс «_о» на «_t». Это самый простой пример той неразберихи, которую я создал, сосредоточившись на частностях, а не базе данных в целом. Потом я вернулся к этому месту и заменил эти два объекта одним:
У меня есть аналогичные базовые объекты и для других типов данных, включая поля VARCHAR2 нескольких стандартных размеров.
Еще один стандарт, которого я придерживаюсь, - это использование суффикса «_о» в именах объектов и суффикса «_t» в именах коллекций (или таблиц). Это позволяет мне быстро различать их типы.
Обработка исключений в функциях, помещенных в оператор SELECT, требует особого внимания. Здесь это несколько сложнее, чем простая передача ошибки в вызывающую программу. Например, что делать в ситуации, когда в приведенном примере функция порождает исключение NO DATA FOUND?
В этом случае Oracle придется поддерживать две структуры выходных данных: одну для успешного выполнения, а вторую, состоящую из одного столбца VARCHAR2, для возможного сообщения об ошибке. Такой подход возможен, но он стал бы источником проблем для вложенных табличных функций, так как им тоже пришлось бы обрабатывать выходные данные, имеющие две различные структуры. Это было бы слишком сложно.
Это лучше, чем возвращать сообщение об ошибке, но иногда может сбивать с толку.
Решение, применяемое Oracle, заключается в том, что функция, в которой возникла ошибка, просто не возвращает строк.
Надо очень тщательно обрабатывать все возможные исключения, иначе ваша табличная функция может не сообщить о возникшей ошибке.
Не все знают, что табличные функции могут в качестве параметров принимать не только курсоры, но и коллекции. Вот простой пример:
| < Предыдущая | Следующая > |
|---|
