Oracle Scratchpad

June 5, 2009

Online Rebuild

Filed under: Index Rebuilds,Indexing,Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 7:44 pm GMT Jun 5,2009

Here’s a little oddity that may be waiting to catch you out – but only if you like to create indexes with very long keys.

create table t1(
	v1	varchar2(4000),
	v2	varchar2(2387),
	v3	varchar2(100)
);

create index t1_i1 on t1(v1, v2);

alter index t1_i1 rebuild;
alter index t1_i1 rebuild online;

My key value is at the limit for an 8KB block size in Oracle 9i and later – which is roughly 80% of (block size – 190 bytes). In earlier versions of Oracle the limit was roughly half that (i.e. 40% rather than 80%). 

If you try to create a longer key you’ll see Oracle error ORA-01450: “maximum key length (6398) exceeded”.  (If you’ve built your indexes using a different blocksize the number in brackets will be different – and you will have to adjust the demo code for 16KB and 32KB block sizes). The difference between my declared column lengths and the error limit relates to the overheads in an index entry – but seems to “forget” the one byte extra for non-unique indexes.

But this is the output you’ll (probably) get from running the script if you’re using an 8KB block size:


Table created.

Index created.

Index altered.

alter index t1_i1 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

So I can create an index with a very large key, rebuild that index safely – and then fail when I try to rebuild the index with the online option!

What’s going on ?

When you use an online index rebuild Oracle has to create a log of all changes you make to the table while the rebuild is running. Once the log is set up, Oracle creates a new index using a read-consistent copy of the data in the table,  then copies the logged changes into this index, and finishes off by tidying up the data dictionary, dropping temporary segments, and dropping the log.

This process locks the table twice, by the way,  once while getting started and creating the log, and then again when finishing off and dopping the log. If you read Richard Foote’s blog regularly you will know that these locks are only held for a short time but can be hard to acquire and may lead to a randomly long delay in the rebuild.

For my purposes, though, the critical feature is  the object that holds the logging information. This is created as an index organized table (IOT) with a name like sys_journal_NNNNN where NNNNN is the object_id of the index you are trying to rebuild. (You can check this by enabling sql_trace just before doing a rebuild.)

It’s the IOT that has the problem with the excess key length – its limit is still set to match the 40% limit used for earlier versions of Oracle, and the key to the IOT is the key defined for the index, plus the rowid for the row with that key.

It’s possible that this outdated limit is simply an oversight in the code; it’s possible that it’s a deliberate design decision that has to stay in place to allow for the different strategies for leaf-block splits adopted by standard B-tree indexes and the index structures supporting IOTs. Either way it’s a limitation that might finally catch you out months after you’ve created an unusually lengthy index key.

Just as a quick demonstration that the problem is in the IOT, we need only run a simple piece of DDL (the sample below was cut and pasted from a live session):


SQL> create table iot1(
  2     v1      varchar2(4000),
  3     v2      varchar2(2380),
  4     v3      varchar2(100),
  5     constraint iot1_pk primary key(v1,v2)
  6  )
  7  organization index
  8  overflow
  9  ;
