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.
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.
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...
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!