Problem in tracking the level in hierarchical query
I need to move the data from one table to the other:
The data in the two tables are shown below:
B ------> D
B ------> F
I need to move the data from table 1 to table 2 in such a way that if the hierarchial structure exists in table 2 for a part already, I should not overwrite it. For example for the above case if I am trying to move the data for A, then since A already exists, it should not be overwritten. This can be done using ‘start with connect by query’ in a cursor and looping through it. Now the problem is that I need only that level from which the data was not overwritten and I am not interested in the child records of it. Eventhough for the above case I will get a list of records with parent as A,B,D,F and G in the not moved list, I simply do not have any way to identify the highest level.
I used SYS_CONNECT_BY_PATH , SUBSTR and INSTR functions to see if the parent part is listed in the existing parts list and if so to neglect the child parts under it. But, if the hierarchical structure is huge, it will throw some memory related errors because of SYS_CONNECT_BY_PATH function.
I could have used the pseudo column to track the level if the records in the cursor had to be in the same order as the hierarchical structure. But, because of the performance issues I select only distinct records in the start with connect by query and hence we cannot expect the record A-B to come before B-F always, in which case using level pseudo column will not work always.
I would like to know if there is any simple and efficient way this could be handled.