create table iot1(
*
ERROR at line 1:
ORA-01450: maximum key length (3215) exceeded

Of course, when you see little oddities like this you might think: “I wouldn’t create an index like that – so I’m not worried”. But it’s worth spending a little time looking at the symptoms from different directions before dismissing the problem.

Try this experiment (starting with the same t1 heap table above) but see if you can guess what’s going to happen and why … before you read the explanation:


create or replace function f (i_in varchar2)
return varchar2
deterministic
as
begin
 return i_in;
end;
/

create index t1_f1 on t1(f(v3));

alter index t1_f1 rebuild;
alter index t1_f1 rebuild online;

You can rebuild the index but you can’t rebuild it online because you get the same Oracle error ORA-01450.

You didn’t build an index with a large key deliberately, but a function returning a varchar2() implicitly returns a 4,000 byte string – so your index has been built on a virtual column of 4,000 bytes.

select
 column_name, column_length
from
 user_ind_columns
where
 index_name = 'T1_F1'
/

COLUMN_NAME          COLUMN_LENGTH
-------------------- -------------
SYS_NC00004$                  4000

1 row selected.

If you want to build this type of index, and be able to rebuild it online, your index definition will have to be something like:

drop index t1_f1;
create index t1_f1 on t1(substr(f(v3),1,100));
alter index t1_f1 rebuild online;

With this definition, the function-based index can be rebuilt online. (Of course, it may no longer be the index you want, and some of your SQL has to be modified so that the predicates match the new index definition.) 

Footnote 1: The 40% limit on IOT keys is further constrained if you are using a 16KB block size by a “hard” limit of 3,800 bytes – so rebuilding the index with a different blocksize will only work around the problem in a limited range of cases.

Footnote 2: Although I implied at the start of the article that it would be unusual to have very long index keys there are quite a lot of applications that define tables with fairly long (e.g. 254, 1000) varchar2() columns: “just in case”.  Watch out when you start creating indexes on these columns.

[Further reading on rebuilding indexes]

17 Comments »

  1. Jonathan,

    this can be even more subtle: The index-organized table used for logging the changes to the table adds some columns to the original index expression (I think usually at least three: OPCODE, PARTNO, RID) which means that you might have the case where the index expression itself can be represented by an IOT even with the 40% limit, but due to the columns added to the IOT used for the rebuild the limit is exceeded.

    So this is another case where a rebuild “offline” succeeds, even an IOT can be built from the index expression, but the rebuild online fails.

    Regards,
    Randolf

    Comment by Randolf Geist — June 5, 2009 @ 9:05 pm GMT Jun 5,2009 | Reply

    • Randolf,

      I haven’t checked it down to the last byte, but the IOT has a primary key of (original_key, old_rowid) while the other columns (opcode and partition number) are not in the primary key.

      It seems fairly likely that the “40%” limit relates to the primary key, rather than the entire row – even so the presence of the rowid will, as you say, make a little difference.

      Comment by Jonathan Lewis — June 6, 2009 @ 3:54 pm GMT Jun 6,2009 | Reply

      • Jonathan,

        I’ve just done a quick test on 10.2.0.4 and 11.1.0.7 Win32, because what I’ve written above was off the top of my head… And the results were the following:

        Using a 8KB blocksize tablespace (LMT MSSM) the entire row size of the IOT was limited to (something very close to) 3215 bytes, if there was no overflow segment defined (Error ORA-01429: no data segment to store overflow row-pieces). So the limitation seems to apply to the entire row, not only the primary key (besides the option to have an overflow segment of course).

        What seemed to be quite puzzling to me: When I engineered a case where the primary key of the IOT logging table was supposed to be below the 3215 bytes maximum key length limit, but the entire row length including the partition number and the opcode was supposed to exceed it, I expected to get a recursive ORA-01429 error when running the online rebuild, but it happily succeeded.

        However when I tried to manually execute the CREATE TABLE statement for the IOT journaling table (taken from the SQL trace file), it didn’t include any overflow information and I got the ORA-01429 error… Even when checking the created IOT table while the rebuild was going on I couldn’t find any overflow segment. Not sure where I went wrong in my test case, so if I find some more time, I’ll visit this again.

        Regards,
        Randolf

        Comment by Randolf Geist — June 6, 2009 @ 9:08 pm GMT Jun 6,2009 | Reply

      • Jonathan,

        trying to be a good Oakie I realized I should have posted what I’ve tried. Here’s my testcase run on 10.2.0.4 / 11.1.0.7 Win32 in a 8KB LMT MSSM tablespace.

        Note that I’ve tried to follow your advices how to keep the formatting, apologies if this shows up as crap.


        -- This works
        create table test_iot (
        c1 varchar2(10) not null,
        c2 varchar2(2000) not null,
        c3 varchar2(1199) not null,
        primary key (c1, c2, c3)
        )
        organization index;

        drop table test_iot purge;

        -- This fails with
        -- ORA-01450
        create table test_iot (
        c1 varchar2(10) not null,
        c2 varchar2(2000) not null,
        c3 varchar2(1200) not null,
        primary key (c1, c2, c3)
        )
        organization index;

        drop table test_iot purge;

        -- This works
        create table test_iot (
        c1 varchar2(10) not null,
        c2 varchar2(2000) not null,
        c3 varchar2(1200) not null,
        primary key (c1)
        )
        organization index;

        drop table test_iot purge;

        -- This fails with
        -- ORA-01429
        create table test_iot (
        c1 varchar2(10) not null,
        c2 varchar2(2000) not null,
        c3 varchar2(1201) not null,
        primary key (c1)
        )
        organization index;

        drop table test_iot purge;

        -- This works
        create table test_iot (
        c1 varchar2(10) not null,
        c2 varchar2(2000) not null,
        c3 varchar2(1188) not null,
        rid rowid not null,
        primary key (c1, c2, c3, rid)
        )
        organization index;

        drop table test_iot purge;

        -- This fails
        -- with ORA-1429
        create table test_iot (
        c1 varchar2(10) not null,
        c2 varchar2(2000) not null,
        c3 varchar2(1188) not null,
        opcode char(1),
        partno number,
        rid rowid not null,
        primary key (c1, c2, c3, rid)
        )
        organization index;

        drop table test_heap purge;

        create table test_heap (
        c1 varchar2(10) not null,
        c2 varchar2(2000) not null,
        c3 varchar2(1188) not null
        );

        insert into test_heap (
        c1, c2, c3)
        select
                rpad('1', 10, '1') as c1
              , rpad('2', 2000, '2') as c2
              , rpad('3', 1188, '3') as c3
        from
                dual
        connect by
                level <= 1000;

        commit;

        exec dbms_stats.gather_table_stats(null, 'test_heap')

        create index test_heap_idx on test_heap (c1, c2, c3);

        alter session set tracefile_identifier = 'index_rebuild_online_non_unique';

        alter session set sql_trace = true;

        alter index test_heap_idx rebuild online;

        alter session set sql_trace = false;

        -- this is taken from SQL trace
        -- but fails with ORA-1429 as expected
        -- but the rebuild online works
        CREATE TABLE "CBO_TEST"."SYS_JOURNAL_62584"
        (
        C0     VARCHAR2(10)  ,
        C1     VARCHAR2(2000),
        C2     VARCHAR2(1188),
        opcode CHAR(1)       ,
        partno NUMBER        ,
        rid rowid            ,
        PRIMARY KEY( C0, C1, C2 , rid )
        )
        organization INDEX TABLESPACE "TEST_8K";

        Comment by Randolf Geist — June 7, 2009 @ 10:33 am GMT Jun 7,2009 | Reply

  2. it seems to be nice if we could

    create or replace function f (i_in varchar2)
    return varchar2(100)
    deterministic
    as
    begin
    return cast(i_in as varchar2(100));
    end;
    /

    i wonder why we can’t

    Comment by Sokrates — June 6, 2009 @ 4:50 pm GMT Jun 6,2009 | Reply

  3. Jonathan,

    Nice post!! One of the problems with online rebuild is that if you are trying to do online rebuild for table with high dml activity and happen to cancel the online rebuild (Ctrl+C),then the Journal table does not get dropped as Smon is unable to clear it (due to high DML on base table). Due to this you won’t be able to drop or rebuild index (not online) and get a error message that “online rebuild in progress”. You will have to wait till smon clears it. . Slightly off topic from your post but wanted to highlight to readers that they should use online rebuild option judiciously and try never to cancel it. (My experiences :-) )

    Comment by Amit — June 6, 2009 @ 6:48 pm GMT Jun 6,2009 | Reply

    • Amit,

      Thanks for the warning.
      Is this something where smon does a couple of “nowait lock” attempts and then gives up for five minutes ? If so could you keep sending it an oradebug wakeup call until it succeeds ?

      Comment by Jonathan Lewis — June 10, 2009 @ 7:27 pm GMT Jun 10,2009 | Reply

  4. Interesting. I encountered just this problem several months ago at a client site (actually while creating indexes online), but never bothered to figure out the cause.

    Wonderful explanation.

    Happily, in this particular case, the tables involved were not huge (although they were incredibly wide), so obtaining table locks for the couple seconds duration of a normal (vs. “online”) index build/rebuild was not a major problem.

    This is indeed a major potential gotcha, though, for large tables where building an index may take many minutes or hours.

    Comment by Mark Brinsmead — July 4, 2009 @ 5:24 am GMT Jul 4,2009 | Reply

  5. [...] key不能同时跨越多个block, 详细描述可以参考Jonathan Lewis的online-rebuild 以及metalin的notes 136158.1. This index key size is limited by the value of db_block_size, [...]

    Pingback by 关于ORA-01450错误的一点测试与说明. » a db thinker's home — January 27, 2010 @ 9:15 am GMT Jan 27,2010 | Reply

  6. [...] 对于第二种情况,Jonathan Lewis有一篇l文章:online rebuid探讨了这个问题。之所以把在线重建索引跟IOT表的创建归结为一类是因为他们都是IOT的创建问题,首先你应该明白索引重建的原理: [...]

    Pingback by 关于ORA-01450的一点探讨 - Baallord's DBA Life - Oracle DBA — July 19, 2010 @ 8:42 am GMT Jul 19,2010 | Reply

  7. Hi can we do ALTER INDEX REBUILD ONLINE for 8i databases dose it work, if so any issues need to be considered.

    Comment by srini — April 21, 2011 @ 8:31 pm GMT Apr 21,2011 | Reply

    • Srini,

      If you need to be able to do this then you probably have access to an 8i database – which means you create create a table with an index and find out if it is possible almost as quickly as posting a question here.

      The request about issues to consider is more appropriate – but apart from the standard comments I make about all index rebuilds, the fact that I can’t think of any special case for 8i at the moment doesn’t mean there aren’t any. In your position, I would have gone to MOS (metalink) and downloaded the patch set note for 9i to check for any patches relating to online index rebuilds. Patches in newer versions tell you about problems in older versions.

      Comment by Jonathan Lewis — April 24, 2011 @ 9:49 am GMT Apr 24,2011 | Reply

  8. [...] 对于这个问题,旺旺同学和Jonathan Lewis同学很早就有描述,只是没碰到一般很少碰到key这么长的索引而不容易注意到,再次记录下备忘。 [...]

    Pingback by 偶遇ORA-01450 — February 29, 2012 @ 5:35 am GMT Feb 29,2012 | Reply

  9. [...] some unexpected internal coding mechanism hits a boundary condition. One example I gave of this was rebuilding indexes online – where a limitation on the key size of index organized tables made it impossible to do an [...]

    Pingback by Surprises « Oracle Scratchpad — August 30, 2012 @ 5:55 pm GMT Aug 30,2012 | Reply

  10. […] to an earlier post, online rebuild works in 12c even when the key is “too long”. The internal code has […]

    Pingback by Index rebuild 12c | Oracle Scratchpad — June 28, 2013 @ 8:35 am GMT Jun 28,2013 | 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,306 other followers