Oracle 9.2.0.8
There is a large table TRANSACTIONS (2 billion rows, 160 GB), and has VARCHAR2(10) column TRANSACTION_ID. The column has approx 200 million distinct values.
There is a single-column index on the column.
A user executed UPDATE that set all rows to a single value: UPDATE TRANSACTIONS SET TRANSACTION_ID = :value (we don't know the value as this database has cursor sharing enabled).
The UPDATE failed (probably run out of space in UNDO) and it was rolled back. Then we noticed that for a certain value of TRANSACTION_ID the query using the index is very slow.
Even the simplified query that accesses only index took 40 min and returned only one row:
SELECT TRANSACTION_ID FROM TRANSACTION WHERE TRANSACTION_ID = '12345ABCDE';
Also the size of index increased by 20%.
After tracing the query we found that it spends time on 'db file sequential read' (as expected). Using p1, p2 from the trace file and doing block dump we found that the index contains thousands of "blank" blocks - example below.
These blank blocks don't have "rows" section. Once the index was rebuilt the issue disappeared.
Question: is it normal for "blank" blocks to appear after large transaction that was rolled back? I had a look at Richard Foote's blog but could't find any references to this behaviour.
Example of blank block:
Leaf block dump
===============
header address 4350311420=0x1034c7bfc
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 4104=0x1008
kdxcoavs 7588
kdxlespl 0
kdxlende 0
kdxlenxt 2135123197=0x7f4364fd
kdxleprv 2135123073=0x7f436481
kdxledsz 0
kdxlebksz 7624
----- end of leaf block dump -----
End dump data blocks tsn: 21 file#: 509 minblk 222399 maxblk 222399