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

From: Charlene Liang <cliang_at_curagen.com>
Date: 1996/07/09
Message-ID: <31E2E6A9.167E_at_curagen.com>#1/1


Chuck Hamilton wrote:
>
> 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!

I found that it is a lot easier to use incremental export and import.

-- 

Charlene Liang                |  (203) 481-1104 ext. 287
CuraGen Corporation           |
322 E. Main St.               |
Branford, CT 06405            |
cliang_at_curagen.com     |  (FAX) (203) 481-1102
Received on Tue Jul 09 1996 - 00:00:00 CEST

Original text of this message