SQL & PL/SQL

Hi all,

I am new as an oracle developer.

I have a table(say table1) of hierarchical data with columns child_id and parent_id. The table has around 0.5 million records. There are around 0.2 million root level records are present.
I need to update some (the number variies on different conditions, might differ from 50K to 0.4 million) of the parent_ids from other table only if that do not cause any circular reference in data of table1.

I am using Oracle9i.

The approach I have tried is:

I copied all data from table1 to a work table table3. Update the parent_id on columns that matches the condition and updated a column source_table to table2.
I copied all the child_id from table2 that are to be updated into another table table4 and built tree for each of those childs. If it builds the tree successfully updated a column is_circular in the table3 to 0 and then deleted the child_id from table4.

This whole process needs to run recursively until no tree is built successfully in an iteration.

But this process is running slow and at the rate it is executing it would run for days for the 0.3 million records.
Please suggest a better way to do this.

The code I am using is :

INSERT /*+ append parallel (target,5) */
INTO table3 target
select /*+ parallel (src,5) */
child_id, parent_id, 'table1' source_table,null
from table1 src;

INSERT INTO table4
select distinct child_id, parent_id
from table2
where status_cd is null;
commit;


Update dtable3 a
set (parent_id,source_table) = (select parent_id,'table2' source_table
from table4 b
where b.child_id = a.child_id);

WHILE v_loop_limit<>v_new_count
LOOP
select count(1)
into v_loop_limit
from table4;

-+-----------------------------------------------------------
--| You need to actually traverse the tree in order to
--| detect circular dependencies.
+-----------------------------------------------------------


For i in 1 .. v_new_count
BEGIN

select child_id
into v_child_id from(
select child_id, row_number() over (order by child_id) pri from table4)
where pri = i;

SELECT COUNT (*) cnt
INTO v_cnt
FROM table3 dl
START WITH dl.child_id = v_child_id
CONNECT BY PRIOR dl.child_id = dl.parent_id ;


UPDATE table3SET is_circular = '0'
where child_id= v_child_id
and source_table = 'table2';

delete from table3
where child_id = v_child_id;

select count(1)
into v_new_count
from table4;

COMMIT;


EXCEPTION WHEN e_infinite_loop
THEN dbms_output.put_line ('Loop detected!'||v_child_id);
WHEN OTHERS THEN
dbms_output.put_line ('Other Exceptions!');
END;
END LOOP;
END LOOP;


Thanks & Regards,
Ruchira