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

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 Thu Oct 29 1998 - 02:26:03 CST