Re: What is the fastes way to merge two tables?

The 2nd method works fine if:

1. you have plenty of space available to work with (since the first table is very large). Since you're creating a third table, you'll have re-create the index(es), drop the first table, coalesce the tablespace, and rename the third table back to the first.
2. The 2nd table or temporary is fairly large, if it has a small amount of data, it better with the first method.
3. The combined data from the 1st and 2nd table must be unique to use UNION ALL (This avoids sorting), otherwise the create statement would failed and you'll have to use UNION.

I don't know how often you insert new data to this huge table and how much historical data you keep.
If it's a monthly update, you can create new table every month and sqlload directly into this table. Then create or update a view that points to these tables.

Minh

Billy Verreynne wrote:

> gilhirsch_at_my-dejanews.com wrote in message
> <7173c8$veb$1_at_nnrp1.dejanews.com>...
> >
> > I'm working with a large (10's of millions) indexed table, and I'm
> >interested in loading new data to a temporary table, to avoid locking the
> >large table (I'm using Direct Path loading). To keep up with the high rate
> of
> >incoming records (~1000/sec), I need to use the fastest way for inserting
> the
> >records from the temporary table to the larger one.
>
> There's two ways to merge 2 tables via standard SQL:
>
> --
> 1st method.
> INSERT INTO table1 SELECT * FROM table2.
>
> Will be faster if you drop indexes and disable contraints on table1 before
> doing this. With Oracle8 INSERTs can also be done with parallel query I
> believe.
> --
>
> --
> 2nd method.
> CREATE TABLE table3
> UNRECOVERABLE AS
> SELECT * FROM table1
> UNION ALL
> SELECT * FROM table2
>
> Remember to add the storage clause. This method will be very fast if you use
> Parallel Query (PQ) to create table3:
> CREATE TABLE table3
> PARALLEL (DEGREE 4)
> UNRECOVERABLE AS
> SELECT /*+parallel(table1,4) */ * FROM table1
> UNION ALL
> SELECT /*+parallel(table1,4) */ * FROM table2
>
> Hmm.. Not sure if the SELECTs should be degree 2 instead. As only 4 create
> PQs are running you ideally would want 4 select PQs to feed them with data.
> I've never had to this type of thing with a union - we usually had to join a
> whole bunch of tables.
> --
>
> Hopes this help.
>
> regards,
> Billy
Received on Wed Nov 04 1998 - 19:24:26 CST