How to adjust a hierarchical query in Oracle so then the parent and the id can come in sequence? I have a view with a hierarchical query like the print screen below. I'm trying to find a way of getting the ids and parent ids (columns cfg_info_id and cfg_parent) as sequential numbers. Is there a way of doing this just adjusting my sql? Or do I need to create a pipelined function to manipulate these values?



Here is the sql (simplified):

select 
       ci.CONFIG_ID, rownum, ci.CONFIG_INFO_ID as cfg_info_id,
       lpad(' ',(level-1)*3,' ')||ci.key as CFG_KEY,
       ci.value AS CFG_VALUE, ci.parent AS CFG_PARENT, 
       level AS CFG_LEVEL
from CONFIGS_INFO ci 
where config_id = 16
start with ci.parent is null
connect by prior ci.CONFIG_INFO_ID = ci.PARENT; 


I can use rownum to get the cfg_info_id as a sequence. But do I have some way of changing the cfg_parent accordingly?
Create sample data:

create table t1
(
  config_id number,
  config_info_id number,
  parent number
);

insert into t1 values (1, 1, null);
insert into t1 values (16, 3983, null);
insert into t1 values (16, 3984, null);
insert into t1 values (16, 3985, null);
insert into t1 values (16, 3986, 3985);
insert into t1 values (16, 3987, 3985);
insert into t1 values (16, 3988, null);
insert into t1 values (16, 3989, 3988);
insert into t1 values (16, 3990, 3988);
commit;


Generate new id values for CONFIG_INFO_ID where config_id = 16:

with new_ids as
(
  select rownum as new_id, config_info_id from
  (
    select config_info_id from t1
    where config_id = 16
    order by config_info_id
  )
)
select * from new_ids
;

    NEW_ID  CONFIG_INFO_ID
---------- ---------------
         1            3983
         2            3984
         3            3985
         4            3986
         5            3987
         6            3988
         7            3989
         8            3990


Replace the original CONFIG_INFO_ID and parent values with the generated values:

with new_ids as
(
  select rownum as new_id, config_info_id from
  (
    select config_info_id from t1
    where config_id = 16
    order by config_info_id
  )
)
select 
  ci.CONFIG_ID, 
  n1.new_id as cfg_info_id,
  n2.new_id  AS CFG_PARENT, 
  level AS CFG_LEVEL
from
  t1 ci
  join new_ids n1 on (ci.config_info_id = n1.config_info_id)
  left join new_ids n2 on (ci.parent = n2.config_info_id)
where config_id = 16
start with ci.parent is null
connect by prior ci.config_info_id = ci.parent
;

 CONFIG_ID  CFG_INFO_ID CFG_PARENT  CFG_LEVEL
---------- ------------ ---------- ----------
        16            1     (null)          1
        16            2     (null)          1
        16            3     (null)          1
        16            4          3          2
        16            5          3          2
        16            6     (null)          1
        16            7          6          2
        16            8          6          2