Oracle Scratchpad

May 28, 2012


Filed under: Index Rebuilds,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 5:31 pm BST May 28,2012

For those not familiar with Richard Foote’s extensive blog about indexes (and if you’re not you should be) – the title of this note is a blatant hi-jacking of his preferred naming mechanism.

It’s just a short note to remind myself (and my readers) that anything you know about Oracle, and anything published on the Internet – even by Oracle Corp. and its employees – is subject to change without notice (and sometimes without being noticed). I came across one such change today while reading the Expert Oracle Exadata book by Kerry Osborne, Randy Johnson and Tanel Poder. It was just a little throwaway comment on page 429 to the effect that:

In NOARCHIVELOG mode all bulk operations (such as INSERT, APPEND, index REBUILD and ALTER TABLE MOVE) are automatically nologging.

The 2obvious” error there is the reference to “index REBUILD”. Although create table as select and alter table move default to nologging (when running in noarchivelog mode) the equivalent commands for indexes have always been logged. On the other hand, pausing for thought here, I wouldn’t expect such an obvious error to slip past all three authors and the technical reviewers so, before opening my mouth and putting my foot in it, I decided to run a quick test and, almost inevitably, I had an appropriate test script that I’ve been running intermittently for years for exactly this case.

rem     Script:         index_rebuild.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2009

execute snap_redo.start_snap

create table t1
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4 -- > comment to avoid wordpress format issue
        rownum                  id,
        lpad(rownum,10,'0')     small_vc,
        rpad('x',100)           padding
        generator       v1,
        generator       v2
        rownum <= 1e5 -- > comment to avoid wordpress format issue

execute snap_redo.end_snap

execute snap_redo.start_snap
create index t1_i1 on t1(padding);
execute snap_redo.end_snap

execute snap_redo.start_snap
alter index t1_i1 rebuild;
execute snap_redo.end_snap

The snap_redo package is a simple bit of pl/sql I wrote to report the changes in a few of the current session stats (view v$mystat) over time. Specfically it looks at the statistics containing the word redo (and, in a more sophisticated form, a few others related to transaction management). Here are the key results for a test run on and

First from 10g

Create Table
redo synch writes                                                            2
redo entries                                                               410
redo size                                                               71,792
redo wastage                                                             1,836
redo writes                                                                  6
redo blocks written                                                        133
redo ordering marks                                                          3
redo subscn max counts                                                      10

Create Index
redo synch writes                                                            3
redo synch time                                                              2
redo entries                                                             1,887
redo size                                                           13,129,888
redo buffer allocation retries                                               4
redo wastage                                                             2,144
redo writes                                                                 11
redo blocks written                                                     26,428
redo write time                                                             67
redo log space requests                                                      3
redo log space wait time                                                    31
redo ordering marks                                                          3
redo subscn max counts                                                       3

Rebuild index
redo synch writes                                                            2
redo entries                                                             1,978
redo size                                                           13,132,052
redo buffer allocation retries                                               2
redo wastage                                                             4,084
redo writes                                                                 16
redo blocks written                                                     26,453
redo write time                                                             66
redo ordering marks                                                          3
redo subscn max counts                                                       4

Now from 11g

Create Table
redo synch writes                                                            1
redo synch time                                                              1
redo entries                                                               428
redo size                                                               67,228
redo size for direct writes                                              2,912
redo wastage                                                             1,072
redo writes                                                                  5
redo blocks written                                                        138
redo write time                                                              1
redo ordering marks                                                          3
redo subscn max counts                                                       5

Create Index
redo synch writes                                                            1
redo entries                                                               601
redo size                                                               71,092
redo size for direct writes                                             14,916
redo wastage                                                               972
redo writes                                                                  4
redo blocks written                                                        146
redo blocks checksummed by FG (exclusive)                                    6
redo ordering marks                                                          3
redo subscn max counts                                                       4

Rebuild index
redo synch writes                                                            1
redo entries                                                               684
redo size                                                               80,400
redo size for direct writes                                             14,916
redo wastage                                                             1,988
redo writes                                                                  7
redo blocks written                                                        167
redo write time                                                              5
redo blocks checksummed by FG (exclusive)                                    6
redo ordering marks                                                          4
redo subscn max counts                                                       5

As you can see, somewhere between and index creation and rebuild finally became consistent with table creation and move when running in noarchivelog mode. The book was right – it was, after all, talking about Exadata which means it’s implicitly talking about 11g and doesn’t really have to qualify the comment with references to earlier versions.

There’s a secondary moral to this story: instead of saying: “You’re wrong”, you might look a little wiser if you start with “Are you sure about that?” or even “Which version are you thinking of?”

There’s another corollary, of course – if you decide to test out the time and impact of rebuilding a very large index by using a backup copy of your production system, make sure that you are running in archivelog mode or you won’t be doing a test that is anything like valid for the production system (assuming your production systems are running in archivelog mode, of course).


Technically the objects are not created “nologging”, but “unrecoverable” – if you check the logging column 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 the nologging keyword.


  1. […] teh fullpost on my blog. Jonathan […]

    Pingback by Ch-ch-ch-ch-changes – All Things Oracle — May 29, 2012 @ 11:14 am BST May 29,2012 | Reply

  2. WordPress has eaten your “less than”s in the test case listing.

    Comment by Flado — May 29, 2012 @ 1:12 pm BST May 29,2012 | Reply

    • Flado,

      Thanks for that; now fixed. The behaviour doesn’t seem to be consistent (though it probably is). I think I get into trouble when I edit a piece after publishing it.

      Comment by Jonathan Lewis — May 29, 2012 @ 1:25 pm BST May 29,2012 | Reply

  3. I’m proud of you :)

    Nice article, I wasn’t aware of this change either.

    Comment by Richard Foote — May 31, 2012 @ 5:26 am BST May 31,2012 | Reply

  4. […] another one of those little changes that sidles in when you’re not […]

    Pingback by LMT Headers « Oracle Scratchpad — May 31, 2012 @ 5:57 pm BST May 31,2012 | Reply

  5. Execllent find…very subtle.

    Side note:
    If someone says to me, “Are you sure about that,” it comes across as “You’re wrong,” but in a smug way. But you are correct in offering the suggestion to check things out. I’ve said, “You’re wrong” too many times to not do so myself. I’ve also noticed that after I’ve checked things out, I don’t find it necessary to point out the other is wrong … even when they are.

    Comment by Tony C — October 8, 2012 @ 10:55 pm BST Oct 8,2012 | Reply

  6. Jonathan – perhaps it was a test? I could almost imagine those folks leaving it vague on purpose just so they could pick on anyone who happened to call them out on it and getting a bit of a chuckle from it :)

    Comment by Chris Taylor — November 13, 2012 @ 2:02 pm GMT Nov 13,2012 | Reply

  7. […] a very important point to check in this test – if your database is noarchivelog mode the rebuild will be nologging. and you’ll be fooled by the apparent efficiency of the mechanism right up to the point where […]

    Pingback by drop partition | Oracle Scratchpad — August 5, 2022 @ 8:24 pm BST Aug 5,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: