Re: How to do incremental updates (SQL*Loader?)?

From: Sergey V. Fedorishin <sfedor01_at_serss1.fiu.edu>
Date: 1996/07/09
Message-ID: <4rtm44$16_at_isis.fiu.edu>#1/1


Francisco Piragibe (piragibe_at_esquadro.com.br) wrote:
: Ofer Inbar wrote:
: >
: > I'm supporting a site that will be keeping an Oracle database
: > synchronized with another database running on a VMS machine. The've
: > got programs to extract the necessary information from the VMS db, and
: > automatically create .sql, .ctl, and .dat files, which they ftp to the
: > Unix box (an HP 9000 K210 running HP-UX 10.10 and Oracle 7.3.2) and
: > load using SQL*Loader. This is working great for the initial transfer.
: >
: > The problem is keeping things up to date. These tables are large, and
: > some of them take hours to load. They're going to want to do a
: > nightly update, with only the changed data. They can have their
: > extract programs output .ctl and .dat files containing only new data
: > in the .dat file, in a format compatible with SQL*Loader and the .ctl
: > file. However, this will be a mix of new records and existing records
: > whose data has been altered. None of the SQL*Loader options (INSERT
: > INTO TABLE, REPLACE, TRUNCATE, etc.) seem to work with this.
: >
: > Any suggestions?

: Ofer
 

: Unfortunately, you just can't do that. SQL*Loader is meant to be used in huge data loads.
: It's somewhat fast, but quite inflexible.
 

: You might overcome this by making your incremental load a two step process: load the data
: to temporary tables and put them on the real ones through PL/SQL procedures. If this
: shows to be too slow for your needs, you'll have to build PRO*C or PRO*something
: programmes...

At least Loader can help you to filter the old records out, if they have unique key. Just use APPEND and see how the brand new rows come in and old stuff goes into *bad file due to integrity constraint violation. Then use *bad file as input to any non-Loader program to update data.

HTH
-Sergey. Received on Tue Jul 09 1996 - 00:00:00 CEST

Original text of this message