Skip to Main Content
  • Questions
  • Recursive Query - heirarchical relationship

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, TAPAS.

Asked: December 28, 2000 - 1:50 pm UTC

Last updated: February 21, 2007 - 11:02 am UTC

Version: Oracle8i(8.1.7)

Viewed 1000+ times

You Asked

There is a little correction in the tables. They look like:

TABLE: emp | TABLE: mgr
empno mgr_key | mgr_key empno
----- ------- | ------- -----
1 k1 | k1 2
2 k2 | k1 3
3 k3 | k3 4
4 k4 |

-----------------------------------------------------------------
The existing schema is on DB2 and I want to convert it to Oracle.
They have modelled the heirarchical relationship in two tables rather than one. The emp table can serve as example. The relationship between employees and their managers are modelled in the same table 'emp' as follows:

TABLE: emp

empno mgr
----- ---
1
2 1
3 1
4 3

This table could be queried using CONNECT BY PRIOR to report the heirarchical relationship.

But at the client site I'm working this is modelled as two tables:

TABLE: emp | TABLE: mgr
empno | mgr empno
----- | --- -----
1 | 1 2
2 | 1 3
3 | 3 4
4 |

As CONNECT BY PRIOR is not allowed on a table join, I cannot query the heirarchical relationship out of these two tables. I suggested to change the design but it is not possible due to many reasons. I solved this broblem by creating another table out of the results of the join and then using CONNECT BY PRIOR on the new table. But it takes huge amount of space and also adds the overhead of populating the new table every time the base table data changes. In DB2, it's possible to do the recursive query with one SQL statement. It has 'WITH' clause which allows the same query to refer to itself iteratively.

Is there another better solution available in Oracle than what I'm doing?

Thanks and regards

Tapas


and Tom said...

This is a perfect time to use a function based index then. We can create a function that converts the "mgr_key" into the empno. We can index that function. Then, we can write a query like:


tkyte@TKYTE816> select *
2 from emp
3 start with mgr_lookup(mgr_key) = -1
4 connect by prior empno = mgr_lookup(mgr_key)
5 /


where mgr_lookup is the function to convert a mgr_key into an empno and have it run very efficiently.

See
</code> http://asktom.oracle.com/~tkyte/article1/index.html <code>
for details on function based indexes and how to use them. Here is a small example showing this at work:


tkyte@TKYTE816> create table emp
2 as
3 select ename, empno, 'mgr_' || mgr mgr_key
4 from scott.emp;

Table created.

tkyte@TKYTE816> create table mgr
2 as
3 select distinct 'mgr_' || mgr mgr_key, mgr empno
4 from scott.emp
5 where mgr is not null;

Table created.

So, that gives me a table similar to your structure. The MGR_KEY in emp is a foreign key to the MGR table which stores the mapping between a mgr_key and the real empno

tkyte@TKYTE816> create or replace
function mgr_lookup( p_mgr_key in varchar2 ) return number
2 deterministic
3 as
4 l_empno mgr.empno%type;
5 begin
6 select empno
7 into l_empno
8 from mgr
9 where mgr_key = p_mgr_key;
10
11 return l_empno;
12 exception
13 when no_data_found then return -1;
14 end;
15 /

Function created.

There is the function to convert a mgr_key into an EMPNO. Notice how I use -1 NOT a NULL in this function. I NEVER want this to return NULL as NULLS are not indexed in btrees and I really want to use the index as much as possible. this may or may not be relevant in your case. Given the scott.emp table -- it was. I wanted to start with "king" who has no manager and hence would normally say "start with mgr is null" but that would have to full scan emp and I wanted to avoid that so I do "start with mgr = -1"

tkyte@TKYTE816> create index mgr_lookup_idx on emp(mgr_lookup(mgr_key))
2 /

Index created.

Now, I create an index on that. this index will be maintained for me by the database. I do not have to rebuild any temporary tables or anything when the structure changes -- this does it for me

tkyte@TKYTE816> alter session set QUERY_REWRITE_ENABLED=TRUE;

Session altered.

tkyte@TKYTE816> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;

Session altered.

These settings would probably be done at the database level in the init.ora, not at the session level. See the paper referenced above for details on this.


tkyte@TKYTE816> set autotrace on explain
tkyte@TKYTE816>
tkyte@TKYTE816> select /*+ first_rows */
2 rpad( '*', (level-1)*2, '*' ) || ename ename,
empno, mgr_key
3 from emp
4 start with mgr_lookup(mgr_key) = -1
5 connect by prior empno = mgr_lookup(mgr_key)
6 /

ENAME EMPNO MGR_KEY
--------------- ---------- --------
KING 7839 mgr_
**JONES 7566 mgr_7839
****SCOTT 7788 mgr_7566
******ADAMS 7876 mgr_7788
****FORD 7902 mgr_7566
******SMITH 7369 mgr_7902
**BLAKE 7698 mgr_7839
****ALLEN 7499 mgr_7698
****WARD 7521 mgr_7698
****MARTIN 7654 mgr_7698
****TURNER 7844 mgr_7698
****JAMES 7900 mgr_7698
**CLARK 7782 mgr_7839
****MILLER 7934 mgr_7782

