Курсорные выражения - это, по сути, вложенные курсоры. Когда мы говорим о «курсорных выражениях», мы не имеем в виду вложенные подзапросы, определяющие результирующее множество. Речь идет о вложенных запросах, возвращающих вложенное результирующее множество. Объясним это на примере.
Этот запрос возвращает список заказов вместе с курсором, позволяющим позже запросить данные конкретного заказа. Вот как это может быть использовано в PL/SQL-процедуре:
Курсорные выражения имеют не вполне очевидный синтаксис, но их использование дает некоторые преимущества. Главное из них заключается в том, что курсорные выражения напрямую связывают логический и физический уровни обработки как для оптимизатора Oracle, так и для программного кода. Оптимизатор извлекает выгоду из наличия явно указанной связи между двумя таблицами (ORDERS и OR- DER_LINES), что позволяет ему сделать лучший выбор в тот момент, когда надо будет извлекать содержимое заказа. Программный код сам по себе ограничивает физическую работу, решая на основе логических условий, стоит ли вообще извлекать данные по определенному заказу. Поэтому не приходится запрашивать записи лишь затем, чтобы проигнорировать их в дальнейшем.
Также интересно проанализировать, что попадает в SGA после выполнения такой вложенной процедуры для 1000 заказов.
Обратите внимание на то, что правая часть предложения WHERE вложенного запроса превратилась в переменную связывания курсора. Так осуществляется связь с главным курсором. Заметьте также, что счетчики
разборов и выполнений для второго курсора имеют значения, равные 500, поскольку он выполнился ровно 500 раз, как и было необходимо. Еще более важно то, что доступ к данным выполнялся только 500 раз.
После того как процедура выполнена, в этом сеансе остается открытым только главный курсор. Однако во время ее выполнения открывалось множество других курсоров. Вы можете увидеть это, добавив в код 10-секундную задержку и обратившись к представлению
V$OPEN_CURSORS.
Выясняется, что значение 500 для второго курсора относится к числу операций открытия до того момента, как процедура завершится и все закроет (так как курсоры выходят из области видимости). Все 500 вложенных курсоров будут использовать уже находящуюся в SGA скомпилированную версию, что видно по постоянно растущему значению счетчиков разборов и выполнений после шести выполнений вложенной процедуры.
Однако из этого описания следует, что все 500 раз преимущества мягкого закрытия остались нереализованными. Также получается, что мы очень близко подходим к разрешенному максимуму для OPEN_CUR- SORS. Поэтому в таких случаях лучше явно закрывать вложенный курсор, когда работа с ним закончена. (Это может быть неочевидным, так как вложенный курсор не использует явной операции открытия.) Вот измененный участок кода:
Уверен, к этому моменту вы уже недоумеваете, почему бы не улучшить наш пример и не переписать его с использованием единственного курсора, примерно так:
Затем должен следовать PL/SQL-код проверки делимости номера заказа надвое без остатка. Различие в этих двух подходах заключается в количестве строк, обработанных при выполнении запроса. В варианте с вложенным курсором получаем следующие значения:
Для построения результирующего множества Oracle должен обработать на четыре тысячи строк меньше. Может показаться, что это немного, но надо учитывать, что в загруженной системе Oracle должен будет сохранять согласованную по чтению копию обрабатываемых записей на всем протяжении запроса, и этих записей будет на 4000 меньше.
Еще одним вариантом может быть добавление выражения MOD(or- der_number,2) = 0 непосредственно в текст запроса, что синтаксически
вполне допустимо. Однако оптимизатор Oracle может выбрать план выполнения, предусматривающий выборку всех позиций заказов, а удаление нечетных выполнить в оперативной памяти. Конечно, эту проблему можно решить использованием индекса по ключу-функции (function-based index), но этот путь тоже требует накладных расходов.
Переход к одному запросу также сводит на нет выгоду от использования массовой выборки позиций заказа в дальнейшем.
Oracle не обеспечивает для вложенных курсоров уровень изоляции READ COMMITTED. Результирующие множества поддерживаются только от момента неявного открытия и до последующего закрытия вложенного курсора. Однако для главного курсора обеспечен уровень изоляции READ COMMITTED.
Еще одной жизнеспособной альтернативой было бы использование двух курсоров, одного для выборки заказов, другого - для выборки позиций заказа. Но тогда оптимизатор вынужден будет рассматривать их как два отдельных курсора, так как ему неизвестно об их связи.
В этой главе с двух разных точек зрения рассмотрено взаимодействие курсоров и PL/SQL. Первая связана с ежедневной работой по администрированию баз данных: такие механизмы, как повторное использование курсоров, их разбор и часто не замечаемый неполный разбор должны быть известны каждому администратору, так как влияют на работу практически каждого приложения Oracle. Вторая в большей степени ориентирована на разработку приложений; такие вопросы, как массовая выборка и использование типа REF CURSOR, хотя и не возникают в работе администратора базы данных ежедневно, но тоже требуют понимания. Ваша работа требует не только отслеживания и диагностики проблем с базой данных. Не менее важно дать правильные рекомендации разработчикам, чтобы ваша база данных не потеряла своей производительности.
| < Предыдущая | Следующая > |
|---|
