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

sfedor01_at_serss1.fiu.edu (Sergey V. Fedorishin) wrote:

>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

We had exactly the same problem here. Some of our tables run > 4 million rows. Our solution was to use sqlload to load just the changed rows into a delta table and update the main table from there. In the case of a new row, we insert it. In the case of a changed row, we delete it and reinsert it.

delete from mytable
  where rowid in
  (select b.rowid from delta$mytable a, mytable b   where b.key = a.key);

insert into MHP.CLAIM_LINE (select * from MHP.UPDT$CLAIM_LINE); commit;

It may seem like the WHERE criteria on the DELETE is a bit strange but trust me, it works much faster than "where exists". At least in this case it does. It forces the full table scan to occur on the delta$ table and not the main table (mytable).

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!