Insert without redo logs or RBS???

Is this possible? I am doind a huge data transfer from Table_1 to Table_2. Is it possible to do this without it being logged or being written to the RBS for a speed increase?

John

RBS will be used in either case. Alter the target table to NOLOGGING, and this will not generate any redo during the inserts. When done, change it back to LOGGING. Commit regularly so that the RBS won't grow too big.

i issued ALTER TABLE TABLENMAE NOLOGGING

then i started the inseret, but the redo writer is still being accessed. i am the only one in the database.

Is it active beacuse the RBS is being written to? or do I have to issue another command?

thanks man, you are very helpful

John

RBS will be used regardless of if you do periodic commit in NOLOGGING mode or not. RBS is used to present a consistent view of the data from the beginning of your transaction to the end.

But is RBS causing the log writer to stay active or am i still utilizing the log files also?

ythansk jophn

I think its because of the RBS. Check to see if there are any frequent log switches over say 30 min to 1hr while the inserts are taking place. You are not quite using the redo log files in actual terms. Don't worry about LGWR being active.

[Edited by Halo on 11-21-2000 at 06:24 PM]

Yes, changes to the RBS tablespace are just like any other (more or less). They are logged to your redo log files.

By dedicating a couple of devices to your redo logs, you can minimize the impact of LGWR. If your LGWR is spinning all the time, you may want to check to see if LGWR is checkpointing too much (Checkpoint not complete message in alert.log) or you may need to tune your redo activity.

you can only use nologging option if you do direct inserts

first alter the table to nologging attribute
then

insert /*+ append */ into xxxx
select * from yyyy;

This wont generate redo log
direct insert is insert.... select....
there is no way you can avoid logging in a normal insertion, only in a direct insert.
And there is no way you can avoid using RBS... as far as I know.

Just to add some note, if you do direct inserts you will start inserting after High Water Mark because of APPEND hint, however APPEND is required for direct inserts. This will work better if you are doing it to a temporary truncated table

Hi

With nologging we cannot eliminate redo completely . we can only reduce it . i tested it on some tables and still found redo creation . What is the problem here? Any idea????


Santosh

You can try out the Copy command.

NOLOGGING will not egenrate redos for data but will always generate logging for dictionary changes, those changes you see are dictionary changes, also the new extents which are been allocated are being marked invalid as well. This is because during a recovery Oracle wont recover those extents marked invalid, invalid extents are basically extents without redo loggings.