Oracle unrecoverable tips Oracle Tips by Burleson Consulting

Please note that the UNRECOVERABLE option has been deprecated and replaced with the NOLOGGING option.

Using Oracle’s UNRECOVERABLE Option

At specific times, Oracle allows its internal transaction logging mechanism to be turned off. With Oracle, the software maintains a read-consistent image of the data for long-running queries, while at the same time providing rollback capability for each update transaction. Of course, this level of recoverability carries a price tag, and significant performance improvements can be achieved with the prudent use of the “unrecoverable” option. In practice, use of the unrecoverable clause will improve response time from 40to 60 percent. Care needs to be taken, of course, to synchronize the use of the unrecoverable clause with the traditional procedures used when taking backups of the archived redo logs.

Unrecoverable can be used for any of the following operations:

* Create table . . . as select . . . unrecoverable

-This type of operation is generally performed when a table is “cloned” or replicated from a master table, usually with a subset of columns and rows. For example, we would use this command to create a subset of a customer table, containing only those customers in a specific region. Of course, a failure during the creation of the table would leave a half-built table in the destination tablespace, and the table would need to be manually dropped.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.