Unrecoverable option in oracle 734

[tompk]

Hi,

I want to use UNRECOVERABLE option in oracle 734 to reduce log switches (or redolog entry ) .
Time difference between the log switches is same when i use UNRECOVERABLE option compare to without UNRECOVERABLE option.

Could anybody able to explain about UNRECOVERABLE option
while inserting to a table . How will i make sure thet transaction is not entering to redologs when i use UNRECOVERABLE optiom.


Thanks in advance
Tom.

[Tony Bass]

UNRECOVERABLE



specifies that the creation of the table (and any indices required because of constraints) will not be logged in the redo log file. As a result, media recovery will not recreate the table (and any indices required because of constraints).



This keyword can only be specified with the AS subquery clause. Using this keyword makes table creation faster than using the RECOVERABLE option because redo log entries are not written.

[tompk]

Tony, i agree with that .

My query is how will i make sure that transactions are not going to redolog when i use UNRECOVERABLE option .

My sql statement is like this .

Insert into <tableA>
select
col1,
col2,
col3
from <tableB>@dblink UNRECOVERABLE;

Thanks
tom

[Tony Bass]

I don't think you can use UNRECOVERABLE with Insert in 7.3.4
Oracle8i now has Insert NOLOGGING option.

Can you use the CREATE......AS SELECT ....... UNRECOVERABLE option?

[tompk]

Yes, I am able to do insert with UNRECOVERABLE option.
I just want to make sure that it is not going in to redologs !!!

Thanks
Tom

[mythical1]

as you noted early on... you want to compare the redo-log switching with and without the option.

So...find a reasonbly large table... copy it....then update some field in each record... note or record time before and after for this operation. Note the number of log switches.
Do the same w/UNRECOVERABLE option...

...

[mythical1]

Hi TOMPK..

Real life example are better illustrations...

I have a 5 GB production database (7.3.4). There are several
other "copies" of this for STAGING, TESTING, DEVELOPING, and just plain screwing with it.! Anyways, the developers
_sometimes_ want one of these copies refreshed with production data. I will use EXP/IMP but on the IMP side do not allow IMP to rebuild Indexes because it takes hours.

Instead I extract the index building script from PROD's data dictionary (using fresh extent sizes) AND ALWAYS use UNRECOVERABLE in rebuilding indexes. I cannot quote the time difference due to NOT LOGGING BLOCk changes because I set this up 2 years ago... BUT IT IS SIGNIFICANT!

[tompk]

Hi Micheal,


Thanks for the reply , I did insert to a reasonably large
table with and without UNRECOVERABLE option .
I couldn't find any difference in time on log switches .
( getting almost same time for both)

So i just want to make sure that this transactions(inserts with UNRECOVERABLE option) are not going in to redologs ...

Thanks
Tom.

[tamilselvan]

There is an error in 7.3
Take this example:
Insert into
select
col1,
col2,
col3
from @dblink UNRECOVERABLE;

Here UNRECOVERABLE is used as Alias for the source table.
Not we thought.

The correct syntax is:

CREATE TABLE1 XXX (col1 , col2) UNRECOVERABLE AS SELECT col1, col2 from TABLE2.

[tompk]

Thank you for the info.

I could able to create a table with UNRECOVERABLE option
but i am not able to insert records with unrecoverable option .

I use the below syntax

Insert into table1
values(col1,col2)
Unrecoverable
select col1,col2 from table2@dblink;

Is there any way to insert records with unrecoverable option?

Thanks
Tom