ReSQL.ru
     
powered by simpleCommunicator - 2.0.30     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Плоскую таблицу преобразовать в Иерархическую
42 сообщений из 42, показаны все 2 страниц
  все  
Плоскую таблицу преобразовать в Иерархическую
    #40121390
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Добрий день!
К сожалению ничего не нашел, может куда направите. :)
Есть плоска таблица с полями
Name1, Name2, Name3, Name4, Name5, Name6, ...
Каждое поле Name это отдельный уровень.
Нужно это преобразовать в иерархическую таблицу
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TABLE HIERARCHICAL (
  ID   NUMBER(10),
  PID  NUMBER,
  NAME VARCHAR2(250) NOT NULL,
  CONSTRAINT PK_HIERARCHICAL_ID PRIMARY KEY (ID),
  CONSTRAINT FK_HIERARCHICAL_PID FOREIGN KEY (PID)
  REFERENCES HIERARCHICAL (ID)
);
CREATE UNIQUE INDEX UK_HIERARCHICAL ON HIERARCHICAL (ID, NAME);


Сейчас я делаю для каждого уровня отдельный Insert.
Сложность еще состоит в том, что некоторые уровни (Name{N}) могут быть пустыми, следовательно, этот уровень нужно пропустить при добавлении в HIERARCHICAL. В этом случае количество Insert-ов для следующих уровней увеличивается в 2 раза.
Кроме того. Сейчас я удаляю все в HIERARCHICAL, а потом создаю наново, но это плохо. Мне нужны ID для дальнейшего использования, а они каждый раз при Insert будут (могут быть) разные. Конечно это тоже можно сделать Insert только новых и Delete отсутствующих, но это еще усложняет обновление HIERARCHICAL.
Может будут какие-то идеи упрощения всего этого процесса?
...
Рейтинг: 0 / 0
19.12.2021, 16:57
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121393
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
SergiiWМожет будут какие-то идеи упрощения всего этого процесса?

Использовать PL/SQL для преобразования данных.
Старую таблицу удалить чтобы этот процесс был одноразовым.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
19.12.2021, 17:02
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121394
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Dimitry Sibiryakov,

Спасибо!
Буду думать как это решить на PL/SQL.
...
Рейтинг: 0 / 0
19.12.2021, 17:11
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121395
Никанор Кузьмич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Никанор Кузьмич
Участник
SergiiW
Есть плоска таблица с полями
Name1, Name2, Name3, Name4, Name5, Name6, ...
...
Может будут какие-то идеи упрощения всего этого процесса?
Я бы сделал примерно так: unpivot + rownumber(), потом отсортировать по какому-нибудь старому полю + NameN, rownumber() становится id, rownumber() предыдущей строки использовать как parent_id. Вроде относительно просто.
Это если я правильно понял суть задачи.
...
Рейтинг: 0 / 0
19.12.2021, 17:16
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121403
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SergiiW
Может будут какие-то идеи упрощения всего этого процесса?
В эсэсэсэрах не научили предоставлять характерные исходные данные?
SergiiW
Мне нужны ID для дальнейшего использования, а они каждый раз при Insert будут (могут быть) разные. Конечно это тоже можно сделать Insert только новых и Delete отсутствующих, но это еще усложняет обновление HIERARCHICAL.
RTFM MERGE
...
Рейтинг: 0 / 0
19.12.2021, 17:53
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121458
Никанор Кузьмич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Никанор Кузьмич
Участник
Что-то типа такого нужно?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with t as (select 1 id, 'Vasya' name1, 'Petya' name2, '' name3 from dual union all
           select 2, 'Masha', 'Katya', 'Olya' from dual)
select id, lvl, val, new_pk, lag(new_pk, 1, null) over (partition by id order by id, lvl) parent_id
  from (select id, lvl, val, row_number() over (order by id, lvl) new_pk
          from (select id, lvl, val
                  from t
               unpivot (val for lvl in (name1 as 1, name2 as 2, name3 as 3))));

        ID        LVL VAL       NEW_PK  PARENT_ID
---------- ---------- ----- ---------- ----------
         1          1 Vasya          1           
         1          2 Petya          2          1
         2          1 Masha          3           
         2          2 Katya          4          3
         2          3 Olya           5          4
...
Рейтинг: 0 / 0
19.12.2021, 20:40
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121469
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
SergiiW

Сложность еще состоит в том, что некоторые уровни (Name{N}) могут быть пустыми, следовательно, этот уровень нужно пропустить при добавлении в HIERARCHICAL.



Не уверен что понял. Понял так: Name{N} пустой значит Name{N + 1} пустой и Name{N + 2} пустой и.т.д. ибо число полей плоской таблицы есть MAX(N).

Код: plsql
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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
CREATE TABLE TEST
  AS
     SELECT  'VANIA' NAME1,
             'PETYA' NAME2,
             'SASHA' NAME3,
             'PASHA' NAME4,
             'VOVA'  NAME5,
             NULL    NAME6
       FROM  DUAL
    UNION ALL
     SELECT  'MISHA' NAME1,
             'MASHA' NAME2,
             'PASHA' NAME3,
             'DASHA' NAME4,
             'GOSHA' NAME5,
             'KESHA' NAME6
       FROM  DUAL
/
INSERT
  INTO HIERARCHICAL
  WITH R(
         LVL,
         RID,
         ID,
         PID,
         NAME,
         OFFSET
        )
    AS (
         SELECT  1      LVL,
                 ROWID  RID,
                 ROWNUM ID,
                 NULL   PID,
                 NAME1  NAME,
                 COUNT(*) OVER() OFFSET
           FROM  TEST
        UNION ALL
         SELECT  R.LVL + 1         LVL,
                 R.RID             RID,
                 R.OFFSET + ROWNUM ID,
                 R.ID              PID,
                 CASE R.LVL
                   WHEN 1 THEN T.NAME2
                   WHEN 2 THEN T.NAME3
                   WHEN 3 THEN T.NAME4
                   WHEN 4 THEN T.NAME5
                   WHEN 5 THEN T.NAME6
                 END NAME,
                 R.OFFSET + COUNT(*) OVER() OFFSET
           FROM  R,
                 TEST T
           WHERE T.ROWID = R.RID
             AND R.NAME IS NOT NULL
             AND R.LVL <= 6
       )
  SELECT  ID,
          PID,
          NAME
    FROM  R
    WHERE NAME IS NOT NULL
