connected groups

connected groups

SQL> @tests/3
SQL> /*
SQL> drop table test purge;
SQL> create table test as
SQL> select rownum clientid,ceil(rownum/3)  accountid from xmltable('1 to 3000000');
SQL>
SQL> begin
SQL>    for r in (
SQL>       select min(group_member_id) as group_max_id, accountid, clientid
SQL>         from  (select clientid as group_member_id
SQL>                , connect_by_root accountid as accountid
SQL>                , connect_by_root clientid  as clientid
SQL>             from test
SQL>             connect by nocycle decode(accountid, prior accountid, 1, 0)
SQL>                              + decode(clientid,  prior clientid,  1, 0)
SQL>                              = 1
SQL>         ) a
SQL>         group by accountid, clientid
SQL>         order by group_max_id, accountid
SQL>    )
SQL>    loop
SQL>       null;
SQL>    end loop;
SQL> end;
SQL> /
SQL> */
SQL> declare
  2     type int_array    is table of pls_integer index by pls_integer;
  3     type arr_elems    is table of sys.ku$_objnumset index by pls_integer;
  4     root              int_array;
  5     root_elems        arr_elems;
  6
  7     n        int;
  8     clients  int_array;
  9     accounts int_array;
 10
 11      l integer:=dbms_utility.get_time();
 12
 13      procedure print(v in varchar2) is
 14      begin
 15        dbms_output.put_line(to_char((dbms_utility.get_time-l)/100,'0999.99')||' '||v);
 16        l:=dbms_utility.get_time();
 17      end;
 18
 19
 20     function get_root(n int) return pls_integer is
 21     begin
 22        if root.exists(n) then
 23           return root(n);
 24        else
 25           return null;
 26        end if;
 27     end;
 28
 29     procedure update_root(old_root pls_integer,new_root pls_integer) is
 30        i       pls_integer;
 31        elem    pls_integer;
 32        cnt_old pls_integer;
 33        cnt_new pls_integer;
 34     begin
 35        if old_root!=new_root then
 36           --root_elems(new_root):=root_elems(new_root) multiset union all root_elems(old_root);
 37           cnt_old:=root_elems(old_root).count;
 38           cnt_new:=root_elems(new_root).count;
 39           root_elems(new_root).extend(cnt_old);
 40           for i in 1..cnt_old
 41           loop
 42              elem := root_elems(old_root)(i);
 43              root(elem):=new_root;
 44              root_elems(new_root)(cnt_new+i):=elem;
 45           end loop;
 46           root_elems(old_root).delete;
 47        end if;
 48     end;
 49
 50     procedure add_elem(p_root pls_integer, p_elem pls_integer) is
 51     begin
 52        if not root_elems.exists(p_root) then
 53           root_elems(p_root):=sys.ku$_objnumset(p_elem);
 54        else
 55           root_elems(p_root).extend();
 56           root_elems(p_root)(root_elems(p_root).count):=p_elem;
 57        end if;
 58     end;
 59
 60     procedure add_link(clientid pls_integer,accountid pls_integer) is
 61        r1       pls_integer;
 62        r2       pls_integer;
 63        new_root pls_integer;
 64     begin
 65        r1:=get_root(clientid);
 66        r2:=get_root(accountid);
 67
 68        if r1 is null or r2 is null then
 69           new_root := coalesce(r1,r2,clientid);
 70           if r1 is null then add_elem(new_root,clientid ); root(clientid) :=new_root; end if;
 71           if r2 is null then add_elem(new_root,accountid); root(accountid):=new_root; end if;
 72        else
 73           new_root := least(r1,r2);
 74           root(clientid) :=new_root;
 75           root(accountid):=new_root;
 76           update_root(greatest(r1,r2),new_root);
 77        end if;
 78     end;
 79
 80     function str_format(p int) return varchar2 is
 81     begin
 82        return utl_lms.format_message('(%d, %d) = group #%d'
 83                                     ,clients(p)
 84                                     ,accounts(p)
 85                                     ,get_root(clients(p))
 86                                     );
 87     end;
 88  begin
 89     print('start');
 90     select clientid,accountid
 91            bulk collect into clients,accounts
 92     from test
 93  --   where rownum<=1000
 94     ;
 95     print('fetched');
 96     n:=clients.count;
 97     dbms_output.put_line('count='||n);
 98     for i in 1..n loop
 99        add_link(clients(i),accounts(i));
100     end loop;
101     print('processed');
102  ---
103  /*
104     for i in 1..n loop
105        dbms_output.put_line(str_format(i));
106     end loop;
107  --   */
108  end;
109  /
Elapsed: 00:00:25.74