Ch-ch-ch-ch-changes

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
rem     Script:         index_rebuild.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2009
rem

execute snap_redo.start_snap

create table t1
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4 -- > comment to avoid wordpress format issue
)
select
        rownum                  id,
        lpad(rownum,10,'0')     small_vc,
        rpad('x',100)           padding
from
        generator       v1,
        generator       v2
where
        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 10.2.0.3 and 11.1.0.7:

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 10.2.0.3 and 11.1.0.7 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).

Footnote:

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.