/,

11 rows created.

SQL> SELECT  LPAD(' ',2 * (LEVEL - 1)) || ID ID,
  2          LPAD(' ',2 * (LEVEL - 1)) || PID PID,
  3          LPAD(' ',2 * (LEVEL - 1)) || NAME NAME
  4    FROM  HIERARCHICAL
  5    START WITH PID IS NULL
  6    CONNECT BY PID = PRIOR ID
  7  /

ID                   PID                  NAME
-------------------- -------------------- --------------------
1                                         VANIA
  3                    1                    PETYA
    5                    3                    SASHA
      7                    5                    PASHA
        9                    7                    VOVA
2                                         MISHA
  4                    2                    MASHA
    6                    4                    PASHA
      8                    6                    DASHA
        10                   8                    GOSHA
          12                   10                   KESHA

11 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
19.12.2021, 21:09
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121482
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Перемудрил. Все что нужно это пронумеровать строки плоской таблицы + LATERAL/CROSS APPLY:

Код: plsql
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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
INSERT
  INTO HIERARCHICAL
  WITH T AS (
             SELECT  ROWNUM * 1000 BASE_ID,
                     T.*
               FROM  TEST T
            )
  SELECT  T.BASE_ID + L.LVL,
          NULLIF(T.BASE_ID + L.LVL - 1,T.BASE_ID) PID,
          L.NAME
    FROM  T,
          LATERAL(
                  SELECT  LEVEL LVL,
                          CASE LEVEL
                            WHEN 1 THEN T.NAME1
                            WHEN 2 THEN T.NAME2
                            WHEN 3 THEN T.NAME3
                            WHEN 4 THEN T.NAME4
                            WHEN 5 THEN T.NAME5
                            ELSE NAME6
                          END NAME
                    FROM  DUAL
                    CONNECT BY LEVEL <= 6
                 ) L
    WHERE NAME IS NOT NULL
/

10 rows created.

SQL> SELECT  LPAD(' ',2 * (LEVEL - 1)) || ID ID,
  2          LPAD(' ',2 * (LEVEL - 1)) || PID PID,
  3          LPAD(' ',2 * (LEVEL - 1)) || NAME NAME
  4    FROM  HIERARCHICAL
  5    START WITH PID IS NULL
  6    CONNECT BY PID = PRIOR ID
  7  /

ID                   PID                  NAME
-------------------- -------------------- --------------------
1001                                      VANIA
  1002                 1001                 PETYA
    1003                 1002                 SASHA
      1004                 1003                 PASHA
2001                                      MISHA
  2002                 2001                 MASHA
    2003                 2002                 PASHA
      2004                 2003                 DASHA
        2005                 2004                 GOSHA
          2006                 2005                 KESHA

10 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
19.12.2021, 21:37
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121490
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SY
Перемудрил.
Код: plsql
1.
INSERT

Недо-
Для сохранения id-шников нужен merge по пути.
...
Рейтинг: 0 / 0
19.12.2021, 23:09
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121511
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Elic

Для сохранения id-шников нужен merge по пути.


Не понял. Ты имеешь ввиду строки плоской таблицы связаны и Вася во всех строках плоской таблицы это один и тот-же Вася?

SY.
...
Рейтинг: 0 / 0
20.12.2021, 01:24
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121513
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
А, ты имеешь ввиду "Мне нужны ID для дальнейшего использования, а они каждый раз при Insert будут (могут быть) разные". Это решается добавлением уникального поля к плоской таблице вместо ROWNUM:

Код: plsql
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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
DROP TABLE TEST PURGE
/
CREATE TABLE TEST
  AS
     SELECT  1 HIERARCHY_ID,
             'VANIA' NAME1,
             'PETYA' NAME2,
             'SASHA' NAME3,
             'PASHA' NAME4,
             NULL  NAME5,
             NULL    NAME6
       FROM  DUAL
    UNION ALL
     SELECT  2 HIERARCHY_ID,
             'MISHA' NAME1,
             'MASHA' NAME2,
             'PASHA' NAME3,
             'DASHA' NAME4,
             'GOSHA' NAME5,
             'KESHA' NAME6
       FROM  DUAL
/
TRUNCATE TABLE HIERARCHICAL
/
INSERT
  INTO HIERARCHICAL
  SELECT  T.HIERARCHY_ID * 1000 + L.LVL - 1 ID,
          CASE
            WHEN L.LVL > 1 THEN T.HIERARCHY_ID * 1000 + L.LVL - 2
          END PID,
          L.NAME
    FROM  TEST T,
          LATERAL(
                  SELECT  LEVEL LVL,
                          CASE LEVEL
                            WHEN 1 THEN T.NAME1
                            WHEN 2 THEN T.NAME2
                            WHEN 3 THEN T.NAME3
                            WHEN 4 THEN T.NAME4
                            WHEN 5 THEN T.NAME5
                            ELSE NAME6
                          END NAME
                    FROM  DUAL
                    CONNECT BY LEVEL <= 6
                 ) L
    WHERE NAME IS NOT NULL
/

10 rows created.

SQL> SELECT  LPAD(' ',2 * (LEVEL - 1)) || ID ID,
  2          LPAD(' ',2 * (LEVEL - 1)) || PID PID,
  3          LPAD(' ',2 * (LEVEL - 1)) || NAME NAME
  4    FROM  HIERARCHICAL
  5    START WITH PID IS NULL
  6    CONNECT BY PID = PRIOR ID
  7  /

ID                   PID                  NAME
-------------------- -------------------- --------------------
1000                                      VANIA
  1001                 1000                 PETYA
    1002                 1001                 SASHA
      1003                 1002                 PASHA
2000                                      MISHA
  2001                 2000                 MASHA
    2002                 2001                 PASHA
      2003                 2002                 DASHA
        2004                 2003                 GOSHA
          2005                 2004                 KESHA

10 rows selected.

SQL>



Поскольку таблица не может иметь больше 1000 полей иерархия из плоской таблицы не может иметь более 1000 уровней. Так-что HIERARCHY_ID * 1000 до HIERARCHY_ID * 1000 + 999 покрывает все возможные уровни и гарантирует каждый раз при insert ID/PID не будут меняться.

SY.
...
Рейтинг: 0 / 0
20.12.2021, 01:56
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121520
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Ну а для оптимизации я бы добавил поле версия в обе таблицы:

Код: plsql
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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
DROP SEQUENCE HIERARCHY_SEQ
/
DROP SEQUENCE VERSION_SEQ
/
DROP TABLE HIERARCHICAL PURGE
/
DROP TABLE FLAT_HIERARCHY PURGE
/
CREATE SEQUENCE HIERARCHY_SEQ
/
CREATE SEQUENCE VERSION_SEQ
/
CREATE TABLE FLAT_HIERARCHY(
                            HIERARCHY_ID NUMBER NOT NULL,
                            VERSION       NUMBER NOT NULL,
                            NAME1         VARCHAR2(10),
                            NAME2         VARCHAR2(10),
                            NAME3         VARCHAR2(10),
                            NAME4         VARCHAR2(10),
                            NAME5         VARCHAR2(10),
                            NAME6         VARCHAR2(10)
                           )
/
ALTER TABLE FLAT_HIERARCHY
  ADD CONSTRAINT FLAT_HIERARCHY_PK
    PRIMARY KEY(HIERARCHY_ID)
/
ALTER TABLE FLAT_HIERARCHY
  ADD CONSTRAINT FLAT_HIERARCHY_UK1
    UNIQUE(HIERARCHY_ID,VERSION)
/
INSERT
  INTO FLAT_HIERARCHY
  VALUES(
         HIERARCHY_SEQ.NEXTVAL,
         VERSION_SEQ.NEXTVAL,
         'VANIA',
         'PETYA',
         'SASHA',
         'PASHA',
         NULL,
         NULL
        )
/
INSERT
  INTO FLAT_HIERARCHY
  VALUES(
         HIERARCHY_SEQ.NEXTVAL,
         VERSION_SEQ.NEXTVAL,
         'MISHA',
         'MASHA',
         'PASHA',
         'DASHA',
         'GOSHA',
         'KESHA'
        )
/
CREATE TABLE HIERARCHICAL(
                          HIERARCHY_ID NUMBER NOT NULL,
                          VERSION      NUMBER NOT NULL,
                          ID           NUMBER(10) NOT NULL,
                          PID          NUMBER,
                          NAME         VARCHAR2(250) NOT NULL,
                          CONSTRAINT PK_HIERARCHICAL PRIMARY KEY(ID),
                          CONSTRAINT FK1_HIERARCHICAL FOREIGN KEY(PID) REFERENCES HIERARCHICAL(ID),
                          CONSTRAINT FK2_HIERARCHICAL FOREIGN KEY(HIERARCHY_ID,VERSION)
                            REFERENCES FLAT_HIERARCHY(HIERARCHY_ID,VERSION)
                         )
/
INSERT
  INTO HIERARCHICAL
  SELECT  T.HIERARCHY_ID,
          T.VERSION,
          T.HIERARCHY_ID * 1000 + L.LVL - 1 ID,
          CASE
            WHEN L.LVL > 1 THEN T.HIERARCHY_ID * 1000 + L.LVL - 2
          END PID,
          L.NAME
    FROM  FLAT_HIERARCHY T,
          LATERAL(
                  SELECT  LEVEL LVL,
                          CASE LEVEL
                            WHEN 1 THEN T.NAME1
                            WHEN 2 THEN T.NAME2
                            WHEN 3 THEN T.NAME3
                            WHEN 4 THEN T.NAME4
                            WHEN 5 THEN T.NAME5
                            ELSE NAME6
                          END NAME
                    FROM  DUAL
                    CONNECT BY LEVEL <= 6
                 ) L
    WHERE NAME IS NOT NULL
/

10 rows created.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  HIERARCHY_ID,
  2          VERSION,
  3          LPAD(' ',2 * (LEVEL - 1)) || ID ID,
  4          LPAD(' ',2 * (LEVEL - 1)) || PID PID,
  5          LPAD(' ',2 * (LEVEL - 1)) || NAME NAME
  6    FROM  HIERARCHICAL
  7    START WITH PID IS NULL
  8    CONNECT BY PID = PRIOR ID
  9  /

HIERARCHY_ID    VERSION ID                   PID                  NAME
------------ ---------- -------------------- -------------------- --------------------
           1          1 1000                                      VANIA
           1          1   1001                 1000                 PETYA
           1          1     1002                 1001                 SASHA
           1          1       1003                 1002                 PASHA
           2          2 2000                                      MISHA
           2          2   2001                 2000                 MASHA
           2          2     2002                 2001                 PASHA
           2          2       2003                 2002                 DASHA
           2          2         2004                 2003                 GOSHA
           2          2           2005                 2004                 KESHA

10 rows selected.

SQL>




Добавим новую иерархию и изменим существующую:

Код: plsql
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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
INSERT
  INTO FLAT_HIERARCHY
  VALUES(
         HIERARCHY_SEQ.NEXTVAL,
         VERSION_SEQ.NEXTVAL,
         'KOLYA',
         'POLYA',
         'SONYA',
         'MITYA',
         'VITYA',
         NULL
        )
/
DELETE HIERARCHICAL
  WHERE HIERARCHY_ID = 2
/
UPDATE FLAT_HIERARCHY
   SET VERSION = VERSION_SEQ.NEXTVAL,
       NAME3 = 'ANYA',
       NAME5 = 'MANYA'
 WHERE HIERARCHY_ID = 2
/
SELECT  *
  FROM  FLAT_HIERARCHY
/

HIERARCHY_ID    VERSION NAME1      NAME2      NAME3      NAME4      NAME5      NAME6
------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
           1          1 VANIA      PETYA      SASHA      PASHA
           2          8 MISHA      MASHA      ANYA       DASHA      MANYA      KESHA
           5          7 KOLYA      POLYA      SONYA      MITYA      VITYA

