Index too big

I thought I’d posted this a couple of years ago – but maybe it was something I put on the Oracle database forum in response to a question. If it was on the forum the same (or similar) question has recently appeared.  “How come my index is so big when there’s no data in the table ?”

Of course, the amount of space currently allocated to an object may have nothing to do with the current amount of data stored in the object – after all, you may have executed a “delete tableX” command moments ago – but it’s worth being aware of a little trap that may result in an apparent sizing anomaly all the time. To get things going, consider the following code fragment that creates an indexed table, then does a little data processing of a style that I sometimes see in overnight batch processes:

rem
rem     Script:         index_too_big.sql
rem     Dated:          March 2010
rem     Author:         Jonathan Lewis
rem

create table t1 (v1 varchar2(400));
create index i1 on t1(v1);

begin
        for i in 1..1000 loop
                insert into t1 values (lpad(i,400));
                delete from t1;
        end loop;
        commit;
end;
/

Assume this is in a database with an 8KB blocksize, and the default tablespace is locally managed with uniform 1MB extents using freelist management rather than ASSM (automatic segment space management).

Question

Roughly how many blocks would you expect to be below the table and index highwater marks at the end of execution ?

Answer

Plenty of good answers in the comments below. Here’s a summary of the key points.

• Oracle cannot reuse the space made available from deletes until after the commit; however tables and indexes are handled differently.
• Tables need only keep a “stub” to represent the row that has been marked for deletion, but indexes have to keep the entire index entry.

As a result, the amount of space “held” until the commit will be much larger in this case for the index than it is for the table.

All 1,000 row stubs will fit into a single 8KB block quite easily, so the table never grows beyond one block. Various people have done arithmetic for the index – and an approximation is good enough. Each entry is about 400 bytes (plus a bit) so we will get about 20 to a leaf block, which means we need about 50 leaf blocks, so we need about 50 branch entries. The branch entries will also be about 400 bytes, so 20 per branch block – so we need three branch blocks and a root block above them.

As we bump the highwater mark on the table and index, though, we may get a few more blocks than we finally use – so the total below the highwater mark may be a little higher. (The purpose of the little puzzle was to highlight the differences between table and index deletion, so I wasn’t planning to mention that originally – but Charles Hooper picked it up in his comment.)

I’ll just close with a couple of trace file dumps from a 9.2.0.8 system after running the code:

Segment header block of the table.
Note “blocks in seg header’s freelists = 1”, “blocks below hwm = 1”. There is one block in the table, and it’s still not full despite having 1,000 row directory entries in it – so it’s still on the freelist.

Start dump data blocks tsn: 12 file#: 11 minblk 9 maxblk 9
buffer tsn: 12 rdba: 0x02c00009 (11/9)
scn: 0x0000.03e52147 seq: 0x01 flg: 0x00 tail: 0x21471001
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 127
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x02c0000b ext#: 0 blk#: 1 ext size: 127
#blocks in seg. hdr's freelists: 1
#blocks below: 1
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 45209 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x02c0000a length: 127

nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 1
SEG LST:: flg: USED lhd: 0x02c0000a ltl: 0x02c0000a
End dump data blocks tsn: 12 file#: 11 minblk 9 maxblk 9

Partial segment header block of the index.
Note that the HWM is at block 63, but we appear to have 53 blocks on the freelist (that’s the empty leaf blocks after the commit, but not the branch blocks). I can’t quite make the numbers add up the way I expected them to, and that’s a detail I may have to look into some day.

Start dump data blocks tsn: 12 file#: 11 minblk 137 maxblk 137
buffer tsn: 12 rdba: 0x02c00089 (11/137)
scn: 0x0000.03e52566 seq: 0x01 flg: 0x00 tail: 0x25661001
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 127
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x02c000c9 ext#: 0 blk#: 63 ext size: 127
#blocks in seg. hdr's freelists: 53
#blocks below: 63
mapblk 0x00000000 offset: 0

Treedump of the index – with the “leaf:” lines deleted.
Note the root and three branch blocks; and the 53 leaf blocks (20 + 20 + 13) in total listed by the branches.

----- begin tree dump
branch: 0x2c0008a 46137482 (0: nrow: 3, level: 2)
branch: 0x2c000a5 46137509 (-1: nrow: 20, level: 1)
branch: 0x2c000a6 46137510 (0: nrow: 20, level: 1)
branch: 0x2c000bb 46137531 (1: nrow: 13, level: 1)

Supplementary Question – for those with nothing better to do this weekend

Each time I delete a row from the table its “delete flag” is set. At some point, though, Oracle wants to re-use the space taken by the row and leave just a “stub” to show where the row should be if I roll back. When, and therefore how often, in my test case does Oracle remove the actual rows and replace them with stubs ? (See “Heap Block Compress” for answers.)