Index-Organized Tables(二)

1.Block 1

2.Block 2

To contrast data access in a heap-organized table to an index-organized table, suppose block 1 of a heap-organized departments table segment contains rows as follows:

50,Shipping,121,1500

20,Marketing,201,1800

Block 2 contains rows for the same table as follows:

30,Purchasing,114,1700

60,IT,103,1400

A B-tree index leaf block for this heap-organized table contains the following entries, where the first value is the primary key and the second is the rowid:

20,AAAPeXAAFAAAAAyAAD

30,AAAPeXAAFAAAAAyAAA

50,AAAPeXAAFAAAAAyAAC

60,AAAPeXAAFAAAAAyAAB

A scan of the table rows in primary key order reads the table segment blocks in the following sequence:

1.Block 1

2.Block 2

3.Block 1

4.Block 2

Thus, the number of block I/Os in this example is double the number in the index-organized example.

Index-Organized Tables with Row Overflow Area

When creating an index-organized table, you can specify a separate segment as a row overflow area. In index-organized tables, B-tree index entries can be large because they contain an entire row, so a separate segment to contain the entries is useful. In contrast, B-tree entries are usually small because they consist of the key and rowid.

If a row overflow area is specified, then the database can divide a row in an index-organized table into the following parts:

■The index entry

This part contains column values for all the primary key columns, a physical rowid that points to the overflow part of the row, and optionally a few of the non-key columns. This part is stored in the index segment.

■The overflow part

This part contains column values for the remaining non-key columns. This part is stored in the overflow storage area segment.

Logical Rowids and Physical Guesses

Secondary indexes use the logical rowids to locate table rows. A logical rowid includes a physical guess, which is the physical rowid of the index entry when it was first made. Oracle Database can use physical guesses to probe directly into the leaf block of the index-organized table, bypassing the primary key search. When the physical location of a row changes, the logical rowid remains valid even if it contains a physical guess that is stale.

For a heap-organized table, access by a secondary index involves a scan of the secondary index and an additional I/O to fetch the data block containing the row. For index-organized tables, access by a secondary index varies, depending on the use and accuracy of physical guesses:

■Without physical guesses, access involves two index scans: a scan of the secondary index followed by a scan of the primary key index.

■With physical guesses, access depends on their accuracy:

–With accurate physical guesses, access involves a secondary index scan and an additional I/O to fetch the data block containing the row.

–With inaccurate physical guesses, access involves a secondary index scan and an I/O to fetch the wrong data block (as indicated by the guess), followed by an index unique scan of the index organized table by primary key value.

Bitmap Indexes on Index-Organized Tables

A secondary index on an index-organized table can be a bitmap index. As explained in "Bitmap Indexes" on page 3-13, a bitmap index stores a bitmap for each index key.