Добро пожаловать, Гость |
SQL, PL/SQL, T-SQL: запросы, DML DDL операторы, пакеты, процедуры, функции, триггеры и последовательности.
ТЕМА:
Re: Поиск по дереву в Oracle 12 года 9 мес. назад #2725
Идея состоит в следуюсчем: - построить список корневых узлов для которых не существует ни одного узла по дороге от него к корню дерева с тем зе самым ограничением; - использовать этот список как корневой узел в конструкции START WITH drop table train.test_tree ;
create table train.test_tree (
node_id number,
root_id number,
txt varchar2(20),
constraint pk_test_tree primary key (node_id)
);
insert into train.test_tree values ( 1, 0, 'node-1');
insert into train.test_tree values ( 2, 1, 'node-b');
insert into train.test_tree values ( 3, 1, 'node-a');
insert into train.test_tree values ( 4, 1, 'node-d');
insert into train.test_tree values ( 5, 2, 'node-a');
insert into train.test_tree values ( 6, 2, 'node-e');
insert into train.test_tree values ( 7, 3, 'node-f');
insert into train.test_tree values ( 8, 3, 'node-d');
insert into train.test_tree values ( 9, 3, 'node-a');
insert into train.test_tree values ( 10, 4, 'node-a');
insert into train.test_tree values ( 11, 4, 'node-x');
insert into train.test_tree values ( 12, 4, 'node-c');
insert into train.test_tree values ( 13, 7, 'node-v');
insert into train.test_tree values ( 14, 7, 'node-a');
insert into train.test_tree values ( 15, 10, 'node-n');
insert into train.test_tree values ( 16, 10, 'node-m');
insert into train.test_tree values ( 17, 10, 'node-l');
insert into train.test_tree values ( 18, 12, 'node-k');
insert into train.test_tree values ( 19, 12, 'node-j');select t.node_id, substr(lpad('.',(level-1)*2,'.') || t.txt,1,40) txt
from train.test_tree t
connect by prior t.node_id = t.root_id
start with t.node_id in ( select i.node_id from train.test_tree i
where txt = 'node-a'
and
not exists ( select null from train.test_tree z
where z.txt = 'node-a' and level > 1
connect by prior z.root_id = z.node_id
start with z.node_id = i.node_id )
)
;
NODE_ID TXT
---------- ----------------------------------------
3 node-a
7 ..node-f
13 ....node-v
14 ....node-a
8 ..node-d
9 ..node-a
5 node-a
10 node-a
15 ..node-n
16 ..node-m
17 ..node-lselect t.node_id, substr(lpad('.',(level-1)*2,'.') || t.txt,1,40) txt
from train.test_tree t
connect by prior t.node_id = t.root_id
start with t.node_id = 1;
NODE_ID TXT
---------- ----------------------------------------
1 node-1
2 ..node-b
5 ....node-a
6 ....node-e
3 ..node-a
7 ....node-f
13 ......node-v
14 ......node-a
8 ....node-d
9 ....node-a
4 ..node-d
10 ....node-a
15 ......node-n
16 ......node-m
17 ......node-l
11 ....node-x
12 ....node-c
18 ......node-k
19 ......node-j | |
Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе. |
Re: Поиск по дереву в Oracle 12 года 9 мес. назад #2726
Огромное спасибо! Практически то что нужно! У меня дерево например такое: NODE_ID TXT
---------- ----------------------------------------
1 node-1
2 ..node-b
3 ....node-a
4 ....node-e
5 node-1
6 ..node-f
7 ....node-v
8 ......node-a
9 ....node-d
10 ....node-a
11 node-1
12 ..node-e
13 ....node-aи если выбирать например node-e, то результат должен быть NODE_ID TXT
---------- ----------------------------------------
1 node-1
2 ..node-b
3 ....node-a
4 ....node-e
11 node-1
12 ..node-e
13 ....node-aт.е. вся ветвь (это наверное уже под-дерево) начиная от корневого узла (у которого родитель null) должна включаться в выборку. | |
Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе. |
Re: Поиск по дереву в Oracle 12 года 9 мес. назад #2727
Нет под рукой Оракула, чтобы проверить, но, по-моему, должно сработать следующее: select t.node_id, substr(lpad('.',(level-1)*2,'.') || t.txt,1,40) txt
from train.test_tree t
connect by prior t.node_id = t.root_id
start with t.node_id in (
select distinct z.node_id from (
select i.node_id, i.root_id
from train.test_tree i
connect by prior i.root_id = i.node_id
start with i.txt = 'node-a'
) z
where z.root_id is null
) | |
Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе. |
Re: Поиск по дереву в Oracle 12 года 9 мес. назад #2728
Значит я несколько недопонял задачу. Но в этой постановке она решается намного проще. 1) Получить корневые узлы САМОГО ВЕРХНЕГО УРОВНЯ для каждого узла у которого выполняется требуемое условие. 2) Построить поддеревья для каждого ВЫБРАННОГО КОРНЕВОГО УЗЛА. Правда пришлось немного подогнать данные, т.к. предыдущее дерево имело 1 общий корень а для данной задачи требуется много корней. truncate table train.test_tree;
insert into train.test_tree values ( 2, 0, 'node-b');
insert into train.test_tree values ( 3, 0, 'node-a');
insert into train.test_tree values ( 4, 0, 'node-d');
insert into train.test_tree values ( 5, 2, 'node-s');
insert into train.test_tree values ( 6, 2, 'node-e');
insert into train.test_tree values ( 7, 3, 'node-f');
insert into train.test_tree values ( 8, 3, 'node-d');
insert into train.test_tree values ( 9, 3, 'node-a');
insert into train.test_tree values ( 10, 4, 'node-a');
insert into train.test_tree values ( 11, 4, 'node-x');
insert into train.test_tree values ( 12, 4, 'node-c');
insert into train.test_tree values ( 13, 7, 'node-v');
insert into train.test_tree values ( 14, 7, 'node-a');
insert into train.test_tree values ( 15, 10, 'node-n');
insert into train.test_tree values ( 16, 10, 'node-m');
insert into train.test_tree values ( 17, 10, 'node-l');
insert into train.test_tree values ( 18, 12, 'node-k');
insert into train.test_tree values ( 19, 12, 'node-j');Запрос теперь выглядит так: -- внутренний подзапрос выбирает ТОЛЬКО корневые узлы обратным проходом от узлов для которых выполняется условие. select t.node_id, substr(lpad('.',(level-1)*2,'.') || t.txt,1,40) txt
from train.test_tree t
connect by prior t.node_id = t.root_id
start with t.node_id in ( select i.node_id from train.test_tree i
where i.root_id = 0
connect by prior i.root_id = i.node_id
start with i.txt = 'node-a')
;
NODE_ID TXT
---------- ----------------------------------------
3 node-a
7 ..node-f
13 ....node-v
14 ....node-a
8 ..node-d
9 ..node-a
4 node-d
10 ..node-a
15 ....node-n
16 ....node-m
17 ....node-l
11 ..node-x
12 ..node-c
18 ....node-k
19 ....node-j
15 rows selected.Примечание запрос проверен и при множественных узлах в поддеревьях. Для узла = node-a NODE_ID TXT
---------- ----------------------------------------
3 node-a
7 ..node-f
13 ....node-v
14 ....node-a
8 ..node-d
9 ..node-a
4 node-d
10 ..node-a
15 ....node-n
16 ....node-m
17 ....node-l
11 ..node-a
12 ..node-c
18 ....node-a
19 ....node-j
15 rows selected. | |
Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе. |
Re: Поиск по дереву в Oracle 12 года 9 мес. назад #2729
Всем спасибо! Оба примера работают отлично. Есть еще вопрос о том как можно оптимизировать этот запрос? У меня в таблице всего 3371 записей. Когда я выполняю запросы, то например, запрос возвращающий 1181 записей выполняется около 14 секунд! Можно ли как нибудь ускорить выполнение запроса или для этого придется отказаться от использования connect by / start with ? Я использую PLSQL/Developer и воспользовался функцией explain plan (optimizer goal = choose) для этого запроса select t.id, t.programme, t.episode
from hr_archive t
connect by prior t.id = t.parent_id
start with t.id in ( select i.id from hr_archive i
where i.parent_id = -1
connect by prior i.parent_id = i.id
start with upper(i.episode) like '%ON%')SELECT STATEMENT, GOAL = CHOOSE
CONNECT BY WITH FILTERING
NESTED LOOPS
NESTED LOOPS
VIEW SYS VW_NSO_1
SORT UNIQUE
FILTER
CONNECT BY WITH FILTERING
NESTED LOOPS
TABLE ACCESS FULL TMDDBA HR_ARCHIVE
TABLE ACCESS BY USER ROWID TMDDBA HR_ARCHIVE
NESTED LOOPS
BUFFER SORT
CONNECT BY PUMP
TABLE ACCESS BY INDEX ROWID TMDDBA HR_ARCHIVE
INDEX RANGE SCAN TMDDBA HR_ARCHIVE_ID_IDX
INDEX RANGE SCAN TMDDBA HR_ARCHIVE_ID_IDX
TABLE ACCESS BY USER ROWID TMDDBA HR_ARCHIVE
NESTED LOOPS
BUFFER SORT
CONNECT BY PUMP
TABLE ACCESS BY INDEX ROWID TMDDBA HR_ARCHIVE
INDEX RANGE SCAN TMDDBA HR_ARCHIVE_PARENT_ID_IDX
FILTER
CONNECT BY WITH FILTERING
NESTED LOOPS
TABLE ACCESS FULL TMDDBA HR_ARCHIVE
TABLE ACCESS BY USER ROWID TMDDBA HR_ARCHIVE
NESTED LOOPS
BUFFER SORT
CONNECT BY PUMP
TABLE ACCESS BY INDEX ROWID TMDDBA HR_ARCHIVE
INDEX RANGE SCAN TMDDBA HR_ARCHIVE_ID_IDX | |
Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе. |
Re: Поиск по дереву в Oracle 12 года 9 мес. назад #2730
| Ну тогда раскажи нам следующее: статистика собиралась? Когда и какая? Как установлен параметер optimizer_index_cost_adj? Без этих сведений ничем не смогу помочь) |
Пожалуйста Войти или Регистрация, чтобы присоединиться к беседе. |
Время создания страницы: 0.255 секунд