JannnyНичего не понимаю:
1) какая связь с темой топика??
2) а что Вы хотите - в одном случае Вы говорите построить дерево, потом отфильровать, а во втором случае наоборот??
ЗЫ: и зачем Вам ROWNUM и sys_connect_by_path? Чтобы было чем ответить на вопрос 1?
Нет, не для этого. Показалась нелогичным отложенная фильтрация строк запроса. Спасибо Apex за
ссылку на TFM :) В моих запросах практически не используются иерархические запросы, зато очень
болезнены вопросы фильтрации.
На табличке в 10-100 млн строк промахнешься - мало не покажется.
Поэтому мне казалось (хотелось :) ) чтобы оракл отбросил лишние строки как можно быстрее.
Почитав FM понял что это частный случай, поэтому оракл реализовал другой подход.
Все дальнейшее написано потому, что на форуме читателей гораздо больше писателей, поэтому
может быть кому-то будет полезно. Для тех, кому это все тривиально - можно не читать :)
Надеюсь, что ошибок мало :)
Собственно, на бытовом языке эти два алгоритма звучат так:
1.(Хотелось) Возьми исходные строки, отфильтруй лишнее, построй дерево и выдай результат.
2.(На самом деле) Возьми исходные строки, построй дерево, отфильтруй лишнее, выдай результат.
Причем, выдать результат надо как можно раньше. Т.е. если строка уже готова к выдаче(прошла фильтрацию),
и все колонки могут быть вычислены - то они вычисляются, строка отправляются пользователю и идет дальнейшее
построение дерева.
В первом алгоритме результат может быть неполным, потому что при фильтрации строк могут быть
потеряны некоторые промежуточные строки, из-за которых нижележащие в дереве уровни пропадут,
хотя будут удовлетворять условиям фильтра.
Например, Пусть есть таблица сотрудников компании из стандартного примера SCOTT.EMP. И нужно
выбрать сотрудников, чье имя начинается на букву "J" .
1.
2.
3.
4.
select empno, ename, level, sys_connect_by_path(ename,'->') as p from scott.emp
where ename like 'J%'
start with mgr is null
connect by mgr = prior empno
Если бы оракл действовал по этому алгритму, то он бы потерял JAMES, у которого менеджером
является BLAKE, чье имя не начинается на букву J.
собственно, правильный алгоритм описан в доке, на которую указал APEX.
Теперь собственно, к теме топика :)
В принципе, ModelR правильно указал на алгоритм, который приводит к такому неочевидному результату.
Мне захотелось потдвердить это на тестовом примере.
Кстати, он же помог понять как работает этот запрос в случае аналитических функций.
Для того, чтобы понять в каком порядке выполняется запрос, нужна вспомогательная функция,
которая будет давать всякий раз новое значение. Sequence для этого не подходит, потому что в
переделах строки не изменяется.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
create or replace package a#h
is
function get_id return integer;
procedure reset_id;
end a#h;
create or replace package body a#h
is
cnt integer;
function get_id return integer
is
begin
cnt:= cnt + 1 ;
return cnt;
end get_id;
/*
Сбрасываем переменную в 0, чтобы получать воспроизводимый результат
в тестовых запросах
*/
procedure reset_id is
begin
cnt := 0 ;
end reset_id;
begin
cnt := 0 ;
end a#h;
перед выполнением запросов будем вызывать a#h.reset_id;
Я немного дополнил исходный запрос Elic'a чтобы видно было этапы выполнения запроса:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select begin_id, end_id, rnum, path_id, path_r from (
select a#h.get_id as end_id, a.* from
( select b.*, rownum as rnum
, sys_connect_by_path(a#h.get_id, '~') as path_id
, sys_connect_by_path(rownum, '~') as path_r
from (select a#h.get_id as begin_id from dual ) b
where mod(level, 3 ) = 0 and a#h.get_id > - 1
start with a#h.get_id = 1
connect by level <= 10 and a#h.get_id > - 2
) a
)
В колонке PATH_ID хранится путь к корню дерева, причем, номер отражает реальный номер строки источника - до фильтрации.
Он возвращает следующий результат:
BEGIN_IDEND_IDRNUMPATH_IDPATH_R891~2~4~6~1~1~117182~2~4~6~11~13~15~1~1~1~2~2~226273~2~4~6~11~13~15~20~22~24~1~1~1~2~2~2~3~3~3
Теперь можно видеть, что запрос выполняется следующим образом:
Первая строка - номер, с которым строка будет выдана пользователю. Как и говорил ModelR, rownum
всякий раз увеличивается после выдачи строки пользователю. Т.е. rownum в процесс выполнения
запроса можно трактовать как "Сейчас готовится к выдаче строка с номером Rownum"
Цифры в ячейках означают номер шага выполнения алгоритмы. Цифра в скобках означает сколько
раз с начала запроса была вызвана функция a#h.get_id
Rownum11111111222222223333333344Level1122333344556666778899991010select begin_id, end_id, rnum, path_id, path_r from (10(8)11(9)21(17)22(18)32(26)33(27)select a#h.get_id as end_id, a.* from( select b.*, rownum as rnumВзять упорядоченный список родителей и добавить туда себя для функции sys_connect_by_path(a#h.get_id, '~')2(2)5(4)8(6)13(11)16(13)19(15)24(20)27(22)30(24)35(29) , sys_connect_by_path(a#h.get_id, '~') as path_id , sys_connect_by_path(rownum, '~') as path_r from (select a#h.get_id as begin_id from dual ) b where mod(level, 3) =0 and a#h.get_id > -13(2)6(4)9(7)14(11)17(13)20(16)25(20)28(22)31(25)36(29) start with a#h.get_id =11(1) connect by level <= 10 and a#h.get_id > -24(3)7(5)12(10)15(12)18(14)23(19)26(21)29(23)34(28)) a)
Несколько комментариев:
1. На шагах 3,6 счетчик вызовов функции a#h.get_id не меняется, так как имеет место быть быстрое
вычисление логического выражения: так как mod(level, 3) =0 - FALSE, то вторую часть вычислять не надо. На шаге 9, mod(level, 3) =0 - TRUE и поэтому вычисляем второе условие and a#h.get_id > -1
2. Судя по всему, Оракл ленив, и вызывает вычисление функции только при крайней необходимости.
Поэтому, например, begin_id и end_id вычисляются для строки самыми последними -
непосредственно, перед выдачей строки клиенту.
3. Из приведенного примера неясно в какой момент вызывается функция sys_connect_by_path - в
процессе обработки каждой строки, в виде, например выражения:
result := result ||delimiter||current_expression, где current_expression - значение аргумента функции для этой строки.
Либо при проходе по дереву формируется как-то список родителей, включая себя, а
непосредственно перед выдачей уже присходит обработка этого списка и превращение в строку
результата.
4. Как видно из пример, фильтрация происходит после обработки строки и в случае успеха, строка
отдается на формирование внешнего вида и выдачи, а в случае неуспеха - просто идет переход к
следующей строки в дереве. Поэтому такие странные значения стоят в выводе функции
sys_connect_by_path(rownum, '~')
Для аналитической функции механизм обработки немного изменяется.
