jump to navigation

Index Block Dump: Index Only Section Part II (Station To Station) October 7, 2010

Posted by Richard Foote in Oracle Indexes.

Finally, we look at the last portion of the index block dump which refers to the actual 3 index entries in our demo that currently reside within the index leaf block we dumped previously.

row#0[8021] flag: ——, lock: 0, len=15
col 0; len 5; (5):  42 4f 57 49 45
col 1; len 6; (6):  02 01 48 8a 00 00
row#1[8002] flag: ——, lock: 0, len=19
col 0; len 9; (9):  4d 41 4a 4f 52 20 54 4f 4d
col 1; len 6; (6):  02 01 48 8a 00 02
row#2[7987] flag: ——, lock: 0, len=15
col 0; len 5; (5):  5a 49 47 47 59
col 1; len 6; (6):  02 01 48 8a 00 01
—– end of leaf block dump —–
End dump data blocks tsn: 8 file#: 8 minblk 84234 maxblk 84234

Note this is a Non-Unique index and so the index entries have a specific format that enables each index entry to still be defined in a unique manner. This is effectively achieved by including the rowid as an additional column within each index entry. This is necessary because Oracle still needs to have an efficient mechanism by which it can find any specific index entry as required. Including the rowid as the last column within an index entry ensures all duplicate index entries are subsequently ordered based on the rowid. Oracle can therefore use the index structure to navigate to the specific index entry of interest if for example an index entry needs to be deleted due to the corresponding row in the table being likewise deleted.

Using the first entry as an example, an index entry basically consists of:

row#0 – A unique row number (starting at 0)
[8021] – Location of the index entry starting at this offset within the block (noting that index blocks are filled “bottom-up”)
flag: ——, lock: 0 – two bytes for flags and locking information (to be discussed more fully later)
len=15 – overall length of the index entry
col 0: – A unique column number for the index entry (again starting at 0)
len 5; (5): – Length of the indexed column
42 4f 57 49 45 – Value of the indexed column (note hex representation of its ASCII value). In this specific case, the value ‘BOWIE’.

Just briefly, if we look at a dump of these records:

SQL> select name, dump(name), rawtohex(name) from bowie;
NAME       DUMP(NAME)                               RAWTOHEX(NAME)
---------- ---------------------------------------- --------------------
BOWIE      Typ=1 Len=5: 66,79,87,73,69              424F574945
ZIGGY      Typ=1 Len=5: 90,73,71,71,89              5A49474759
MAJOR TOM  Typ=1 Len=9: 77,65,74,79,82,32,84,79,77  4D414A4F5220544F4D


We can see that “BOWIE” is represented by database character values 66,79,87,73,69. If we convert these to Hex, we get 42 4f 57 49 45 as shown in the block dump.

This indexed column data is then repeated for each column within the index entry. The second (and in this demo last) column in this example:

col 1; len 6; (6):  02 01 48 8a 00 00

actually represents the 6 bytes for the rowid. As discussed previously, all index entries are effectively made unique by Oracle by including the rowid as an additional column for non-unique indexes.

The index entry portion is then all repeated for each index entry within the index leaf block.

If we to look at the index entries of a unique index as seen in this simple example:

SQL> create table thin_white_duke as select rownum id, 'BOWIE' name from dual connect by level <= 1000;
Table created.
SQL> create unique index thin_white_duke_i on thin_white_duke(id);
Index created.

row#0[8024] flag: ——, lock: 0, len=12, data:(6):  02 01 52 8b 00 1f
col 0; len 3; (3):  c2 06 16
row#1[8012] flag: ——, lock: 0, len=12, data:(6):  02 01 52 8b 00 20
col 0; len 3; (3):  c2 06 17
row#2[8000] flag: ——, lock: 0, len=12, data:(6):  02 01 52 8b 00 21
col 0; len 3; (3):  c2 06 18

We notice a subtle difference in the format of the index entries.

Rather than be stored as an additional column, the rowid is simply an overhead attribute of the index entry. The unique index value itself is sufficient for Oracle to navigate the index structure to efficiently locate any specific index entry of interest and as such, there’s no requirement to specifically include the rowid as an additional index column. This also saves Oracle having to store a length byte associated with the rowid column definition.

Next, we’ll put all this together and see how the distinct sections within an Oracle block are impacted during standard DML operations, in order to implement both Oracle row level locking and read consistency.


No comments yet — be the first.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: