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

Staying with the Oracle 8, if the main table was set up as partitioned, there is a command that will merge an external table with a partitioned table as a new partition. I haven't worked with it yet, but if your keys line up properly, it might be faster.

In article <3640FE4A.C2857457_at_fast.net>,   Minh Giang <mpg_at_fast.net> wrote:
> 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
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Nov 05 1998 - 15:01:19 CST