|
Плоскую таблицу преобразовать в Иерархическую
#40121848
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Модератор форума
Откуда: Мск
Сообщения: 5 731
|
|
Почему-же? Простое в лоб MV дает и fast refresh и on commit. Все что нам нужно знать это max возможную глубину иерархии. Например 4 для таблицы EMP: ну, вообще, я говорил именно про рекурсивные запросы (что connect-by, что recursive subq factoring в списке ограничений). Да, конечно, способ с разворачиванием в джойны хорошо распространен, но ты сделал немного не так: в твоем запросе фактически идет схлопывание, а-ля connect_by_isleaf=1, что обычно добавляет сложности и далеко не везде зайдет.
Обычно делается что-то типа такого:
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.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
--drop materialized view mv_plain_hierarchy;
--drop table t_hierarchy purge;
create table t_hierarchy(
id int primary key
,parent_id int
,name varchar2(10)
,constraint fk_parent foreign key (parent_id) references t_hierarchy(id)
);
create materialized view log on t_hierarchy
with primary key,rowid,
sequence(parent_id,name)
including new values;
create materialized view mv_plain_hierarchy
refresh fast on commit
as
select
h0.id
,decode(null,h1.id,1,h2.id,2,h3.id,3,4) lvl
,coalesce(h3.id,h2.id,h1.id,h0.id) lvl_1
,decode(null, h1.id,to_number(null), h2.id,h0.id, h3.id,h1.id, h2.id) lvl_2
,decode(null, h1.id,to_number(null), h2.id,null, h3.id,h0.id, h1.id) lvl_3
,decode(null, h1.id,to_number(null), h2.id,null, h3.id,null , h0.id) lvl_4
,ck.id as ck
,h0.rowid h0_rid,h1.rowid h1_rid,h2.rowid h2_rid,h3.rowid h3_rid,ck.rowid ck_rid
from t_hierarchy h0
,t_hierarchy h1
,t_hierarchy h2
,t_hierarchy h3
,t_hierarchy ck
where
h1.id(+)=h0.parent_id
and h2.id(+)=h1.parent_id
and h3.id(+)=h2.parent_id
and ck.id(+)=h3.parent_id
;
create index mv_ix_1 on mv_plain_hierarchy(id);
create index mv_ix_2 on mv_plain_hierarchy(lvl_1);
alter table mv_plain_hierarchy add constraint mv_chk_level check (ck is null);
Что это дает:
- кол-во строк у обоих таблиц одинаково (легкая валидация, простые джойны при необходимости);
- каждый элемент изначальной таблицы имеет свою запись в развернутой, со своей доп.инфой типа уровня иерархии элемента и прочего, при необходимости;
- констрейнт дает возможность контролировать глубину иерархии, чтобы не случилось внезапного расхождения данных.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
begin
insert into t_hierarchy values (1 ,null,'name_1');
insert into t_hierarchy values (10 ,1 ,'name_10');
insert into t_hierarchy values (100 ,10 ,'name_100');
insert into t_hierarchy values (11 ,1 ,'name_11');
insert into t_hierarchy values (110 ,11 ,'name_110');
insert into t_hierarchy values (2 ,null,'name_2');
insert into t_hierarchy values (20 ,2 ,'name_20');
insert into t_hierarchy values (200 ,20 ,'name_200');
insert into t_hierarchy values (21 ,2 ,'name_21');
insert into t_hierarchy values (210 ,21 ,'name_210');
insert into t_hierarchy values (2100 ,210 ,'name_2100');
commit;
end;
/
мы получим:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SQL> select id,lvl,lvl_1,lvl_2,lvl_3,lvl_4 from mv_plain_hierarchy;
ID LVL LVL_1 LVL_2 LVL_3 LVL_4
---------- ---------- ---------- ---------- ---------- ----------
1 1 1
10 2 1 10
100 3 1 10 100
11 2 1 11
110 3 1 11 110
2 1 2
20 2 2 20
200 3 2 20 200
21 2 2 21
210 3 2 21 210
2100 4 2 21 210 2100
11 rows selected.
А при попытке вставить элемент более глубокого уровня получим ошибку:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SQL> begin
2 insert into t_hierarchy values (21000 ,2100 ,'fail');
3 commit;
4 end;
5 /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (XTENDER.MV_CHK_LEVEL) violated
ORA-06512: at line 3
Полный скрипт для тестов тут: https://gist.github.com/xtender/7d2449f3132624bf4b2819a4a4c01c87 |
|
|
