Oracle Hierarchical data copy

Hello all.

I'm attempting to do copy a Hierarchical dataset. As part of the copy, the tree_id (pk) to the record will change.

CREATE TABLE TREE
(

  TREE_ID           NUMBER(10)                  NOT NULL,
  PARENT_TREE_ID    NUMBER(10),
  PARENT_TREE_KEYS  VARCHAR2(255)               NOT NULL,
  DESCRIPTION       VARCHAR2(255)		NOT NULL,

Parent_tree_keys is a pipe delimited list of all the parent records.

insert into tree(1,null,'',ROOT)
insert into tree(2,1,'1','Level 1')
insert into tree(3,2,'1|2','Level 2')
insert into tree(4,2,'1|2','Level 2')

I can select this hierarchically quite nicely:

  1 SELECT LPAD(' ',2*level-2)||tree_id, description,   2 FROM TREE
  3 CONNECT BY PRIOR TREE_ID = PARENT_TREE_ID   gives me something like

TREE_ID DESCRIPTION PARENT_TREE_KEYS

-------  -----------  ----------------
1        ROOT         
  2      Level 1      1 
    3    Level 2      1|2
    4    Level 2      1|2

I need to copy this hierarchy to a different tree in the same table. The new tree would be identical, except the TREE_ID's would be different, which would require a rebuilding of PARENT_TREE_KEYS.

I'm currently doing this in java, and the process is taking about 3 minutes. Most of the time is spent managing a map of old PARENT_TREE_KEYS to new PARENT_TREE_KEYS. Before inserting a single record, I find it's parent in the cached map and grab the parents new datum.

Each tree has about 30,000 records, with 5-7 levels.

I know there's got to be a better way. A stored procedure seems ideal, but I'm not quite sure how to approach this. Theoretically, I can ditch the method storing a map of all old:new keys if I manage the hierarchy inteligently. Specifically, I think I can ditch the map if I always know what the values of the parent of the current record are.

insert into tree(
 select(
  SEQ.nextVal,
  PARENT(tree_id),
  concat(PARENT(parent_tree_keys),'|',PARENT(tree_id)),   tree.description
 from tree
)

I just can't figure out how to get the parent's data.

Can anyone pull up an example of a stored procedure that copies a hierarchical dataset?

Thanks,

_Am Received on Wed Apr 02 2003 - 00:53:26 CEST