Expand an experiment on time-related trees

-- CREATE a recursive tree History TABLE and generate the data create table TREE_HIS (id number, C_CHILD VARCHAR2 (32 BYTE), C_NAME VARCHAR2 (100 BYTE), P_ID NUMBER, EFF_DATE DATE, EXP_DATE DATE); create index IDX2 ON TREE_HIS (P_ID); create unique index IDX3 ON TREE_HIS (C_CHILD, EXP_DATE); create unique index TREE_HIS_PK ON TREE_HIS (ID ); alter table TREE_HIS ADD (CONSTRAINT TREE_HIS_PK primary key (ID); alter table TREE_HIS ADD (CONSTRAINT TREE_HIS_R01 foreign key (P_ID) REFERENCES TREE_HIS (ID); Insert into TREE_HIS (ID, c_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (44, 'x', 'node x', NULL, TO_DATE ('2014/1/2/0 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/12/31 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (45, 'A', 'node A', 44, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/12/31 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (46, 'B', 'node B ', 45, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/12/31 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (47, 'E', 'node E', 46, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/12/31 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (48, 'F', 'node F', 46, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/12/31 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (49, 'D', 'node d', 45, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/28 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (50, 'J', 'node J', 49, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/28 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (51, 'k', 'node K', 49, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/28 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (52, 'y', 'node y', 44, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/12/31 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (53, 'C', 'node C', 52, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/12/31 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (54, 'G', 'node G', 53, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/12/31 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (55, 'h', 'node H', 53, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/12/31 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (56, 'I', 'node I ', 53, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/12/31 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (0, 'A', 'node A', NULL, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (1, 'B', 'node B ', 0, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (2, 'E', 'node E', 1, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (3, 'F', 'node F', 1, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (4, 'C', 'node C', 0, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (5, 'G', 'node G', 4, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (6, 'h', 'node H', 4, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (7, 'I', 'node I ', 4, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (8, 'D', 'node d', 0, TO_DATE ('2014/1/2/0 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (9, 'J', 'node J', 8, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (10, 'k', 'node K', 8, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/23 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (11, 'A', 'node a1', NULL, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (12, 'B', 'node B1 ', 11, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (13, 'E', 'node E1 ', 12, TO_DATE ('2014/1/2/0 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (14, 'F', 'node f1', 12, TO_DATE ('2014/1/0/16 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (15, 'C', 'node c1', 11, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (16, 'G', 'node g1', 15, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (17, 'h', 'node H1 ', 15, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (18, 'I', 'node i1', 15, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (19, 'D', 'node 1', 11, TO_DATE ('2014/1/2/0 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (20, 'J', 'node j1', 19, TO_DATE ('2016/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (21, 'k', 'node k1', 19, TO_DATE ('2014/1/0/16 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/24 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (22, 'A', 'node A2 ', NULL, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (23, 'B', 'node B1 ', 22, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/25 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (24, 'E', 'node e2', 23, TO_DATE ('2014/1/2 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/25 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (25, 'F', 'node f1', 23, TO_DATE ('2014/1/0/16 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/25 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (26, 'C', 'node c2', 22, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/25 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (27, 'G', 'node g1', 26, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/25 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (28, 'h', 'node H2 ', 26, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/25 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (29, 'I', 'node i1', 26, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/25 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (30, 'D', 'node 1', 22, TO_DATE ('2014/1/2/0 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (31, 'J', 'node j1', 30, TO_DATE ('2016/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/26 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (32, 'k', 'node k2', 30, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (33, 'B', 'node b2', 22, TO_DATE ('2014/1/0/16 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/26 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (34, 'E', 'node e2', 33, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/26 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (35, 'F', 'node f1', 33, TO_DATE ('2014/1/0/16 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/26 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (36, 'C', 'node c3', 22, TO_DATE ('2014/1/2/0 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (37, 'G', 'node g1', 36, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (38, 'h', 'node H2 ', 36, TO_DATE ('2014/1/0/16 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (39, 'I', 'node i1', 36, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (40, 'B', 'node b3', 22, TO_DATE ('2014/1/0/16 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (41, 'E', 'node e2', 40, TO_DATE ('2017/24/60 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (42, 'F', 'node f1', 40, TO_DATE ('2014/1/0/16 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss ')); insert into TREE_HIS (ID, C_CHILD, C_NAME, P_ID, EFF_DATE, EXP_DATE) Values (43, 'J', 'node j1', 30, TO_DATE ('2014/1/0/16 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'), TO_DATE ('2016/06/27 00:00:00 ', 'Mm/DD/YYYY HH24: MI: ss'); COMMIT; -- create table TREE_EXPLODE (C_PARENT NUMBER, C_CHILD NUMBER, C_LEVEL INTEGER, C_DISTANCE INTEGER, C_BOTTOM CHAR (1 BYTE), C_SEQ VARCHAR2 (100 BYTE), EFF_DT DATE, EXP_DT DATE, C_NAME VARCHAR2 (100 BYTE), ROOT_NAME VARCHAR2 (100 BYTE); -- create or replace procedure using execute immediate 'truncate table tree_explode 'when creating a stored PROCEDURE '; FOR x IN (SELECT id, LEVEL c_level, c_name, eff_date, exp_date FROM tree_his start with p_id is null connect by prior id = p_id) loop insert into tree_explode (c_parent, c_child, c_level, c_distance, c_bottom, c_seq, eff_dt, exp_dt, c_name, root_name) SELECT CONNECT_BY_ROOT. id,. id, x. c_level, LEVEL, DECODE (CONNECT_BY_ISLEAF, 1, 'y', 'n'), (SELECT SYS_CONNECT_BY_PATH (c_name, '/') FROM tree_his WHERE id =. p_id start with p_id is null connect by prior id = p_Id), x. eff_date, x. exp_date,. c_name, x. c_name FROM tree_his a start with. id = x. id connect by prior. id =. p_id; end loop; COMMIT; END;/-- Test EXEC p_tree_his_explode; SELECT * FROM tree_explode;