Database Tables - Expert Oracle Database Architecture - page 383

8 including y
9 overflow
10 /
Table created.

what we can expect to find is illustrated in Figure 10-7 .

1,01-JAN-01,<pointer>
2,01-JAN-01,<pointer>
3,01-JAN-01,<pointer>

4,01-JAN-01,<pointer>
5,01-JAN-01,<pointer>
6,01-JAN-01,<pointer>

n Bytes of Data n Bytes of Data n Bytes of Data

n Bytes of Data n Bytes of Data n Bytes of Data

Figure 10-7. IOT with OVERFLOW segment, INCLUDING clause

In this situation, regardless of the size of the data stored in it, Z will be stored out of line in the overflow segment
(all nonprimary key columns that follow the column specified in the INCLUDING clause are stored in the overflow
segment).
Which is better then: PCTTHRESHOLD , INCLUDING , or some combination of both? It depends on your needs. If you
have an application that always, or almost always, uses the first four columns of a table and rarely accesses the last five
columns, using INCLUDING would be appropriate. You would include up to the fourth column and let the other five
be stored out of line. At runtime, if you need them, the columns will be retrieved in much the same way as a chained
row would be. Oracle will read the head of the row, find the pointer to the rest of the row, and then read that. If, on
the other hand, you cannot say that you almost always access these columns and hardly ever access those columns,
you should give some consideration to PCTTHRESHOLD . Setting PCTTHRESHOLD is easy once you determine the number
of rows you would like to store per index block on average. Suppose you wanted 20 rows per index block. Well, that
means each row should be one-twentieth (5 percent). Your PCTTHRESHOLD would be 5, and each chunk of the row that
stays on the index leaf block should consume no more than 5 percent of the block.
The last thing to consider with IOTs is indexing. You can have an index on IOTs themselves—sort of like having
an index on an index. These are called secondary indexes . Normally, an index contains the physical address of the
row it points to, the rowid. An IOT secondary index cannot do this; it must use some other way to address the row.
This is because a row in an IOT can move around a lot, and it does not migrate in the way a row in a heap organized
table would. A row in an IOT is expected to be at some position in the index structure, based on its primary key
value; it will only be moving because the size and shape of the index itself is changing. (We'll cover more about how
index structures are maintained in the next chapter.) To accommodate this, Oracle introduced a logical rowid . These
logical rowids are based on the IOT's primary key. They may also contain a guess as to the current location of the
row, although this guess is almost always wrong because after a short while, data in an IOT tends to move. The guess
is the physical address of the row in the IOT when it was first placed into the secondary index structure. If the row in