Re: index node split autonomy

<ctcgag_at_hotmail.com> wrote in message news:20030525191450.694

> If an DML statement causes a index block to split, and then
> that statement rolls back, is the split also rolled back, or does
> it persist?

Bloody good question!

The split is NOT rolled back, I guess because it would be an awful lot of reverse-work, and if you just do one more insert, we'll probably have to repeat it all over again.

As a simple test:

SQL> create table blah as select * from emp; Table created.

SQL> insert into blah select * from blah; 14 rows created.
SQL> /
28 rows created.
SQL> / [etc etc etc]

SQL> create index idx on blah (ename) pctfree 0; Index created.

SQL> analyze index idx validate structure; Index analyzed.

SQL> select lf_blks from index_stats;

   LF_BLKS

        31

[So, 31 leaf blocks. In another session, I do an insert for a guy called 'MILLERISH'. You can tell I'm in the middle of that insert because in this session, I get this when trying to re-compute the index stats:]

SQL> analyze index idx validate structure; analyze index idx validate structure
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

[Now I issue a rollback of the insert in the other session and try again:}

SQL> analyze index idx validate structure; Index analyzed.

SQL> select lf_blks from index_stats;

   LF_BLKS

        32

[So, despite issuing a rollback, I've still got an extra leaf node which wasn't there before. Therefore, the 'split and acquire' process is not itself rolled back].

Regards
HJR Received on Mon May 26 2003 - 14:39:58 CDT