| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Intermittent Commits
Hello,
I think you should keep the solution A. You have 2 possibilities to make it
work for sure.
(A1) You can have a big rolback segment that can sure handle the maximum
transaction size and you attach it to your transaction
SET TRANSACTION USE ROLLBACK SEGMENT rbsbig ;
INSERT INTO a SELECT * FROM B ;
COMMIT ;
You can take the big rollback segment online before the transaction and
offline just after
(A2) If you are using Oracle 9i you can use a new feature called Resumable
Space Allocation.
Before you begin your transaction you ALTER SESSION ENABLE RESUMABLE (or
some thing like that).
The effect is that when you encounter an error such as a rollback segment
that cannot extend, your transaction is suspended instead of rollbacked.
This leaves you the time to solve your problem. When the problem is solved,
the transaction goes on automatically.
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:3F099308.73BE_at_yahoo.com...
> Michael wrote:
> >
> > Hye !
> >
> > I have a query which selects from several tables and inserts
into a
> > table X. Two ways of writing this query are (A) fast but risky (B)
> > slow but certain
> >
> > (A) Writing this select in one bulk INSERT SELECT statement will be
> > the fastest way, however we only commit once at the end. If something
> > wrong happens then nothing will be stored in the new table and in
> > addition we should wait a period of time for the database to rollback
> > its work.
> >
> > (B) Now performing the same INSERT in a cursor and COMMITing after
> > each INSERT is slower but more certain, because if something happens
> > which will stop
> > the query, then it is possible to restart from where we stopped.
> >
> > What I am looking for is a solution in between, that will balance
> > speed and risk for example to commit every chunk of records. The
> > reason for that is not space but time. When this query is run the
> > database should be turned offline and the downtime is minimal. By
> > introducing more commits, it will be much simpler to forecast how much
> > time will it take to perform this INSERT, better feedback of what is
> > happening. Any ideas??
> >
> > I am using Oracle 8i and the INSERT varies from 100000 to 500000
> > records.
> >
> > TIA
> > Michal
>
>
>
 |
 |