I did let you know about the poor perfomance beforhand.
well, try this, I think it would be better, but I'm not sure if it would be appropriate for you.
SQL> with t as (select 1111 id1, 234 id2 from dual union all
2 select 1111, 456 from dual union all
3 select 222, 456 from dual union all
4 select 333, 456 from dual union all
5 select 444, 345 from dual union all
6 select 555, 678 from dual union all
7 select 666, 678 from dual union all
8 select 777, 789 from dual union all
9 select 777, 890 from dual union all
10 select 1111, 134 from dual union all
11 select 999, 134 from dual union all
12 select 999, 135 from dual)
13 --
14 select id1, id2, dense_rank() over (order by new_id1) key
15 from (select id1, id2, min(min(cbr)) over (partition by id1) new_id1
16 from (select tt.*, connect_by_root(id1) cbr
17 from (select t.*, prior id2 pr_id2
18 from (select t.*,
19 count(1) over(partition by id2) cnt
20 from t) t
21 connect by nocycle prior id1 = id1
22 and prior id2 id2
23 and cnt > 1
24 and prior cnt > 1) tt
25 connect by nocycle(prior id1 id1
26 and prior id2 = id2)
27 or (prior id1 = id1 and prior id2 = pr_id2))
28 group by id1, id2)
29 order by 1, 2
30 /
ID1 ID2 KEY
----------
222 456 1
333 456 1
444 345 2
555 678 3
666 678 3
777 789 4
777 890 4
999 134 1
999 135 1
1111 134 1
1111 234 1
1111 456 1
12 rows selected
SQL>
the main problem in perfomance I think is generating too many values by the two connect by's
Well, I placed inline view, which counts what is the number of each id2 in your table, and then connect by id2's only if count(id2)>=2.
So I tried to decrease number of unneccessary values generating inside, which don't infulence the op.
so, let us know if it is better.
