SQL запрос обычно содержит управляющие параметры. Как их передать правильно?
Есть таблица square_test из двух столбцов (arg, func), квадратная функция аргумента - просто для примера:
Мы хотим извлечь одну строку, скажем для значения аргумента 5, и пишем запрос, в условную часть которого where записываем нужное нам условие arg = 5 - получаем искомый результат:
Казалось бы, все правильно, и так часто учат. А на самом деле правильно вот так - и мы сейчас обсудим очень важный момент, способный сильно повлиять на производительность системы:
Дело в том, что, получив SQL запрос, Oracle не может выполнить его непосредственно, а должен сначала построить план исполнения запроса, т.е. решить, как именно он будет извлекать данные. Для запроса к одной записи одной таблицы план будет очевидным и единственным. Для сложного запроса к нескольким таблицам, как правило, возможно несколько вариантов плана достижения одного и того же результата, различающихся стоимостью исполнения (cost) - и Oracle должен будет самостоятельно выбрать наиболее "дешевый" вариант. И так для каждого запроса:
Поскольку столбец arg нашей таблицы объявлен первичным ключом, то на нем создан индекс. Запрос с точным указанием единственного значения индексированного столбца получился очень эффективным, его стоимость равна всего 1, лучше и не бывает. Но в фазе исполнения. А мы, напомню, говорим о фазе построения планов.
Построение планов исполнения и выбор наилучшего из них является очень дорогой операцией и Oracle старается экономить на ней. Поэтому он складывает уже разобранные запросы и выбранные для них - наилучшие - планы исполнения в некий справочник. Получив новый запрос, Oracle сначала проверяет по справочнику, а не был ли этот запрос уже разобран, нет ли для него готового плана? Нельзя ли сэкономить на дорогущем разборе? И проверяет Oracle в два этапа - сначала берет хэш-функцию от текста запроса и сравнивает ее результат со столбцом хэш-значений для уже разобранных запросов в справочнике.
И каков же хэш от двух одинаковых запросов, но для двух разных значений параметра, скажем, 5 и 6?
Для Oracle эти два запроса абсолютно разные. А что ему еще делать? Мы рассматриваем элементарный пример, где, как говорится, "мог бы и догадаться". Но механизм-то единый для всех запросов, большинство из которых далеко не тривиальны. Поэтому Oracle действует жестко: нет хэша в справочнике - на разбор!
Если хэш одинаковый, то Oracle выполнит второй этап сравнения, уже непосредственно по тексту нового запроса и найденного в справочнике совпадающего хэша.
Так вот, если наша бизнес-логика в среднем слое - например, PHP - предполагает что-то такого типа:
for ($i = 1; $i <= ...; $i++) {
$sql = "select... from... where... = ".$i;
...
}
- то мы - сами! своими руками! - заставим Oracle циклически каждый раз заново разбирать абсолютно одинаковые для нас и абсолютно разные для него запросы. Эта работа будет доминировать над смысловой, поскольку разбор запроса гораздо более ресурсоемкий, чем его исполнение. Ресурс будет надежно занят бесполезной работой.
Хуже того. Oracle будет забивать справочник планами разбора по сути копий запроса, вытесняя при этом из справочника "хорошие" - и, вероятно, сложные - запросы. Поскольку Oracle, как правило, является многопользовательской системой, то разработчик, использующий литеральные запросы, будет надежно мешать работе более грамотно написанных приложений. Ученик "положит" работу учителей.
Правильнее будет передать в Oracle запрос, поставив на место значения параметра т.н. placeholder, bind, подстановочную переменную, а перед исполнением связать значения:
for ($i = 1; $i <= 10; $i++) {
$sql = "select... from... where... = :x";
$stmt = oci_parse(..., $sql);
oci_bind_by_name($stmt, ":x", $i);
oci_execute($stmt);
...
}
В этом случае, циклически получая запрос, Oracle будет проверять его по справочнику и начиная со второго раза миновать ресурсоемкую фазу его разбора.
Заметим в скобках, сам-то план для литеральной и биндованной версий запроса будет одинаковым, но в литеральной версии его будут упорно и дорого строить вновь и вновь, вот в чем грустный смех-то!
Еще правильнее передать запрос лишь один раз, до цикла, тогда Oracle не будет тратить время даже на проверку, а сразу будет использовать нужный план исполнения:
$sql = "select... from... where... = :x";
$stmt = oci_parse(..., $sql);
for ($i = 1; $i <= 10; $i++) {
oci_bind_by_name($stmt, ":x", $i);
oci_execute($stmt);
...
}
Связывание переменных предусмотрено и для других СУБД, но если у Oracle связывание поименное, то у других систем оно выполняется по порядку появления параметров в запросе.
Почему на курсах Web программирования часто забывают о связывании? Потому что классический курс Web программирования методически сложен: надо изучать, скажем, PHP и, скажем, Oracle - это две самостоятельные области и глубокое погружение в каждую из них требует большего учебного времени. Связывание параметров - частая жертва учебному плану.
Использование Oracle APEX для создания Web приложений помогает решить эту методическую проблему связывания параметров, особенно если курс нацелен на подготовку не программистов, а деловых людей, которым нужна более общая картина без жертвования экономически важными моментами.