Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 запрос с Connect By. Оптимален ли вариант  [new]
AlRight
Guest
Приветствую всех. Решаю задачку нахождения "пути от родителя к детёнышу". Необходимо вывести перечень промежуточных объектов при известных детёныше и родителе. Глубина вложенности не ограничена. Циклов нет.
Пример: "деревянная" табличка t (c - детёныш, p - родитель)
with t as (select 1 c, 0 p from dual union all
select 2 c, 1 p from dual union all
select 3 c, 1 p from dual union all
select 4 c, 2 p from dual union all
select 5 c, 0 p from dual union all
select 3 c, 5 p from dual union all
select 6 c, 5 p from dual union all
select 4 c, 6 p from dual
)
получились 2 дерева:

1
2
4
3
5
3
6
4
При условии "детёныш - 4, родитель - 1", выборка должна вернуть:

1
2
4
Пока не придумал ничего лучше, чем найти пересечение двух множеств - всех детёнышей заданного родителя и всех родителей заданного детёныша:
select c from t  
start with c=1
connect by prior c=p
intersect
select  c
from t connect by prior p=c start with c=4
Есть ли более грамотное решение?
27 окт 08, 10:29    [6358330]     Ответить | Цитировать Сообщить модератору
 Re: запрос с Connect By. Оптимален ли вариант  [new]
Добрый Э - Эх
Guest
Кто мешает сделать банальнейший обход дерева в обратном направлении - от детёныша к родителю?
27 окт 08, 10:32    [6358343]     Ответить | Цитировать Сообщить модератору
 Re: запрос с Connect By. Оптимален ли вариант  [new]
Добрый Э - Эх
Guest
+ анализ "пути" (sys_connect_by_path)
27 окт 08, 10:35    [6358360]     Ответить | Цитировать Сообщить модератору
 Re: запрос с Connect By. Оптимален ли вариант  [new]
AlRight
Guest
Добрый Э - Эх,
Второе множество из пересекаемых и делает, имхо, обратный обход - от детёныша к родителю (если я правильно понимаю терминологию).
А вообще - подумал и понял, что поставленных условий недостаточно. В моей задачке один и тот же детёныш может находиться на разных уровнях вложенности, входить в родителя одновременно через разные цепочки детёнышей. Поэтому результат, получаемый intersect'ом - не верен
27 окт 08, 11:14    [6358616]     Ответить | Цитировать Сообщить модератору
 Re: запрос с Connect By. Оптимален ли вариант  [new]
Andrey.L
Member

Откуда: Харьков
Сообщений: 1546
AlRight
Добрый Э - Эх,
Второе множество из пересекаемых и делает, имхо, обратный обход - от детёныша к родителю (если я правильно понимаю терминологию).
А вообще - подумал и понял, что поставленных условий недостаточно. В моей задачке один и тот же детёныш может находиться на разных уровнях вложенности, входить в родителя одновременно через разные цепочки детёнышей. Поэтому результат, получаемый intersect'ом - не верен


По-моему идея у тебя правильная (если я правильно понял задачу).
Только пересечение тебе нужно при таких исходных данных
with t as (select 1 id, 1 c, 0 p from dual union all
select 2 id, 2 c, 1 p from dual union all
select 3 id, 3 c, 1 p from dual union all
select 4 id, 4 c, 2 p from dual union all
select 5 id, 5 c, 0 p from dual union all
select 6 id, 3 c, 5 p from dual union all
select 7 id, 6 c, 5 p from dual union all
select 8 id, 4 c, 6 p from dual
)
select id from t  
start with c=1
connect by prior c=p
intersect
select  id
from t connect by prior p=c start with c=4
27 окт 08, 11:36    [6358743]     Ответить | Цитировать Сообщить модератору
 Re: запрос с Connect By. Оптимален ли вариант  [new]
Добрый Э - Эх
Guest
AlRight
Добрый Э - Эх,
Второе множество из пересекаемых и делает, имхо, обратный обход - от детёныша к родителю (если я правильно понимаю терминологию).
А вообще - подумал и понял, что поставленных условий недостаточно. В моей задачке один и тот же детёныш может находиться на разных уровнях вложенности, входить в родителя одновременно через разные цепочки детёнышей. Поэтому результат, получаемый intersect'ом - не верен

У-гу. Вот, попытался реализовать то, что посоветовал.
Вышел такой уродец:
with t as (select 1 c, 0 p from dual union all
select 2 c, 1 p from dual union all
select 3 c, 1 p from dual union all
select 4 c, 2 p from dual union all
select 5 c, 0 p from dual union all
select 3 c, 5 p from dual union all
select 6 c, 5 p from dual union all
select 4 c, 6 p from dual
)
select c, p
 from (
        select c,p,
               first_value(path) 
                      over(partition by tree_id 
                               order by lv desc) as path
          from (
                 select c, p, level as lv,
                        replace(sys_connect_by_path(decode(level,1,c),','),',')||'~'||
                        replace(sys_connect_by_path(decode(level,1,p),','),',') as tree_id,
                        rtrim(replace(replace(
                          replace(sys_connect_by_path(
                            decode(c, :c, c, :p , c),','),',,',','||chr(0)),chr(0)||','),chr(0))
                             ,',')||',' as path
                   from t 
                   connect by prior p = c 
                   start with c = :c
               )
       )
 where path like '%,'||:c||','||:p||',%'

Из плюсов - дерево строим один раз.
Из минусов - решение совсем нечетабельное.
Как вариант, можно сделать гибрид твоего и моего решения.
27 окт 08, 11:45    [6358810]     Ответить | Цитировать Сообщить модератору
 Re: запрос с Connect By. Оптимален ли вариант  [new]
AlRight
Guest
Буду пробовать вплести это в свой код. Надо ещё отобрать самую длинную цепочку

Спасибо за идеи :)
27 окт 08, 12:01    [6358906]     Ответить | Цитировать Сообщить модератору
 Re: запрос с Connect By. Оптимален ли вариант  [new]
max(id)
Member

Откуда: Владивосток
Сообщений: 407
Ну и для расширения спектра представленных решений предлагаю такой простецкий вариант:
with t as (select 1 c, 0 p from dual union all
select 2 c, 1 p from dual union all
select 3 c, 1 p from dual union all
select 4 c, 2 p from dual union all
select 5 c, 0 p from dual union all
select 3 c, 5 p from dual union all
select 6 c, 5 p from dual union all
select 4 c, 6 p from dual
)
select c, p
from ( select c, p
       from t
       start with c = :c
       connect by prior p = c
     )
start with c = :p
connect by prior c = p 
Дерева конечно же строится два, но второе дерево строится на более ограниченом наборе и нет intersect-а.
28 окт 08, 05:19    [6363068]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить