Database Reference
In-Depth Information
5 constraint iot_pk primary key (x)
6 )
7 organization index
8 pctthreshold 10
9 overflow
10 /
Table created.
Graphically, it could look like Figure 10-6 .
1,01-JAN-01,Small Data
2,01-JAN-01,Small Data
3,01-JAN-01,<pointer>
4,01-JAN-01,Small Data
5,01-JAN-01,<pointer>
6,01-JAN-01,Small Data
1,000 Bytes
of Data
880 Bytes
of Data
Figure 10-6. IOT with overflow segment, PCTTHRESHOLD clause
The gray boxes are the index entries, part of a larger index structure (in Chapter 11 you'll see a larger picture of
what an index looks like). Briefly, the index structure is a tree, and the leaf blocks (where the data is stored) are, in
effect, a doubly linked list to make it easier to traverse the nodes in order once we've found where we want to start in
the index. The white box represents an OVERFLOW segment. This is where data that exceeds our PCTTHRESHOLD setting
will be stored. Oracle will work backward from the last column up to but not including the last column of the primary
key to find out what columns need to be stored in the overflow segment. In this example, the number column X and
the date column Y will always fit in the index block. The last column, Z , is of varying length. When it is less than about
190 bytes or so (10 percent of a 2KB block is about 200 bytes; subtract 7 bytes for the date and 3 to 5 for the number),
it will be stored on the index block. When it exceeds 190 bytes, Oracle will store the data for Z in the overflow segment
and set up a pointer (a rowid, in fact) to it.
The other option is to use the INCLUDING clause. Here we are stating explicitly what columns we want stored on
the index block and which should be stored in the overflow. Given a CREATE TABLE statement like this
EODA@ORA12CR1> create table iot
2 ( x int,
3 y date,
4 z varchar2(2000),
5 constraint iot_pk primary key (x)
6 )
7 organization index
Search WWH ::




Custom Search