Употребление процедур в запросах

Подразделы

• Общие сведения
• Примеры
• И всё-таки, соединение через параметр

Общие сведения

Основная ситуация, когда эти проблемы могут проявиться - соединение процедуры и простой таблицы. Где-то в недрах оптимизатора особенности процедур учитываются не до конца.

Так же должен отметить, что в отличие от предыдущей темы, поведение оптимизатора заметно изменилось в процессе развития InterBaseмежду версиями 4 и 6. Правда, с четвёркой давно таких экспериментов не устраивал, но на моей памяти случались совершенно странные ситуации, заканчивавшиеся падением сервера с сообщениями в стиле ``Internal gds consistency check''. Из шестёрки, на которой проводил эксперименты, такого получить не удалось. Более того, заставить её выйти на ``кривой'' план удалось только ручным планированием.

Но обо всём по порядку. Первопричина всех неприятностей - то, что ядро InterBase до сих пор не умеет делать соединение типа join с хранимыми процедурами. Здесь имеется в виду не конструкция join в select ... from, а одна из технологий соединения в plan. Факт, достойный сожаления, поскольку такое соединение во многих ситуациях -- одно из самых эффективных.

Так вот, по моим наблюдениям, в 4.2 оптимизатор мог совершенно спокойно сгенерировать план типа join(sp natural, table index(...)). После чего попытка выполнить такой план заканчивалась последствиями различной тяжести. В 5-ых версиях ситуация стала реже, а последствия перестали быть катастрофическими. Наконец, как я уже отмечал, в 6 оптимизатор вроде как научился преодолевать свою склонность к join, видя процедуру. Конечно, лучше было бы реализовать такой join, нежели предотвращать, но и на том спасибо. Однако это всего лишь моё предположение, попытки копать исходный код не привели меня к однозначному выводу о причинах такого поведения -- вполне возможно, что мне просто повезло.

Примеры

create procedure sp1(n integer)
       returns(id integer, x integer) as
begin
 for select id, x from test1 into :id, :x do
  begin 
    x = :x+:n; suspend; 
  end
end

Далее, как обычно, выполняем запросы со включённым отображением плана.

select * from sp1(3);
PLAN (TEST1 NATURAL)

Первый сюрприз. Если тело запроса заключено в оператор for select, то план выдаётся со ссылкой именно на этот запрос, а не на процедуру. Можно подумать, что InterBase вникает во внутренние особенности процедуры и учитывает это при планировании внешнего запроса. Однако это всего лишь ``оптический обман''.

select * from sp1(3) where id=5
PLAN (TEST1 NATURAL)

Если бы гипотеза была верна, то условие id=5 внеслось бы в where внутри процедуры, и план был основан на индексе первичного ключа. Невозможность такой оптимизации на самом деле проистекает из архитектуры InterBase. Для каждой процедуры выполняется компиляция в BLR, и тем самым фактически формируется фиксированный план для всех запросов и вообще всех операций, описанных внутри. В то время как план (и BLR) внешнего запроса может всего лишь вызвать процедуру, обеспечив обмен параметрами.

Но возвращаемся к основной теме.

select * from sp1(3) sp, test2 t where sp.id = t.id
PLAN MERGE (SORT (T NATURAL),SORT (TEST1 NATURAL))

select * from test2 t, sp1(3) sp where sp.id = t.id
PLAN MERGE (SORT (T NATURAL),SORT (TEST1 NATURAL))

select * from sp1(3) sp inner join test2 t on sp.id = t.id 
PLAN MERGE (SORT (T NATURAL),SORT (TEST1 NATURAL))

Неизменный результат. Правда, эксперимент проводился на IB6. Все процедурные особенности изолируются от остальной части плана через SORT(). Не самый эффективный, но безопасный вариант.

select * from sp1(3) sp, test2 t where sp.id = t.id
plan join(sp natural, t index(rdb$primary2))

Statement failed, SQLCODE = -104

invalid request BLR at offset 71
-BLR syntax error: expected TABLE at offset 71, 
 encountered 125

BLR 125 -- это (судя по jrd/blr.h) pid - procedure id. То есть для осуществления join нужна реальная таблица.

Ещё одна особенность, точнее на этот раз пожалуй даже глупость, которую периодически пытаются сделать новички. Ну например так:

select * from sp1(t.id) sp, test2 t

То есть в качестве параметра процедуры идёт одно из полей другого соединяемого отношения. Никогда не пройдёт. По той простой причине, что при любой технологии сначала выполняется соединение, а уже потом появляются поля.

Если не совсем понятно, то задумайтесь над таким фактом. Если допустить, что можно в качестве параметров употреблять поля из того же from, то почему нельзя употреблять поля той же процедуры. И что получаем? From sp1(sp.id) sp. Интересно, можно ли этому придумать ... ну если не разумную интерпретацию, то хотя бы способ реализации? Мне что-то на ум ничего не приходит.

Таким образом правильный способ реализации следующий:

for select ... from test2 t into :id, ... do
  for select ... from sp1(:id) into ... do suspend;

По-длиннее, конечно. Но зато никаких неоднозначностей.

И всё-таки, соединение через параметр

select * from test2 t left join sp1(t.id) on 0=0;
PLAN JOIN (T NATURAL,TEST1 NATURAL)

При работе же через join и внешнем соединении, InterBase делает ``сплошной'' проход по ``внешней'' стороне соединения (то есть эта сторона разумеется сама по себе может фильтроваться, в том числе и по индексам, но только внутри себя), и для каждой полученной записи вычисляет ``довесок'' из другой таблицы. Если вычислить не получилось, то присоединяются значения null. Подстановка параметров при этом тоже срабатывает.

Заключительная неожиданность этой области -- попробуем вручную добавить к этому запросу план через join.

select * from test2 t left join sp1(t.id) sp on 0=0 
plan sort merge(t natural, sp natural);

Statement failed, SQLCODE = -104

invalid request BLR at offset 77
-BLR syntax error: expected TABLE at offset 77, encountered 125