Oracle Scratchpad

July 2, 2010

Unrecoverable

Filed under: Infrastructure,redo — Jonathan Lewis @ 6:38 pm GMT Jul 2,2010

A recent question on the OTN database forum asked: “What’s the difference between index rebuild unrecoverable and nologging?”

The most important difference, of course, is that unrecoverable is a deprecated option so you shouldn’t be using it even though it still works.

Another important difference is that unrecoverable tells you exactly the risk you are taking, while nologging sounds like you are simply managing to avoid doing some work and doesn’t highlight the implicit risk in what you are doing.

There is another interesting difference, though, that makes the nologging option really irritating and an annoying “downgrade” from the unrecoverable option. If you rebuild an index unrecoverable, it is the operation that is unrecoverable – the index state after the rebuild is still logging; if you rebuild the index nologging, both the operation and the state of the index are nologging – you’ve used one word to mean two different things – future rebuilds of the index will be implicitly nologging unless you deliberate modify the index to logging, or explicitly state that the rebuild should be logging.

SQL> select index_name, logging from user_indexes where table_name = 'T3';

INDEX_NAME           LOG
-------------------- ---
T3_I1                YES

SQL> alter index t3_i1 rebuild unrecoverable;

Index altered.

SQL> select index_name, logging from user_indexes where table_name = 'T3';

INDEX_NAME           LOG
-------------------- ---
T3_I1                YES

SQL> alter index t3_i1 rebuild nologging;

Index altered.

SQL> select index_name, logging from user_indexes where table_name = 'T3';

INDEX_NAME           LOG
-------------------- ---
T3_I1                NO

SQL>

4 Comments »

  1. As also with specifying the PARALLEL attribute when creating an index.
    The index continues as a “Parallel enabled” index.

    Hemant K Chitale

    Comment by Hemant K Chitale — July 4, 2010 @ 8:11 am GMT Jul 4,2010 | Reply

    • Hemant,

      Your point is worth mentioning as I remember we had performance issues when a small tables index was rebuild with parallel option and it continued as a parallel enabled index.

      Thanks,
      Balaji

      Comment by Balaji Ramachandran — July 6, 2010 @ 10:30 am GMT Jul 6,2010 | Reply

  2. [...] Lewis explains the difference between unrecoverable and nologging [...]

    Pingback by Log Buffer #194, A Carnival of The Vanities for DBAs | The Pythian Blog — July 12, 2010 @ 4:11 am GMT Jul 12,2010 | Reply

  3. [...] in user_tables and user_indexes, you’ll see that it is set to ‘YES’. But the unrecoverable keyword has been deprecated for many years, even though it doesn’t mean exactly the same as [...]

    Pingback by Ch-ch-ch-ch-changes « Oracle Scratchpad — July 20, 2012 @ 5:02 am GMT Jul 20,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,422 other followers