14 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1
1 0 CONNECT BY
2 1 INDEX (RANGE SCAN) OF 'MGR_LOOKUP_IDX' (NON-
3 1 TABLE ACCESS (BY USER ROWID) OF 'EMP'
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1
5 4 INDEX (RANGE SCAN) OF 'MGR_LOOKUP_IDX' (NON-



The query plan shows we

o used the index to find the start with rows
o used the index to connect by

So the runtime performance of this will be excellent. it will not be actually invoking the PLSQL routine when we query. Only when we modify the (update or insert) the MGR_KEY column in the EMP table will this function be invoked. Since we typically insert/update a single row at a time, the overhead of this function based index typically will not be noticed by an existing application.





Rating

  (34 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

New feature in Oracle9i

TAPAS SAHOO, June 28, 2001 - 5:04 pm UTC

Tom,
I want to share this important information on the above question. I was happy to know that Oracle has introduced the 'WITH' clause in release 9i that will solve the problem I was facing as described above. This feature was available in DB2 and I was wondering why Oracle doesn't have it.

Recursive call

sivababu, December 07, 2001 - 12:57 pm UTC

Hello TOM,
I searched in the archive and looked on this example. But i'm not able to solve this problem.
look this example,

I have this table.

create table t(parent varchar(10), child varchar(10));

values are:
insert into t values('100','200');
insert into t values('200','300');
insert into t values('300','100');
If i will give the following query i got error.

select parent, child from t start with parent=100 connect by prior parent= child

ERROR:
ORA-01436: CONNECT BY loop in user data

This is correct. I made a recursive loop for raising the error. But it is not working correctly. I couldn't get where i made mistake. Look this procedure.


CREATE OR REPLACE PROCEDURE CHECKloop ( p_parent varchar2,
p_child varchar2 )
as
Looped exception;
strChild varchar2(100);
cursor cSqlRec is
select child from t where child=p_parent;
begin
if p_parent = p_child then
raise Looped ;
else
open cSqlRec;
loop
fetch cSqlRec Into strchild;
exit when cSqlRec%notfound;
If p_Parent=p_Child then
close cSqlRec;
raise Looped ;
end if;
CHECKloop ( strchild, p_child );
end loop;
close cSqlRec;
end if;
exception
when Looped then
raise_application_error( -20001, 'Loop data' );
end;
/


exec checkloop('100','200');

expecting your reply,

regards,
sivababu


SQL to detect recursive loop

Ma$e, September 23, 2003 - 2:34 pm UTC

Tom:

I was wondering if you can help me with a SQL to detect recursive loop condition that might exist in a table having two columns (group_id, member_id) where two groups are members of each other.

Can such a situation be prevented with proper referential integrity or constraints or some other means ?

Thanks
Ma$e

Tom Kyte
September 23, 2003 - 2:44 pm UTC

i'm not aware of any RI that can deal with this.

generally, you have to actually run the connect by and walk the tree in order to do this. it is very hard to do so in a multi-user situation, really hard. you would have to serialize on the inserts to do it properly.

Here is my solution...is there a better way?

Ma$e, September 23, 2003 - 3:10 pm UTC

Hi Tom:

Thanks for the prompt response.

On the issue of detecting a looping situation, here is what I came up with. Is this an optimal solution ?

SQL> connect scott/tiger
Connected.
SQL> 
SQL> 
SQL> 
SQL> desc gm;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ------------------------
 G                                                                          NUMBER
 M                                                                          NUMBER

SQL> select * from gm;

         G          M
---------- ----------
       123        777
       777        123
       123        345
       123        678
       777        779
       777        998

6 rows selected.

SQL> get gm.sql
  1* select a.g from gm a where a.m in (select distinct b.g from gm b where b.m=a.g)
SQL> 
SQL> @gm

         G
----------
       123
       777

SQL> 


Thanks once again for your assistance. 

Tom Kyte
September 23, 2003 - 6:15 pm UTC

1 3
3 2
2 1

1->3->2->1->3->......

ops$tkyte@ORA920LAP> create table gm ( g int, m int );

Table created.

ops$tkyte@ORA920LAP> insert into gm values ( 1, 3 );

1 row created.

ops$tkyte@ORA920LAP> insert into gm values ( 3, 2 );

1 row created.

ops$tkyte@ORA920LAP> insert into gm values ( 2, 1 );

1 row created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select a.g from gm a where a.m in (select distinct b.g from gm b where
  2  b.m=a.g);

no rows selected

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select * from gm start with g = 1 connect by prior m = g;
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected


you have to actually walk the tree. 

What if....

Ma$e, September 24, 2003 - 12:00 pm UTC

Hi Tom:

Thanks for the response.

Does the situation change if I had another table that would differentiate between members and groups. I think I agree with you that prevention of a looping situation at run-time would be difficult, however if I can find a way to detect it and report it, I'll be a happy camper.

Have you covered the issue of "walking the tree" in one of the discussions ? If so, can you please provide me with the link.

Thanks

Ma$e


Tom Kyte
September 24, 2003 - 12:37 pm UTC

i don't know what a "member" and a "group" means in your context.

the only real way i know to do it is:

lock the table
do the insert
do a connect by using the newly inserted row as root row and walk down the tree
do a connect by using the newly inserted row as root row and walk up the tree
commit



My definition

Ma$e, September 24, 2003 - 1:32 pm UTC

Hi Tom:

Thanks for the response.

I think in terms of users and groups. A user is an end point, but a group can contain users and other groups.

We track the users and groups in another table via a type columns: users = 1 and groups = 4. This table contains all the users and groups defined in the system.

Ma$e



generating hierarchical test data

A reader, September 24, 2003 - 8:49 pm UTC

Hi Tom
Is there a sql way of generating hierarchical data
to create a big table with hierarchies running to
say a depth of 5-10 or so? This is for testing
purposes?

Thanx!

performance of hierarchical queries

A reader, September 25, 2003 - 12:12 pm UTC

consider if the hierarchy depth is large (5-10) and the number of records we are weeding through is also large (say in 50K range).
How do hierarchical queries perform? Do you ever consider
maintaining a flattened version of data to improve performance?
thanx!

Tom Kyte
September 25, 2003 - 11:28 pm UTC

they perform fine. weeding through 50k records -- hope that is a batch process, an end user couldn't possibly use that.

Hierarchical query

A reader, June 09, 2004 - 5:03 am UTC

Hi Tom

Consider the example below:

SQL> create table test
  2  ( id number,
  3    parent_id number,
  4    ultimate_parent_id number );

Table created.

SQL> insert into test values(1,1,'');

1 row created.

SQL> insert into test values(2,1,'');

1 row created.

SQL> insert into test values(3,2,'');

1 row created.

SQL> insert into test values(4,3,'');

1 row created.

SQL> commit;

Commit complete.

SQL> select *from test;

        ID  PARENT_ID ULTIMATE_PARENT_ID
---------- ---------- ------------------
         1          1
         2          1
         3          2
         4          3

Now, the value of column ultimate_parent_id need to be set to 1 for all the records. Because ids 2,3,4 ultimately point to 1 which contains the original input. Is it possible to write a single select statement to retreive the value of root record?

Thanks 

Tom Kyte
June 09, 2004 - 8:57 am UTC

do you really want to set it, or just retrieve it.  setting it would make maintenance harder -- if you update id=2 and reparent it, you would have to "know" to update 3 and 4 as well.

You can either update it:


ops$tkyte@ORA9IR2> update test t1
  2     set ultimate_parent_id = ( select substr( max(to_char(level,'fm00000')||t2.id), 6 )
  3                                  from test t2
  4                                 start with t2.id = t1.id
  5                               connect by prior t2.parent_id = t2.id and prior t2.parent_id <> prior t2.id )
  6  /
 
4 rows updated.

<b>and be responsible for maintaining it or you can select it out:</b>

 
ops$tkyte@ORA9IR2> select t1.*, to_number( ( select substr( max(to_char(level,'fm00000')||t2.id), 6 )
  2                              from test t2
  3                             start with t2.id = t1.id
  4                           connect by prior t2.parent_id = t2.id and prior t2.parent_id <> prior t2.id ) ) upid
  5    from test t1
  6  /
 
        ID  PARENT_ID ULTIMATE_PARENT_ID       UPID
---------- ---------- ------------------ ----------
         1          1                  1          1
         2          1                  1          1
         3          2                  1          1
         4          3                  1          1
 

<b>as you need it</b>
 

Hierarchical query

A reader, June 09, 2004 - 9:24 am UTC

You really are a star Tom !!!

Thanks very much for your help.

I want to know

r, September 10, 2004 - 7:54 am UTC

I want to fetch the level of leaf node , How to do this

Tom Kyte
September 10, 2004 - 9:26 am UTC

umm, select level?

A reader, March 15, 2005 - 2:19 pm UTC


What happens if we update a value in the mgr table?

Steve, September 12, 2005 - 4:12 pm UTC

Tom,

You wrote, "Only when we modify (update or insert) the MGR_KEY column in the EMP table will this function be invoked."

What happens if we modify the MGR_KEY or EMPNO columns in the MGR table? Is there a dependency relationship between MGR and EMP that the database "knows" about that will invoke a refresh of certain values in the function-based index on the EMP table?

I am trying to implement your solution in 8.1.7.4 and it appears that the index must be rebuilt each time a value in MGR changes. The does not appear to be maintained by the database. Or have I implemented something incorrectly?

Ragards,
Steve

Tom Kyte
September 12, 2005 - 5:41 pm UTC

The index is maintained only when it needs be - when the mgr_key column is modified.

The function must be deterministic! That is, given the same inputs - it *always* returns the same outputs, that is one of the mandatory things about a function based index and we must assert that via the deterministic keyword

Can I setup a trace to show me where in the tree this fails

Rob, October 07, 2005 - 11:53 am UTC

Tom:

I am hitting hitting the ora-1436 error on a connect by query that is executed many, many times. I would like to get the value of the bind variables when this error occurs so I can know what data is causing the problem. Would setting the following event cause a dump with the bind values when the error occurs:
event="1436 trace name errorstack level 12"

Thanks as always for your help.

Rob

Tom Kyte
October 07, 2005 - 9:54 pm UTC

when you tested it........ what did you see......



It worked

Rob, October 21, 2005 - 10:46 am UTC

Well, I saw the values of the bind variables.

Parag J Patankar, February 17, 2006 - 4:16 am UTC

11:57:10 SQL> create table t
11:57:17   2  (
11:57:17   3  a  number,
11:57:17   4  b  number,
11:57:17   5  c  number,
11:57:17   6  d  number);

Table created.

insert into t values (1, 1, 1, 1);
insert into t values (2, 2, 1, 1);
insert into t values (3, 3, 1, 1);
insert into t values (4, 4, 2, 2);
insert into t values (5, 5, 5, 5);
insert into t values (6, 6, 6, 6);
insert into t values (7, 7, 6, 6);
commit;
11:59:28 SQL> select * from t;

         A          B          C          D
---------- ---------- ---------- ----------
         1          1          1          1
         2          2          1          1
         3          3          1          1
         4          4          2          2
         5          5          5          5
         6          6          6          6
         7          7          6          6

7 rows selected.

In this my parent records is always where a=c and b=d.

I want to write "hierarchical query" by following but it is giving me an error

14:20:04 SQL> select *
14:25:51   2  from t
14:25:51   3  start with a=c and b=d
14:25:51   4  connect by prior c = a and prior d = b;
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

Kindly correct me how to write such query 

thanks & regards
pjp
 

Tom Kyte
February 17, 2006 - 1:53 pm UTC

you have a loop in your data, it recurses forever.  

what should happen when that happens?  


All of your data is "an infinite loop"

ops$tkyte@ORA10GR2> select t.*, connect_by_iscycle
  2  from t
  3  start with a=c and b=d
  4  connect by NOCYCLE prior c = a and prior d = b;
 
         A          B          C          D CONNECT_BY_ISCYCLE
---------- ---------- ---------- ---------- ------------------
         1          1          1          1                  1
         5          5          5          5                  1
         6          6          6          6                  1


(nocycle and connect by iscycle are new 10g features) 

Hierarchical Deletion

Ramu, February 26, 2006 - 4:46 am UTC

Tom,

I have tables
Admin_mast(admin_id primary key,.....),
School_mast(shcool_id number primary key, ... admin_id FK),
Branch_mast(branch_id number primary key,..., school_id FK).
In this way i have many tables.

On delete cascade is not implemented.

Quite often i get request to delete some records and related child records at various levels of the hierarchy. It gets tedious to manually find the related child tables and the hierarchy and delete the records.

Is there any way i implement on delete cascade through pl/sql code using user_XXXXXXX views.

Tom Kyte
February 26, 2006 - 10:06 am UTC

why is it tedious.

You sort of know the tables in the hierarchy right - just code a stored procedure that removes the data.

application then calls code.


Could you use the dictionary? Yes.
Should you use the distionary? Absolutely not, not in my opinion.

You are removing data, you want to be darn sure you are doing the right thing. I want to have the explicit dependency between this stored procedure you should code and the objects it affects. That means static sql - no dynamic sql.



Alternative to connect_by_iscycle in Oracle 9i ?

@n@nd, April 10, 2006 - 5:26 am UTC

Hi Tom,

We use Oracle 9i Database and update to 10g is not possible any time now ? Doesnt Oracle have a similar feature or workaround when a similar situation to loop data exists?

@n@nd

Tom Kyte
April 10, 2006 - 6:02 am UTC

if it did - we would not have needed to create a new function?

You MIGHT be able to code into the connect by some condition that prevents the loop - but only you know if you can (based on your knowledge of the data itself)

this is my trick to detect ORA-01436 on bad data

virgile CREVON, April 21, 2006 - 4:07 am UTC

Hi guys,

Tom already provide you with the solution to detect issues in 9i : "just walk the tree".

If you're not familiar with PL, i can give you a sample code to do the thing for you (and keep your brain very cold ....) :

create table XX(parent number, child number, id number);

insert into XX values(1,2,1);
insert into XX values(2,3,1);

commit ;

select parent,child
from XX
connect by prior child = parent;

=> OK

insert into XX values(2,4,1);
insert into XX values(2,4,1);

commit ;

=> OK

select parent,child
from XX
connect by prior child = parent;


case 1 :

truncate table XX ;

insert into XX values(1,1,2) ;

commit ;

select parent,child
from XX
connect by prior child = parent;

--> NOK

détection par égalité des prédicats (detect by equal predicate method)

cas 2 :


truncate table XX ;

insert into XX values(1,2,2) ;
insert into XX values(2,1,2) ;

commit ;

select parent,child
from XX
start with parent=3
connect by prior child = parent;


--> NOK

Detect here by the "walk the tree" method ;o)

set serveroutput on;
execute dbms_output.enable(100000);


DECLARE
/* code source by VIC 20/04/2006 @RENAULT */
parent_value number ;
dummy number ;
erreur_cp exception ;
PRAGMA exception_init (erreur_cp, -01436) ;

BEGIN

dbms_output.put_line ('BEGIN');

for X in (select distinct parent from XX )
LOOP
BEGIN
parent_value := x.parent;

select min (parent) into dummy from xx
start with parent = x.parent
connect by prior child = parent ;
EXCEPTION
when erreur_cp
THEN
dbms_output.put_line ('bogus value : '||parent_value);
END;
END LOOP;
dbms_output.put_line ('END');
EXCEPTION
when others
THEN
dbms_output.put_line ('Error msg :'||SQLERRM);
END;
/

=> The min lines of code i can write, thaht will do the thing you all asks for.

Regards,

Virgile

Recursion handling

Srividya, November 21, 2006 - 9:45 am UTC

Thanks a lot for that piece of code that helps in finding the employee list that causes the CONNET BY LOOP problem in 9i....

Connect by level

Kishore, February 15, 2007 - 1:52 pm UTC

Hi Sir

Could you please explain with a samll example how
CONNECT BY LEVEL works.

Regards
Kishore

Tom Kyte
February 16, 2007 - 1:09 pm UTC

select * from dual connect by level < 5;

for the first row, level is 1 - 1 < 5, so it "connects" - that increased the level to 2. 2 < 5, so that "connects"...

Connect By

Kishore, February 16, 2007 - 1:26 pm UTC

you said the first row had level 1 and coz 1 < 5 it connects. But what does it connects to.
I have a dual table which has only 1 row. So it read the 1 (only row) from dual for which level was 1. How would it get to the second row. Where does the second row come from in the dual table.

Regards
Kishore
Tom Kyte
February 17, 2007 - 11:17 am UTC

it connects to "itself"


connect by is not giving the full tree

A reader, February 17, 2007 - 8:43 am UTC

Hi Tom,

When i use connect by i am not getting the full tree as i am expecting.

Please see my query below

SQL> select * from emp;

EMPNO ENAME MGR
---------- ---------- ----------
100 A
100 C A
100 H C
100 E A
100 N E
100 E B
100 P O
100 O F
100 J O
100 B
100 F

11 rows selected.

SQL> select ename, level
2 from emp
3 start with mgr is null
4 connect by prior ename = mgr;

ENAME LEVEL
---------- ----------
A 1
C 2
H 3
E 2
N 3
B 1
E 2
N 3
F 1
O 2
P 3
J 3

12 rows selected.

SQL>

but i want output like given below

ENAME LEVEL
---------- ----------
A 1
C 2
H 3
A 1
E 2
N 3
B 1
E 2
N 3
F 1
O 2
P 3
F 1
O 2
J 3

How to achieve above result from the query
select ename, level
from emp
start with mgr is null
connect by prior ename = mgr;
i.e i want the result to start everytime from root.
Please guide me how can i get the above output.
Tom Kyte
February 18, 2007 - 9:40 am UTC

no create table
 no insert intos
   no look

Conenct by

A reader, February 20, 2007 - 9:39 am UTC

INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (100, 'A', NULL);
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (200, 'C', 'A');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (300, 'H', 'C');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (400, 'E', 'A');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (500, 'N', 'E');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (600, 'E', 'B');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (700, 'P', 'O');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (800, 'O', 'F');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (900, 'J', 'O');
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (1000, 'B', NULL);
INSERT INTO EMP(EMPNO,ENAME,MGR) VALUES (1100, 'F', NULL);
COMMIT;

if i start A as root, i should get the complete branches from root.

A
C
H
A
E
N

I am expecting output to start with root for all branches.i.e 'A' should appear as start to all the branches.
Hope i am able to explain the issue now.

please tell me how can i achieve with connect by.
Tom Kyte
February 20, 2007 - 9:56 am UTC

Not really sure what you are looking for - A is the parent of C and E and that is "obvious"


ops$tkyte%ORA9IR2> select rpad('*',2*level,'*') || ename nm, mgr, empno
  2    from emp
  3   start with mgr is null
  4   connect by prior ename = mgr;

NM         M      EMPNO
---------- - ----------
**A                 100
****C      A        200
******H    C        300
****E      A        400
******N    E        500
**B                1000
****E      B        600
******N    E        500
**F                1100
****O      F        800
******P    O        700
******J    O        900

12 rows selected.


A reader, February 21, 2007 - 5:43 am UTC

Tom,
Yes A is root for E and C. For branch E and C i want the tree to start from A.

expected output is this

NM M EMPNO
---------- - ----------
**A 100
****C A 200
******H C 300
**A 100
****E A 400
******N E 500
**B 1000
****E B 600
******N E 500
**F 1100
****O F 800
******P O 700
**F 1100
****O F 800
******J O 900

which means the root should repeat for all the branches. A should repeat for all the branches which starts with A.

Thanks for all your help.

Tom Kyte
February 21, 2007 - 11:02 am UTC

it does not work that way, sorry - you can easily synthesize that since you have "prior nm" you can select and you have sys_connect_by_root or sys_connect_by_path as well.

To: A reader

Michel Cadot, February 21, 2007 - 9:45 am UTC


It seems you only want leaves but with all the path.

First you can get the path for leaves with:
SQL> select sys_connect_by_path(ename,'/') path
  2  from t a
  3  where not exists (select null from t b where b.mgr = a.ename)
  4  start with mgr is null
  5  connect by prior ename = mgr
  6  /
PATH
------------------------------
/A/C/H
/A/E/N
/B/E/N
/F/O/J
/F/O/P

Then you just have to split the lines (there are many exemples on AskTom).
If you know the maximum depth (here 3), you can use:
SQL> with 
  2    data as (
  3      select sys_connect_by_path(ename,'/')||'/' path, rownum rn
  4      from t a
  5      where not exists (select null from t b where b.mgr = a.ename)
  6      start with mgr is null
  7      connect by prior ename = mgr
  8    ),
  9    lines as ( select rownum ln from dual connect by level <= 3 )
 10  select rn, ln, 
 11         substr(path, 
 12                instr(path, '/', 1, ln) + 1,
 13                instr(path, '/', 1, ln+1) - instr(path, '/', 1, ln) - 1) ename
 14  from data, lines
 15  where ln < length(path)-length(replace(path,'/',''))
 16  order by rn, ln
 17  /
        RN         LN ENAME
---------- ---------- ------------------------------
         1          1 A
         1          2 C
         1          3 H
         2          1 A
         2          2 E
         2          3 N
         3          1 B
         3          2 E
         3          3 N
         4          1 F
         4          2 O
         4          3 J
         5          1 F
         5          2 O
         5          3 P

Regards
Michel

Conenct by

A reader, February 21, 2007 - 10:13 am UTC

Thanks Michel,

Yes this is what i am expecting. But it didn't work for me. i get the below error
SP2-0642: SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed

and i do not know the maximum depth of the tree.

SQL solution for leaves only path

Frank Zhou, February 21, 2007 - 12:43 pm UTC

Hi reader

Here is a sql for leaves only path.



Frank


SQL> COLUMN ename FORMAT A3
SQL> select LEVEL ,
2 SUBSTR(path,
3 INSTR(path, '/', 1, LEVEL ) + 1,
4 INSTR(path, '/', 1, LEVEL+1) -
5 INSTR(path, '/', 1, LEVEL) -1 ) ename
6 from
7 (
8 select sys_connect_by_path(ename,'/')||'/' path , connect_by_isleaf Leaf_flag
9 from emp a
10 start with mgr is null
11 connect by prior ename = mgr
12 ) where Leaf_flag = 1
13 CONNECT BY PRIOR path = path
14 AND INSTR (path, '/', 1, LEVEL+1) > 0
15 AND PRIOR dbms_random.string ('p', 10) IS NOT NULL;

LEVEL ENA
---------- ---
1 A
2 C
3 H
1 A
2 E
3 N
1 B
2 E
3 N
1 F
2 O
3 J
1 F
2 O
3 P

15 rows selected.

Thanks a lot

A reader, February 21, 2007 - 2:25 pm UTC

Hi Frank,

Thanks a lot...


Conect By

A reader, February 22, 2007 - 8:13 am UTC

Hi,

The query was very useful to me. Thanks a lot Tom, Michel and Frank..
Tom, i have a requirement to number the roots and branches.

1 1 1 A
1 1 2 C
1 1 3 H
2 1 1 A
2 1 2 E
2 1 3 N
3 2 1 B
3 2 2 E
3 2 3 N
4 3 1 F
4 3 2 O
4 3 3 J
5 3 1 F
5 3 2 O
5 3 3 P

How to achive them.

Thanks in advance.

SQL for numbering the Leaf roots and branches

Frank Zhou, February 22, 2007 - 11:44 am UTC

Hi Reader,

Here is the sql for numbering the leaf roots and branches.

Frank

SQL>
SQL> SELECT LEVEL,
2 SUBSTR(path,
3 INSTR(path, '/', 1, LEVEL ) + 1,
4 INSTR(path, '/', 1, LEVEL+1) -
5 INSTR(path, '/', 1, LEVEL) -1 ) ename, rn branch_num,
6 dense_rank( ) over ( order by rt) root_num
7 FROM
8 (
9 SELECT path , rt, rownum rn
10 FROM (SELECT sys_connect_by_path(ename,'/')||'/' path, connect_by_root ename as rt ,
11 connect_by_isleaf Leaf_flag
12 FROM emp a
13 START WITH mgr IS NULL
14 CONNECT BY PRIOR ename = mgr
15 )
16 WHERE Leaf_flag = 1
17 )
18 CONNECT BY PRIOR path = path
19 AND INSTR (path, '/', 1, LEVEL+1) > 0
20 AND PRIOR dbms_random.string ('p', 10) IS NOT NULL;

LEVEL ENA BRANCH_NUM ROOT_NUM
---------- --- ---------- ----------
1 A 1 1
2 C 1 1
3 H 1 1
1 A 2 1
2 E 2 1
3 N 2 1
1 B 3 2
2 E 3 2
3 N 3 2
1 F 4 3
2 O 4 3
3 J 4 3
1 F 5 3
2 O 5 3
3 P 5 3

15 rows selected.

Root and branches

A reader, February 22, 2007 - 6:59 pm UTC

Thanks a lot for the Query.Unfortunately i didnot get the expected result for complex tree structures.

The Insert statement for tree is given below

INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( A--TEST@@GBP007', 'C--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( A--TEST@@GBP007', 'D--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( A--TEST@@GBP007', 'E--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( C--TEST@@GBP007', 'F--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( C--TEST@@GBP007', 'G--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( F--TEST@@GBP007', 'H--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( H--TEST@@GBP007', 'I--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( H--TEST@@GBP007', 'J--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( J--TEST@@GBP007', 'K--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( J--TEST@@GBP007', 'L--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( J--TEST@@GBP007', 'M--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( K--TEST@@GBP007', 'P--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( D--TEST@@GBP007', 'J--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( O--TEST@@GBP007', 'M--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( Q--TEST@@GBP007', 'M--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( E--TEST@@GBP007', 'N--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( E--TEST@@GBP007', 'O--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( B--TEST@@GBP007', 'E--TEST@@GBP007');
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( G--TEST@@GBP007', NULL);
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( I--TEST@@GBP007', NULL);
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( L--TEST@@GBP007', NULL);
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( M--TEST@@GBP007', NULL);
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( N--TEST@@GBP007', NULL);
INSERT INTO BATCH_INPUT_OUTPUT ( OUTPUT_BATCH_ID, INPUT_BATCH_ID ) VALUES ( P--TEST@@GBP007', NULL);

output is 56 rows as given below

I--TEST@@GBP007 1 1 5
H--TEST@@GBP007 1 1 4
F--TEST@@GBP007 1 1 3
C--TEST@@GBP007 1 1 2
A--TEST@@GBP007 1 1 1
P--TEST@@GBP007 1 2 7
K--TEST@@GBP007 1 2 6
J--TEST@@GBP007 1 2 5
H--TEST@@GBP007 1 2 4
F--TEST@@GBP007 1 2 3
C--TEST@@GBP007 1 2 2
A--TEST@@GBP007 1 2 1
L--TEST@@GBP007 1 3 6
J--TEST@@GBP007 1 3 5
H--TEST@@GBP007 1 3 4
F--TEST@@GBP007 1 3 3
C--TEST@@GBP007 1 3 2
A--TEST@@GBP007 1 3 1
M--TEST@@GBP007 1 4 6
J--TEST@@GBP007 1 4 5
H--TEST@@GBP007 1 4 4
F--TEST@@GBP007 1 4 3
C--TEST@@GBP007 1 4 2
A--TEST@@GBP007 1 4 1
G--TEST@@GBP007 1 5 3
C--TEST@@GBP007 1 5 2
A--TEST@@GBP007 1 5 1
P--TEST@@GBP007 1 6 5
K--TEST@@GBP007 1 6 4
J--TEST@@GBP007 1 6 3
D--TEST@@GBP007 1 6 2
A--TEST@@GBP007 1 6 1
L--TEST@@GBP007 1 7 4
J--TEST@@GBP007 1 7 3
D--TEST@@GBP007 1 7 2
A--TEST@@GBP007 1 7 1
M--TEST@@GBP007 1 8 4
J--TEST@@GBP007 1 8 3
D--TEST@@GBP007 1 8 2
A--TEST@@GBP007 1 8 1
N--TEST@@GBP007 1 9 3
E--TEST@@GBP007 1 9 2
A--TEST@@GBP007 1 9 1
M--TEST@@GBP007 1 10 4
O--TEST@@GBP007 1 10 3
E--TEST@@GBP007 1 10 2
A--TEST@@GBP007 1 10 1
N--TEST@@GBP007 2 1 3
E--TEST@@GBP007 2 1 2
B--TEST@@GBP007 2 1 1
M--TEST@@GBP007 2 2 4
O--TEST@@GBP007 2 2 3
E--TEST@@GBP007 2 2 2
B--TEST@@GBP007 2 2 1
M--TEST@@GBP007 3 1 2
Q--TEST@@GBP007 3 1 1

The above is a sample tree. the actual tree is more complex and millions of records exists.

Please guide me how to achieve the tree as shown above.

Thanks a ton for all your help.

Great answer. Can this be used to find the transitive relation

Ram, August 02, 2007 - 11:08 am UTC

create table t_grp (id1 char(1), id2 char(2)) ;

insert into t_grp values ('A','C'); --> A:C are related
insert into t_grp values ('B','D'); --> B:D are related
insert into t_grp values ('F','H');
insert into t_grp values ('G','H');
insert into t_grp values ('B','G');
insert into t_grp values ('X','Y');
insert into t_grp values ('W','Y');

I want to group the values based on the following rules,

1, if any of the value from id1 or id2 can be linked to any other values, they all will be treated as one group

Ex, in the above case
A has relationship to C, neither of them has any other relationship to any other values in the table
B has relationship to D, B has relationship to G, G has relationship to H, H has relationship to F
F has relationship to H, H has relationship to G ...

A:C - Group 1
B:D -> B:G -> G:H -> H:F - Group 2
F:H -> H:G -> G:B -> B:D - Group 2
G:H -> G:B -> H:F -> B:D - Group 3
G:B -> G:H -> H:F -> B:D - Group 2
X:Y -> Y:W - Group 3

grp id1 id2
----- ------ ------
1 A C
2 B D
2 F H
2 G H
2 B G
3 X Y
3 W Y

The following output is also fine [This would be the final output]

grp id
---- -------
1 A
1 C
2 B
2 D
2 F
...
3 X
3 Y
3 W
3 Y

Note: I am using Oracle 8i

optimum connect by query

whizkid, May 14, 2010 - 8:39 am UTC

Have a requirement to update the policy year on an incremental basis to the existing set of policies. Below is the script

CREATE TABLE TMP_HEALTH_RENEWAL_UNQ
(
POLICY_REF VARCHAR2(50 BYTE),
OLD_POLICY_NO VARCHAR2(50 BYTE)
);


Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081901840100000996', 'XX071901840100000747');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071901840100002458', 'XX061901840100001952');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001847', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071908840100000054', 'XX061901840100001857');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX082201840100002435', 'XX072201840100002420');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081908840100000327', 'XX071901840100002458');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081908840100000262', 'XX071908840100000052');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051901840100001837', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081905840100000712', 'XX071905840100000692');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061901840100001820', 'XX051901840100001840');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001703', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091905840100001273', 'XX081905840100000712');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051905840100000033', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051905840100000066', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051901840100001905', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091901840100003598', 'XX081901840100002542');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091908840100000689', 'XX081908840100000317');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091905840100001576', 'XX081905840100000888');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071901840100000747', 'XX061901840100000660');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051901840100001839', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061905840100000722', 'XX061905840100000003');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001693', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071905840100000692', 'XX061905840100000492');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071901840100002401', 'XX061901840100001816');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061901840100001851', 'XX051901840100001905');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061901840100001952', 'XX051901840100002011');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061906840100000746', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091901840100001290', 'XX081901840100000996');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051901840100001906', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061901840100001809', 'XX051901840100001838');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061901840100001857', 'XX051901840100001906');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051901840100000573', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX092201840100002477', 'XX082201840100002234');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071901840100002481', 'XX061901840100001820');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX072201840100000798', 'XX062201840100000465');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051901840100001838', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081905840100000344', 'XX071905840100000395');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX072201840100002334', 'XX062201840100001748');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX082201840100002236', 'XX072201840100002273');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051905840100000034', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051901840100001840', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001892', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100000465', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071905840100000838', 'XX061905840100000722');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061901840100000497', 'XX051901840100000517');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091901840100003599', 'XX081901840100002541');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001894', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001890', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081901840100007308', 'XX071901840100005825');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081905840100000888', 'XX071905840100000838');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051905840100000025', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071102840100000572', 'XX061102840100000194');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX092201840100002493', 'XX082201840100002236');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061102840100000194', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091901840100003602', 'XX081901840100002543');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081102840100000445', 'XX071102840100000572');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091908840100000753', 'XX081908840100000327');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061901840100000660', 'XX051901840100000573');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX072201840100002273', 'XX062201840100001703');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071901840100002235', 'XX061901840100001809');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061905840100000492', 'XX051905840100000066');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX072201840100002462', 'XX062201840100001847');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051901840100001440', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001891', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081901840100003141', 'XX071901840100002235');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081901840100002545', 'XX071901840100002380');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001895', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001980', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX072201840100002420', 'XX062201840100001980');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071901840100005825', 'XX061906840100000746');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081901840100002541', 'XX071901840100002481');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001748', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061901840100001819', 'XX051901840100001837');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX092201840100002573', 'XX082201840100002249');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081901840100002544', 'XX071901840100002235');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX072201840100000666', 'XX062201840100000465');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081908840100000317', 'XX071908840100000054');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091911840100000924', 'XX081901840100007308');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001893', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX072201840100002123', 'XX062201840100001693');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051901840100000517', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX051901840100002011', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081901840100002543', 'XX071901840100002401');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091905840100000570', 'XX081905840100000344');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061905840100000003', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071905840100000395', 'XX061905840100000201');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081901840100002542', 'XX071901840100002236');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071901840100002224', 'XX061901840100001819');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX082201840100002234', 'XX072201840100002334');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071901840100005920', 'XX061905840100000283');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091901840100003591', 'XX081901840100003141');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061901840100001816', 'XX051901840100001839');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061905840100000201', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX081901840100002784', 'XX071901840100002224');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX062201840100001889', '');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071901840100002236', 'XX061901840100001816');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071908840100000052', 'XX061901840100001851');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX071901840100002380', 'XX061901840100001820');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX092201840100002639', 'XX082201840100002435');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX082201840100002249', 'XX072201840100002123');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091901840100003590', 'XX081901840100002545');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX061905840100000283', 'XX051905840100000025');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091901840100003541', 'XX081901840100002784');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX091908840100000747', 'XX081908840100000262');
Insert into TMP_HEALTH_RENEWAL_UNQ
(POLICY_REF, OLD_POLICY_NO)
Values
('XX101901840100001161', 'XX091901840100001290');
COMMIT;

select a.*,
lpad(' ', level*8) || policy_ref pol_hier,
sys_connect_by_path (policy_ref, '/') cpb,
level policy_year,
connect_by_isleaf last_node,
connect_by_iscycle
fromtmp_health_renewal_unq a
connect by nocycle prior policy_ref = old_policy_no

Using the above SQL, am able to get the policy year. Issue is the base data is about 8 million records and when I create a table out of the sql, it does not get complete even after 30 hours. (Oracle 10gR2, AIX 6.1, 8cpu 32 GB RAM IBM hardware) Also on a daily basis, there will be new records inserted in this table (new policies & old policies). Policy year for the new policies also have to be calculated. Is there a way to do this optimally both for one time and incremental basis? thanks in advance for your help.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library