Allocate overflow Space

In the previous exercise, you created an index-organized table that resulted in a large number of columns.
Although the usage characteristics for the table might have indicated that it be created as an index-organized table, the fact that each row contains so much data creates a problem. Because all the data is stored in the leaf nodes of the index, there may not be very many entries in a single leaf node. There would, however, now be numerous leaf nodes, which, as you have already seen, undercuts some of the performance advantages of the index-organized table. There is a way around this situation. By specifying which data in the table is not stored directly in the leaf node, Oracle will add a pointer to the rest of the data in the row.

PCTTHRESHOLD pct [OVERFLOW TABLESPACE tablespace]

Where pct is an integer that indicates the percentage of space reserved in the block for a row. The value of pct can be 0 to 50. If the value is 50, for example, half the space in a leaf node is reserved for another row, so the leaf node will hold at least two rows.
The optional OVERFLOW TABLESPACE keyword clause is used to indicate a tablespace for holding the additional data in the row. Tablespace is the tablespace used to hold the additional data. Although this clause is optional, if you do not specify a destination for the overflow and a row exceeds the allocated length, Oracle will reject the row.

There may be times when you want explicitly to specify the columns that will occupy the leaf node. You can do this with one additional piece of syntax:

INCLUDING column

To create the COIN table, where you would leave at least 50 percent of the leaf block empty and include the columns up to the denomination column, you would use the following syntax:

CREATE TABLE COIN (coin_id NUMBER, coin_date DATE, 
mint_mark VARCHAR2(20), series VARCHAR2(20), 
denomination  VARCHAR2(10), variety
VARCHAR2(20), grade VARCHAR2(10), 
owner_client_id NUMBER,  country
VARCHAR2(20), 
CONSTRAINT pk_coin PRIMARY KEY (coin_id));
ORGANIZATION INDEX
PCTTHRESHOLD 50 INCLUDING denomination
OVERFLOW TABLESPACE iot_overflow;