Re: Large table merge

"sviba" <sviba_at_sviba.net> wrote in message news:<vB0M7.1941$Lk.444668_at_newsrump.sjc.telocity.net>...
> All,
> I have two large table A:120K record, B: 40K record, try to get the B's 2
> field info into A.
> A and B has 4 common field, those 4 field in B no null values, in A 2 of the
> fileds has null values.
>
> I use join A and B, but it takes me many hours, how should I do to gain the
> speed, I saw sombody do the same thing only take 7 minutes.
>
> Thanks,
> Christina

Christina,

from your posting, I assume that you are using 4 columns from A in the WHERE clause which are ANDed and 2 columns in the SET clause such as this:

update A set (C10, C11) =

      (select C10, C11 from B 
       where B.C1 = A.C1 and B.C2 = A.C2 ...)

To speed up that, the best you can do is to define a proper index on all these 4 columns of your source table B, with the not nullable columns leading.

If for whatever reason you can not do that, your second best choice is to exploit PL/SQL's power. If you are a lucky user of 8i, things should be quite simple because you can take advantage of the bulk collect and bulk binding features. The following example should help (for simplicity, I only include 1 column in the WHERE and 1 in the SET clause, assuming that the corresponding columns have the same names in A and B. You'll have to adjust it for your purpose)

declare

        type t_c1_tab is table of B.C1%type;
        type t_c10_tab is table of B.c10%type;
        v_c1_tab t_c1_tab;
        v_c10_tab t_c10_tab;
begin
        select c1, c10
        bulk collect into v_c1_tab, v_c10_tab
        from B;
        forall i in v_c1_tab.first .. v_c1_tab.last 
                update A set c10 = v_c10_tab(i)
                where c1 = v_c1_tab(i)
        ;

If you are not using 8i yet, you can still achieve the performance gain, but it will be much, much more coding using DBMS_SQL calls (define_array, bind_array)

Good luck and please share the result if you do give it a try.

Bonminh Lam Received on Sun Nov 25 2001 - 11:55:49 CST