Re: Loading one big table into an even bigger table...
Date: 19 Jul 2001 19:21:28 -0700
Message-ID: <8fe904ce.0107191821.3709125d_at_posting.google.com>
Using the APPEND syntax will not fill the rollback segment space??? Filling the rollback segment space was the problem with doing a straight "insert into select * from" sytax...
Here is an example of what we currently have...
Any suggestions would be appreciated...
PROCEDURE EXAMPLE IS
- Cursor declarations CURSOR csr IS SELECT COL_A, COL_B, COL_C, COL_D, COL_E, COL_F, COL_G, COL_H, COL_I, COL_J, ...(25 COLUMNS) FROM MONTHLY_TABLE;
- Variable declarations intCounter NUMBER := 0;
BEGIN
- Insert all records from Monthly table into Full table
FOR rec IN csr LOOP
INSERT INTO LARGE_TABLE (
COL_A,
COL_B,
COL_C
...(25 COLUMNS)
)
VALUES (
rec.COL_A,
rec.COL_B,
rec.COL_C,
rec.COMM_DT,
...(25 COLUMNS)
);
- Record Counter intCounter := intCounter + 1;
- Commit Every 5000 Records IF intCounter = 5000 THEN COMMIT; intCounter := 0; END IF; END LOOP;
WHEN OTHERS THEN
ROLLBACK;
RAISE ;
END; Would using Export Import using the Conventional mode be quicker even though in Conventional mode it creates insert statements for each record??? (We cannot use direct mode)
"Steve McDaniels" <steve.mcdaniels_at_sierra.com> wrote in message news:<9j4e0r$jm9$1_at_spiney.sierra.com>...
> insert /*+ APPEND */ into big_table
> select * from small_table where ....
>
> if you feel compelled to use PL/SQL then
>
> create or replace procedure big_insert is
> begin
> insert /*+ APPEND */ into big_table
> select * from small_table where ....;
> commit;
> end;
> /
>
> "Simon" <stemp1ar_at_yahoo.com> wrote in message
> news:8fe904ce.0107162023.70bc871b_at_posting.google.com...
> > I am very new to 8i and I would like to enhance a stored proc using
> > some of the features in Oracle 8i.
> >
> > Currently I have a cursor that selects all records from one table and
> > inserts them into another table since the two tables reside within the
> > same schema and are identical(one is a table containing 1 month of
> > data while the other contains all months of data). I dont believe I
> > can use export import because primary key is being created using a
> > trigger with a sequence generator.
> >
> > I have looked at the bulk collect and the problem is the smaller table
> > has _at_ 900,000 records and is growing.
> >
> > What ways would you suggest to speed up the process using oracle 8i
> > features?
Received on Fri Jul 20 2001 - 04:21:28 CEST