INSERT
  INTO HIERARCHICAL
  SELECT  T.HIERARCHY_ID,
          T.VERSION,
          T.HIERARCHY_ID * 1000 + L.LVL - 1 ID,
          CASE
            WHEN L.LVL > 1 THEN T.HIERARCHY_ID * 1000 + L.LVL - 2
          END PID,
          L.NAME
    FROM  FLAT_HIERARCHY T,
          LATERAL(
                  SELECT  LEVEL LVL,
                          CASE LEVEL
                            WHEN 1 THEN T.NAME1
                            WHEN 2 THEN T.NAME2
                            WHEN 3 THEN T.NAME3
                            WHEN 4 THEN T.NAME4
                            WHEN 5 THEN T.NAME5
                            ELSE NAME6
                          END NAME
                    FROM  DUAL
                    CONNECT BY LEVEL <= 6
                 ) L
    WHERE NAME IS NOT NULL
      AND T.HIERARCHY_ID NOT IN (
                                 SELECT  H.HIERARCHY_ID
                                   FROM  HIERARCHICAL H
                                )
/

11 rows created.

SQL> SELECT  HIERARCHY_ID,
  2          VERSION,
  3          LPAD(' ',2 * (LEVEL - 1)) || ID ID,
  4          LPAD(' ',2 * (LEVEL - 1)) || PID PID,
  5          LPAD(' ',2 * (LEVEL - 1)) || NAME NAME
  6    FROM  HIERARCHICAL
  7    START WITH PID IS NULL
  8    CONNECT BY PID = PRIOR ID
  9  /


HIERARCHY_ID    VERSION ID                   PID                  NAME
------------ ---------- -------------------- -------------------- --------------------
           1          1 1000                                      VANIA
           1          1   1001                 1000                 PETYA
           1          1     1002                 1001                 SASHA
           1          1       1003                 1002                 PASHA
           2          4 2000                                      MISHA
           2          4   2001                 2000                 MASHA
           2          4     2002                 2001                 ANYA
           2          4       2003                 2002                 DASHA
           2          4         2004                 2003                 MANYA
           2          4           2005                 2004                 KESHA
           3          3 3000                                      KOLYA
           3          3   3001                 3000                 POLYA
           3          3     3002                 3001                 SONYA
           3          3       3003                 3002                 MITYA
           3          3         3004                 3003                 VITYA

15 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
20.12.2021, 03:02
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121523
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
SergiiW,

А зачем вообще все это нужно? Вы же понимаете, что хоть плоская таблица и менее "гибкая", но более эффективная для получения родительских и дочерних узлов?
Ну, и может проще вьюху тогда уж сделать?
...
Рейтинг: 0 / 0
20.12.2021, 03:38
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121544
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Спасибо всем за ответы! Не ожидал такого участия. :) Буду читать и анализировать.
SY
Не уверен что понял. Понял так: Name{N} пустой значит Name{N + 1} пустой и Name{N + 2} пустой и.т.д.

