Re: How to do incremental updates (SQL*Loader?)?
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-1102Received on Tue Jul 09 1996 - 00:00:00 CEST