Planization experiment of recursive tree

/*** Existing dimension table: dim_org -- organization, which is a recursive tree with historical information. Its primary key is the proxy key dim_person generated by the SEQ_DIM_ORG_PK sequence. It contains historical information, org_pk is associated with the proxy key of dim_org. Objective: To deliver data to the OLAP tool in the form of a flat tree. function: a fixed level-3 organization is defined according to dim_org. Each person is associated with a level-3 organization, dim_person.org_pk is supplemented by Level 3 below Level 3. Level 3 is classified as Level 3 ***/-- create table DIM_ORG (ORG_PK NUMBER, ORG_NAME VARCHAR2 (100 BYTE), P_ORG_PK NUMBER, EFF_DATE DATE, EXP_DATE DATE); alter table DIM_ORG ADD (primary key (ORG_PK); -- create table DIM_PERSON (PERSON_PK NUMBER, PERSON_NAME VARCHAR2 (30 BYTE), EFF_DATE DATE, EXP_DATE DATE, ORG_PK NUMBER); create index IDX_ORG_PK ON DIM_PERSON (ORG_PK); alter table DIM_PERSON ADD (primary key (PERSON_PK); alter table DIM_PERSON ADD (CONSTRAINT DIM_PERSON_R01 foreign key (ORG_PK) REFERENCES DIM_ORG (ORG_PK); -- create table tmp_org_level (org_pk number not null, org_1_pk NUMBER, org_1_name VARCHAR2 (100), org_2_pk NUMBER, org_2_name VARCHAR2 (100 ), org_3_pk NUMBER, org_3_name VARCHAR2 (100); create unique index partition ON tmp_org_level (org_pk); alter table tmp_org_level ADD (CONSTRAINT tmp_org_level_pk primary key (org_pk )); -- CREATE an association VIEW between personnel and organizational unit planialized tables and provide it to the OLAP tool create view v_tree_complanateAS SELECT person_pk, person_name, org_1_pk, org_1_name, org_2_pk, org_2_name, org_3_pk, org_3_name FROM dim_person JOIN tmp_org_level ON dim_person.org_pk = flatten; -- create or replace procedure during planialization -- generate planialized table data execute immediate 'truncate table tmp_org_level 'each time during ETL '; insert into tmp_org_level (org_pk, org_1_pk, org_1_name, primary, org_2_name, primary, org_3_name) SELECT org_pk, SUBSTR (c_pk_path, INSTR (c_pk_path, '/', 1, 1) + 1, INSTR (c_pk_path, '/', 1, 2)-INSTR (c_pk_path, '/', 1, 1)-1) org_1_pk, SUBSTR (c_name_path, INSTR (c_name_path, '/', 1, 1) + 1, INSTR (c_name_path, '/', 1, 2)-INSTR (c_name_path, '/', 1, 1)-1) org_1_name, SUBSTR (c_pk_path, INSTR (c_pk_path, '/', 1, 2) + 1, INSTR (c_pk_path, '/', 1, 3)-INSTR (c_pk_path, '/', 1, 2)-1) org_2_pk, SUBSTR (c_name_path, INSTR (c_name_path, '/', 1, 2) + 1, INSTR (c_name_path ,'/', 1, 3)-INSTR (c_name_path, '/', 1, 3)-1) org_2_name, SUBSTR (c_pk_path, INSTR (c_pk_path, '/', 1, 3) + 1, INSTR (c_pk_path, '/', 1, 4)-INSTR (c_pk_path, '/', 1, 3)-1) org_3_pk, SUBSTR (c_name_path, INSTR (c_name_path, '/', 1, 3) + 1, INSTR (c_name_path, '/', 1, 4)-INSTR (c_name_path, '/', 1, 3) -1) org_3_name FROM (SELECT org_pk, org_name, SYS_CONNECT_BY_PATH (org_pk, '/') | '/'c_pk_path, SYS_CONNECT_BY_PATH (org_name ,'/') | '/' c_name_path FROM dim_org start with p_Org_pk is null connect by prior org_pk = p_org_pk); -- Supplement Level 2 UPDATE tmp_org_level SET org_2_pk = SEQ_DIM_ORG_PK.NEXTVAL, org_2_name = org_1_name | 'department of the Department 'where org_2_pk is null; -- Supplement Level 3 UPDATE tmp_org_level SET org_3_pk = primary, org_3_name = org_2_name | 'department team 'where org_3_pk is null; END ;/