Например если Name3 пусто, то Name4 будет связано с родителем Name2.
Но нужно признать, что, на сегодня, только одно поле Name3 может быть пустое. Все остальные поля должны быть заполнены.
Есть другие нюанси, которые я упустил. :(
Количество записей 1-2 млн.
Обновлять нужно каждый день.
Sayan MalakshinovНу, и может проще вьюху тогда уж сделать?
Нужно работать с деревом, а вьюха не будет достаточно быстрой для отображения в интерфейсе. По крайней мере, у меня ничего не получилось.
...
Рейтинг: 0 / 0
20.12.2021, 08:04
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121547
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
SergiiW,

Иерархическая таблица обычно в разы, а иногда и на порядки хуже для работы с деревьями, например при указанной высоте 6 для получения предков получится минимум 12 логических чтений (6 раз индекс + 6 к таблице), а с плоской всего 2.
...
Рейтинг: 0 / 0
20.12.2021, 08:28
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121548
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
При большом объёме обычно как раз оптимизируют путем создания плоских мвьюх по иерархическим таблицам
...
Рейтинг: 0 / 0
20.12.2021, 08:30
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121603
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Sayan Malakshinov,

Мне нужно идти от родителя. Для иерархический это просто.
Код: plsql
1.
SELECT * from HIERARCHICAL WHERE PID = :PID

это, обычно, 5-20 записей.
Для плоской нужно указывать всех родителей.
Код: plsql
1.
SELECT * FROM table WHERE Name1 =:Name1 and Name2=:Name2 AND ...
...
Рейтинг: 0 / 0
20.12.2021, 11:50
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121669
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
SergiiWДля плоской нужно указывать всех родителей.

У вас реально задача на поиск всех поддеревьев с заданным именем?

Даже с указанием всех родителей и индексом на каждое поле это всё равно
получается быстрее и проще, чем в иерархии.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
20.12.2021, 14:11
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121684
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
SergiiW
Sayan Malakshinov,

Мне нужно идти от родителя. Для иерархический это просто.
Код: plsql
1.
SELECT * from HIERARCHICAL WHERE PID = :PID


это, обычно, 5-20 записей.
Для плоской нужно указывать всех родителей.
Код: plsql
1.
SELECT * FROM table WHERE Name1 =:Name1 and Name2=:Name2 AND ...

у вас какая-то странная и непонятная плоская таблица... обычно делается что-то типа такого:
ID, LVL, NAME, LVL0, LVL1, LVL2, LVL3, LVL4, LVL5, LVL6, LVL7, LVL8, LVL9
...
Рейтинг: 0 / 0
20.12.2021, 14:41
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121717
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Dimitry Sibiryakov
Даже с указанием всех родителей и индексом на каждое поле это всё равно
получается быстрее и проще, чем в иерархии.
Это утверждение для меня полностью непонятно. :(
Я даже возразить ничего не могу, так как для меня очевидно, что иерархическая таблица во много раз проще и быстрее. Для нее нужно указать только PID, а для плоской всех родителей в текстовом виде. Реально у меня быстродействие на несколько порядков больше.
...
Рейтинг: 0 / 0
20.12.2021, 16:07
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121718
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Sayan Malakshinov
у вас какая-то странная и непонятная плоская таблица
Какая есть. Другой нет и не предвидеться. :(
...
Рейтинг: 0 / 0
20.12.2021, 16:08
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121719
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
SergiiWЯ даже возразить ничего не могу, так как для меня очевидно, что иерархическая
таблица во много раз проще и быстрее.

А для некоторых очевидно, что солнце вращается вокруг плоской земли...
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
20.12.2021, 16:09
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121721
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Никанор Кузьмич
Я бы сделал примерно так: unpivot + rownumber(), потом отсортировать по какому-нибудь старому полю + NameN, rownumber() становится id, rownumber() предыдущей строки использовать как parent_id. Вроде относительно просто.
Спасибо, хорошая идея! Но есть но, мне нужны постоянные ID при обновлении таблицы. :( Если бы не постоянные ID - однозначно бы воспользовался идеей, мне она понравилась.
...
Рейтинг: 0 / 0
20.12.2021, 16:16
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121724
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Elic
RTFM MERGE
Как по мне два Insert и Delete проще для понимания и обслуживания чем один MERGE или я не понял.
...
Рейтинг: 0 / 0
20.12.2021, 16:24
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121726
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Никанор Кузьмич
 
Что-то типа такого нужно?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with t as (select 1 id, 'Vasya' name1, 'Petya' name2, '' name3 from dual union all
           select 2, 'Masha', 'Katya', 'Olya' from dual)
select id, lvl, val, new_pk, lag(new_pk, 1, null) over (partition by id order by id, lvl) parent_id
  from (select id, lvl, val, row_number() over (order by id, lvl) new_pk
          from (select id, lvl, val
                  from t
               unpivot (val for lvl in (name1 as 1, name2 as 2, name3 as 3))));

        ID        LVL VAL       NEW_PK  PARENT_ID
---------- ---------- ----- ---------- ----------
         1          1 Vasya          1           
         1          2 Petya          2          1
         2          1 Masha          3           
         2          2 Katya          4          3
         2          3 Olya           5          4

Еще раз спасибо, именно то что нужно было бы, если бы не не постоянные ID. Но идея великолепная!
...
Рейтинг: 0 / 0
20.12.2021, 16:35
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121752
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
SergiiW
Еще раз спасибо, именно то что нужно было бы, если бы не не постоянные ID. Но идея великолепная!


Код в моем примере дает постоянные ID и не требует unpivot, аналитику, итд.

SY.
...
Рейтинг: 0 / 0
20.12.2021, 18:17
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121767
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
SY
Код в моем примере дает постоянные ID и не требует unpivot, аналитику, итд.
Спасибо, я, пока, разбираюсь в Вашим кодом, моих познаний мало, чтобы его быстро осмыслить.
Но у меня есть сомнения в по поводу постоянности ID. Плоская таблица каждый день создается наново. Я там ничего хранить не могу, я только ее читаю как есть.
Насколько я понимаю, обеспечить постоянство ID можно только сравнивая плоскую таблицу с иерархической.
...
Рейтинг: 0 / 0
20.12.2021, 19:10
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121781
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Sayan Malakshinov
При большом объёме обычно как раз оптимизируют путем создания плоских мвьюх по иерархическим таблицам
Кстати, а возможен refresh complete on commit для таких мвьюх?
И меня не получалось.
...
Рейтинг: 0 / 0
20.12.2021, 21:02
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121809
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
SergiiW

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


Т.е. плоская таблица каждый день создается наново a иерархическая нет? Если да, то я бы вместо HIERARCHY_ID, VERSION как в моем примере добавил бы HIERARCHY_HASH в обе таблицы и заполнял это поле через DBMS_CRYPTO.HASH хешируя NAME1 || '|' || NAME || '|' || NAME3... ( если | может встречаться в NAME то замени | на символ который встречаться не может). Таким образом ты получишь постоянные ID для той-же самой иерархии.

SY.
...
Рейтинг: 0 / 0
21.12.2021, 00:34
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121812
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Elic
Sayan Malakshinov
При большом объёме обычно как раз оптимизируют путем создания плоских мвьюх по иерархическим таблицам
Кстати, а возможен refresh complete on commit для таких мвьюх?
И меня не получалось.
к сожалению, нет, тк on commit требует выполнения условий для fast refreshable, а connect-by в этом списке ограничений
...
Рейтинг: 0 / 0
21.12.2021, 02:04
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121817
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Sayan Malakshinov
к сожалению, нет, тк on commit требует выполнения условий для fast refreshable, а connect-by в этом списке ограничений


Почему-же? Простое в лоб MV дает и fast refresh и on commit. Все что нам нужно знать это max возможную глубину иерархии. Например 4 для таблицы EMP:

Код: plsql
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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
CREATE MATERIALIZED VIEW LOG
  ON EMP
  WITH PRIMARY KEY,
       ROWID,
       SEQUENCE(
                ENAME,
                JOB,
                MGR,
                HIREDATE,
                SAL,
                COMM,
                DEPTNO
               )
       INCLUDING NEW VALUES
/
CREATE MATERIALIZED VIEW EMP_MV
  REFRESH FAST
  ON COMMIT
  AS
    SELECT  L1.EMPNO EMPNO_L1,
            L2.EMPNO EMPNO_L2,
            L3.EMPNO EMPNO_L3,
            L4.EMPNO EMPNO_L4,
            L1.ROWID L1_ROWID,
            L2.ROWID L2_ROWID,
            L3.ROWID L3_ROWID,
            L4.ROWID L4_ROWID
      FROM  EMP L1,
            EMP L2,
            EMP L3,
            EMP L4
      WHERE L1.MGR IS NULL
        AND L2.MGR(+) = L1.EMPNO
        AND L3.MGR(+) = L2.EMPNO
        AND L4.MGR(+) = L3.EMPNO
/

Materialized view created.

SQL> SELECT  EMPNO_L1,
  2          EMPNO_L2,
  3          EMPNO_L3,
  4          EMPNO_L4
  5    FROM  EMP_MV
  6  /

  EMPNO_L1   EMPNO_L2   EMPNO_L3   EMPNO_L4
---------- ---------- ---------- ----------
      7839       7566       7902       7369
      7839       7566       7788       7876
      7839       7698       7521
      7839       7698       7499
      7839       7698       7900
      7839       7782       7934
      7839       7698       7654
      7839       7698       7844

8 rows selected.

SQL> UPDATE EMP
  2     SET EMPNO = 8888
  3   WHERE EMPNO = 7698
  4  /

1 row updated.

SQL> UPDATE EMP
  2     SET MGR = 8888
  3   WHERE MGR = 7698
  4  /

5 rows updated.

SQL> INSERT
  2    INTO EMP
  3    VALUES(9999,'BAKER','CLERK',7844,TRUNC(SYSDATE),1234,NULL,30)
  4  /

1 row created.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  EMPNO_L1,
  2          EMPNO_L2,
  3          EMPNO_L3,
  4          EMPNO_L4
  5    FROM  EMP_MV
  6  /

  EMPNO_L1   EMPNO_L2   EMPNO_L3   EMPNO_L4
---------- ---------- ---------- ----------
      7839       7566       7902       7369
      7839       7566       7788       7876
      7839       7782       7934
      7839       8888       7844       9999
      7839       8888       7521
      7839       8888       7499
      7839       8888       7900
      7839       8888       7654

8 rows selected.

SQL> UPDATE EMP
  2     SET EMPNO = 7698
  3   WHERE EMPNO = 8888
  4  /

1 row updated.

SQL> UPDATE EMP
  2     SET MGR = 7698
  3   WHERE MGR = 8888
  4  /

5 rows updated.

SQL> DELETE EMP
  2   WHERE EMPNO = 9999
  3  /

1 row deleted.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  EMPNO_L1,
  2          EMPNO_L2,
  3          EMPNO_L3,
  4          EMPNO_L4
  5    FROM  EMP_MV
  6  /

  EMPNO_L1   EMPNO_L2   EMPNO_L3   EMPNO_L4
---------- ---------- ---------- ----------
      7839       7566       7902       7369
      7839       7566       7788       7876
      7839       7782       7934
      7839       7698       7521
      7839       7698       7499
      7839       7698       7900
      7839       7698       7654
      7839       7698       7844

8 rows selected.

SQL>



Конечно self-join таблицы max возможная глубина иерархия раз не шашечки, но едет.

SY.
...
Рейтинг: 0 / 0
21.12.2021, 03:11
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121832
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SY
Все что нам нужно знать это max возможную глубину иерархии.
Ну да, "всего лишь".
Более того, connect by размножает строки, например для получения прямых связей:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
select
    connect_by_root id as id
  , id as any_parent_id 
  from tbl
  where level >= 2
  connect by id = prior parent_id
;

SY
Конечно self-join таблицы max возможная глубина иерархия раз не шашечки, но едет.
self-join-ом можно сэмулировать ограниченное подмножество иерархических запросов.
...
Рейтинг: 0 / 0
21.12.2021, 07:46
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121848
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
SY
Почему-же? Простое в лоб MV дает и fast refresh и on commit. Все что нам нужно знать это max возможную глубину иерархии. Например 4 для таблицы EMP:
ну, вообще, я говорил именно про рекурсивные запросы (что connect-by, что recursive subq factoring в списке ограничений). Да, конечно, способ с разворачиванием в джойны хорошо распространен, но ты сделал немного не так: в твоем запросе фактически идет схлопывание, а-ля connect_by_isleaf=1, что обычно добавляет сложности и далеко не везде зайдет.
Обычно делается что-то типа такого:
 
Код: plsql
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);


Что это дает:
  • кол-во строк у обоих таблиц одинаково (легкая валидация, простые джойны при необходимости);
  • каждый элемент изначальной таблицы имеет свою запись в развернутой, со своей доп.инфой типа уровня иерархии элемента и прочего, при необходимости;
  • констрейнт дает возможность контролировать глубину иерархии, чтобы не случилось внезапного расхождения данных.
 
Код: plsql
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;
/


мы получим:
Код: plsql
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.


А при попытке вставить элемент более глубокого уровня получим ошибку:
Код: plsql
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
...
Рейтинг: 0 / 0
21.12.2021, 09:33
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121884
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
SY
Т.е. плоская таблица каждый день создается наново a иерархическая нет?
Да.
SY
DBMS_CRYPTO.HASH хешируя NAME1 || '|' || NAME || '|' || NAME3...

Думаю, это ничего не даст. Мне нужно не ID всей строки, а ID Name1, ID - Name2 (с учетом родителя), ...
...
Рейтинг: 0 / 0
21.12.2021, 12:35
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121889
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SergiiW
Мне нужно не ID всей строки, а ID Name1, ID - Name2 (с учетом родителя), ...


Вы б привели пример Вашей плоской таблицы,
напр для emp.ename, плоская таблица выглядит ...

зы
если "ID" символьное, не поможет

....
stax
...
Рейтинг: 0 / 0
21.12.2021, 12:55
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121892
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Stax
Вы б привели пример Вашей плоской таблицы,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT 'VANIA' NAME1,
       'PETYA' NAME2,
       'SASHA' NAME3,
       'PASHA' NAME4,
       'VOVA'  NAME5,
       'VIKA'  NAME6
FROM  DUAL
UNION ALL
SELECT 'MISHA' NAME1,
       'MASHA' NAME2,
       'PASHA' NAME3,
       NULL    NAME4,
       'GOSHA' NAME5, -- Родитель NAME3 (PASHA)
       'KESHA' NAME6
 FROM  DUAL
...
Рейтинг: 0 / 0
21.12.2021, 13:10
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121906
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
Еще раз спасибо всем за помощь!
Свою задачу с вашей помощью я решил.
Кратко:
1. Сделал View V_FH на основе запроса
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT 
  p.Name1, P.Name2, P.Name3, P.Name4, P.Name5, P.Name6,
  a1.PID PID1, a1.ID ID1,
  a2.PID PID2, a2.ID ID2,
  a3.PID PID3, a3.ID ID3,
  a4.PID PID4, a4.ID ID4,
  a5.PID PID5, a5.ID ID5,
  a6.PID PID6, a6.ID ID6
from FLAT p 
  LEFT JOIN HIERARCHY A1 ON A1.PID IS null AND A1.NAME = P.Name1
  LEFT JOIN HIERARCHY A2 ON A2.PID = A1.ID AND A2.NAME = P.Name2
  LEFT JOIN HIERARCHY A3 ON A3.PID = A2.ID AND A3.NAME = P.Name3
  LEFT JOIN HIERARCHY A4 ON A4.PID = A3.ID AND A4.NAME = p.Name4
  LEFT JOIN HIERARCHY A5 ON A5.PID = NVL(A4.ID, A3.ID) AND A5.NAME = p.Name5
  LEFT JOIN HIERARCHY A6 ON A6.PID = A5.ID AND A6.NAME = p.Name6


2. Удаление через временную таблицу
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
INSERT INTO TMP (ID)
SELECT DISTINCT ID1 FROM V_FH UNION ALL
SELECT DISTINCT ID2 FROM V_FH UNION ALL
SELECT DISTINCT ID3 FROM V_FH UNION ALL
SELECT DISTINCT ID4 FROM V_FH UNION ALL
SELECT DISTINCT ID5 FROM V_FH UNION ALL
SELECT DISTINCT ID6 FROM V_FH;
DELETE FROM HIERARCHY H WHERE NOT EXISTS(SELECT 1 FROM TMP t WHERE T.ID = H.ID);


3. Обновление для каждого уровня отдельно
3.1
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
INSERT INTO HIERARCHY (id, NAME, PID)
SELECT
  i.i + ROWNUM ID, Name, PID
FROM (SELECT NVL(MAX(ID), 0) i FROM HIERARCHY) i, 
(SELECT
  Name1 Name, PID1 PID
from V_FH
WHERE ID1 IS NULL
GROUP BY Name1, PID1);


3.2
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
INSERT INTO HIERARCHY (id, NAME, PID)
SELECT
  i.i + ROWNUM ID, Name, PID
FROM (SELECT NVL(MAX(ID), 0) i FROM HIERARCHY) i, 
(SELECT
  Name2 Name, PID2 PID
from V_FH
WHERE ID1 IS NOT NULL AND ID2 IS NULL
GROUP BY Name2, PID2);


3.3 ...

Написал пост и понял, что PID в View не нужен. Можно без этого поля обойтись.
...
Рейтинг: 0 / 0
21.12.2021, 13:37
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121940
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
SergiiW,

Создаем таблицы:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
DROP TABLE HIERARCHICAL PURGE
/
DROP TABLE FLAT_HIERARCHY PURGE
/
CREATE TABLE FLAT_HIERARCHY(
                            NAME1      VARCHAR2(10),
                            NAME2      VARCHAR2(10),
                            NAME3      VARCHAR2(10),
                            NAME4      VARCHAR2(10),
                            NAME5      VARCHAR2(10),
                            NAME6      VARCHAR2(10)
                           )
/
CREATE TABLE HIERARCHICAL(
                          HIER_HASH  RAW(2000),
                          ID         NUMBER,
                          PID        NUMBER,
                          NAME       VARCHAR2(10)
                         )
/



Заполняем плоскую:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
INSERT
  INTO FLAT_HIERARCHY
     SELECT  'VANIA' NAME1,
             'PETYA' NAME2,
             'SASHA' NAME3,
             'PASHA' NAME4,
             'VOVA'  NAME5,
             'VIKA'  NAME6
       FROM  DUAL
    UNION ALL
     SELECT  'MISHA' NAME1,
             'MASHA' NAME2,
             'PASHA' NAME3,
             NULL    NAME4,
            'GOSHA' NAME5, -- Родитель NAME3 (PASHA)
            'KESHA' NAME6
      FROM  DUAL
/
COMMIT
/



В иерархию добавляем только новое:

Код: plsql
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.
39.
40.
41.
42.
43.
MERGE
  INTO HIERARCHICAL T
  USING (
         WITH F AS (
                    SELECT  DBMS_CRYPTO.HASH(TO_CLOB(NAME1) || '|' || NAME2 || '|' || NAME3 || '|' || NAME4 || '|' || NAME5 || '|' || NAME6,3) HIER_HASH,
                            ID,
                            CASE ID
                              WHEN 1 THEN NAME1
                              WHEN 2 THEN NAME2
                              WHEN 3 THEN NAME3
                              WHEN 4 THEN NAME4
                              WHEN 5 THEN NAME5
                              ELSE NAME6
                            END NAME
                      FROM  FLAT_HIERARCHY,
                            LATERAL(
                                    SELECT  LEVEL ID
                                      FROM  DUAL
                                      CONNECT BY LEVEL <= 6
                                   )
                   )
         SELECT  HIER_HASH,
                 ID,
                 LAG(ID) OVER(PARTITION BY HIER_HASH ORDER BY ID) PID,
                 NAME
           FROM  F
           WHERE NAME IS NOT NULL
        ) S
  ON (
      T.HIER_HASH = S.HIER_HASH
     )
  WHEN NOT MATCHED
    THEN
      INSERT
        VALUES(
               S.HIER_HASH,
               S.ID,
               S.PID,
               S.NAME
              )
/
COMMIT
/



Имеем:

Код: plsql
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.
COLUMN ID_INDENTED FORMAT A12
SELECT  RPAD(' ',2 * (LEVEL - 1)) || ID ID_INDENTED,
        PID,
        NAME
  FROM  HIERARCHICAL
  START WITH PID IS NULL
  CONNECT BY HIER_HASH = PRIOR HIER_HASH
         AND PID = PRIOR ID
/

ID_INDENTED         PID NAME
------------ ---------- ----------
1                       VANIA
  2                   1 PETYA
    3                 2 SASHA
      4               3 PASHA
        5             4 VOVA
          6           5 VIKA
1                       MISHA
  2                   1 MASHA
    3                 2 PASHA
      5               3 GOSHA -- Родитель ID=3 (PASHA)
        6             5 KESHA

11 rows selected.

SQL>



Удаляем все из плоской и заполняем тем-же плюс новая иерархия:

Код: plsql
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.
TRUNCATE TABLE FLAT_HIERARCHY
/
INSERT
  INTO FLAT_HIERARCHY
     SELECT  'VANIA' NAME1,
             'PETYA' NAME2,
             'SASHA' NAME3,
             'PASHA' NAME4,
             'VOVA'  NAME5,
             'VIKA'  NAME6
       FROM  DUAL
    UNION ALL
     SELECT  'MISHA' NAME1,
             'MASHA' NAME2,
             'PASHA' NAME3,
             NULL    NAME4,
            'GOSHA' NAME5, -- Родитель NAME3 (PASHA)
            'KESHA' NAME6
      FROM  DUAL
    UNION ALL
     SELECT  'DAVID'   NAME1,
             'MICHAEL' NAME2,
             'RYAN'    NAME3,
             'KEVIN'   NAME4,
             NULL      NAME5,
             NULL      NAME6
      FROM  DUAL
/
COMMIT
/



Выполняем NERGE:

Код: plsql
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.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
MERGE
  INTO HIERARCHICAL T
  USING (
         WITH F AS (
                    SELECT  DBMS_CRYPTO.HASH(TO_CLOB(NAME1) || '|' || NAME2 || '|' || NAME3 || '|' || NAME4 || '|' || NAME5 || '|' || NAME6,3) HIER_HASH,
                            ID,
                            CASE ID
                              WHEN 1 THEN NAME1
                              WHEN 2 THEN NAME2
                              WHEN 3 THEN NAME3
                              WHEN 4 THEN NAME4
                              WHEN 5 THEN NAME5
                              ELSE NAME6
                            END NAME
                      FROM  FLAT_HIERARCHY,
                            LATERAL(
                                    SELECT  LEVEL ID
                                      FROM  DUAL
                                      CONNECT BY LEVEL <= 6
                                   )
                   )
         SELECT  HIER_HASH,
                 ID,
                 LAG(ID) OVER(PARTITION BY HIER_HASH ORDER BY ID) PID,
                 NAME
           FROM  F
           WHERE NAME IS NOT NULL
        ) S
  ON (
      T.HIER_HASH = S.HIER_HASH
     )
  WHEN NOT MATCHED
    THEN
      INSERT
        VALUES(
               S.HIER_HASH,
               S.ID,
               S.PID,
               S.NAME
              )
/

4 rows merged. -- добавилась только новая иерархия

SQL> COMMIT
  2  /

Commit complete.

SQL>



Имеем:

Код: plsql
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.
SELECT  RPAD(' ',2 * (LEVEL - 1)) || ID ID_INDENTED,
        PID,
        NAME
  FROM  HIERARCHICAL
  START WITH PID IS NULL
  CONNECT BY HIER_HASH = PRIOR HIER_HASH
         AND PID = PRIOR ID
/

ID_INDENTED         PID NAME
------------ ---------- ----------
1                       VANIA
  2                   1 PETYA
    3                 2 SASHA
      4               3 PASHA
        5             4 VOVA
          6           5 VIKA
1                       MISHA
  2                   1 MASHA
    3                 2 PASHA
      5               3 GOSHA
        6             5 KESHA
1                       DAVID
  2                   1 MICHAEL
    3                 2 RYAN
      4               3 KEVIN

15 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
21.12.2021, 15:26
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40121982
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
SY,

Спасибо! Интересное решение, но есть несколько но.
1. Оно более сложно для восприятия, понимания, по крайней мере для меня, и, соответственно, мне или другим будет сложнее его поддерживать. Даже несмотря на то, что оно может быть более универсальное и содержать меньше кода. Возможно, это субъективное мнение потому, что не я создавал этот код.
2. Отсутствует удаление записей, которых нет в плоской таблице.
3. ID не уникальное. То есть, выборки по ID или по PID будут проблематичны.
...
Рейтинг: 0 / 0
21.12.2021, 17:44
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40122011
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
SergiiW
SY,

Спасибо! Интересное решение, но есть несколько но.
1. Оно более сложно для восприятия, понимания, по крайней мере для меня, и, соответственно, мне или другим будет сложнее его поддерживать. Даже несмотря на то, что оно может быть более универсальное и содержать меньше кода. Возможно, это субъективное мнение потому, что не я создавал этот код.
2. Отсутствует удаление записей, которых нет в плоской таблице.
3. ID не уникальное. То есть, выборки по ID или по PID будут проблематичны.


1. Все что используется в решении это хеш имен для проверки есть ли в HIERARCHICAL такая иерархия и самый обыкновенный MERGE так-что даже если у тебя с этим сложности то другим не будет сложнее его поддерживать разве-что они тоже entry level.
2. Ну так добавь это в MERGE или просто выполни

Код: plsql
1.
2.
3.
4.
5.
6.
DELETE HIERARCHICAL
 WHERE HIER_HASH NOT IN (
                         SELECT  DBMS_CRYPTO.HASH(TO_CLOB(NAME1) || '|' || NAME2 || '|' || NAME3 || '|' || NAME4 || '|' || NAME5 || '|' || NAME6,3)
                           FROM  FLAT_HIERARCHY
                        )
/



3. Все ID/PID уникальные в пределах иерархии. Сколько бы раз мы бы ни создавали иерархию из той-же плоской 'MISHA', 'MASHA', 'PASHA', NULL, 'GOSHA','KESHA' всегда MISHA получит ID = 1 а GOSHA получит ID = 5. Кроме того ID/PID это artificial key и привязка к нему превращает их в natural keys. Зачем привязываться к ID/PID если у нас есть имена? Приведи пример (словами) что ищется в таблице HIERARCHICAL. Например, найти непосредственных родителей Васи во всех иерархиях.

SY.
...
Рейтинг: 0 / 0
21.12.2021, 19:50
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40122029
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergiiW
Гость
SY,


ORA-00904: "DBMS_CRYPTO"."HASH": invalid identifier
...
Рейтинг: 0 / 0
21.12.2021, 21:52
| Ответить | Цитировать | Написать  
Плоскую таблицу преобразовать в Иерархическую
    #40122178
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
SergiiW
SY,


ORA-00904: "DBMS_CRYPTO"."HASH": invalid identifier


Ну так попроси DBA дать тебе EXECUTE на DBMS_CRYPTO.

SY.
...
Рейтинг: 0 / 0
22.12.2021, 15:49
| Ответить | Цитировать | Написать  
42 сообщений из 42, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Плоскую таблицу преобразовать в Иерархическую
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1879660]:
 0ms
get settings:
 17ms
get forum list:
 5ms
check forum access:
 1ms
check topic access:
 1ms
track hit:
 112ms
get topic data:
 2ms
get forum data:
 0ms
get page messages:
 780ms
get tp. blocked users:
 0ms
others: 276ms
total:  1194ms
созд. / загр.: 1194ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]