Re: how to handle updates to millions of rows

You could play around with something like:

    create table C as
    select a.rowid a_rowid, b.rowid b_rowid ...     from a, b
    where ...

    delete from a
    where a.rowid in (select a_rowid from C)

    delete from b
    where b.rowid in (select b_rowid from C)

    insert /*+ append */ into a_and_b_joined     select {all but a_rowid, b_rowid}
    from C;

    commit;

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated July 27th




"Peter Mueller" <lord.of.board_at_gmx.de> wrote in message
news:e3b18ff5.0408270202.7de499a5_at_posting.google.com...


> Unfortunately the merge command is not what I was looking for.

> I just need to find an efficient way to join data from two tables and

> have the original records deleted.

>

> before:

>

>  table a (apx. 8.000.000 records)

>  table b (apx. 8.000.000 records)

>  table a_an_b_joined (0 records)

>

> What I would like to have afterwards:

>

>  table a (apx. 1.000.000 records)

>  table b (apx. 1.000.000 records)

>  table a_an_b_joined (apx. 7.000.000 records + converted currencies)

>

> The procedure has to be run once a month.

> How can I achieve this?

>

> Best regards,

> Peter

>

> DB = ORACLE 9i

>

>

> Frank van Bortel <fvanbortel_at_netscape.net> wrote in message news:

> > In that case: lookup the merge command - it's far more

> > efficient than any PL/SQL!

> > See:

> >

http://asktom.oracle.com/pls/ask/f?p=4950:8:2999541794298325973::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6618304976523,