Re: How to update a huge table efficiently
From: Randy Dewoolfson <randyd_at_cais3.cais.com>
Date: 1996/11/10
Message-ID: <565nqq$iqa_at_news2.cais.com>#1/1
: >
: > I need to update a 10 million record table everymonth. About 95% of the
: > records will be updated. I am planning to load the update information to
: > another table, then run a PL/SQL procedure to update the records that
: > are already in the master table or insert the new records that do not
: > exist in the master table. But, probably this will be extremely slow.
: > Any suggestions on how I can go about doing this would be appreciated...
Date: 1996/11/10
Message-ID: <565nqq$iqa_at_news2.cais.com>#1/1
Sten E. Vesterli (sev_at_post3.tele.dk) wrote:
: Tansel Ozkan wrote:
: >
: > I need to update a 10 million record table everymonth. About 95% of the
: > records will be updated. I am planning to load the update information to
: > another table, then run a PL/SQL procedure to update the records that
: > are already in the master table or insert the new records that do not
: > exist in the master table. But, probably this will be extremely slow.
: > Any suggestions on how I can go about doing this would be appreciated...
get the input data as a unix file. sort it using unix sort on the
key column(s)
have an index on the database on the same key columns.
write a pro*c routine that creates a cursor ordered by the key cols.
loop through the input file and the next cursor row (skipping
database entries that dont match) and update the database.
commit every n records so that you dont kill your rollback segs.
Randy :)
--
..uu. Randy DeWoolfson ----------------------
.?$" '?i . I randyd_at_cais.com I
.T^M ._at_" d9 . .f ,.un. t , I--------------------I
" Z :#" M `8 U < .dP"``Y# `M _at_" I brandall_at_erols.com I
&H?` Xl _R $5. $ ?* _at_ 'P,#" I--------------------I
,d#^*L :RP'~$b f`$L:M Xf .f' dH` I ,\//. X I
& 'M ,P `E M "$ Mux~ n!` I |o o| I
dk `h" ' j " y" *~ I=oOO==(_)==Ooo======I
Received on Sun Nov 10 1996 - 00:00:00 CET