|Lesson 5|| Working with larger rows|
|Objective||Allocate overflow space for use by large rows. |
Working with Larger Database Table Rows
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.
To store some of the data outside the leaf node, add the following syntax to a SQL command used to create an index-organized table:
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.
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.
Specifying included columns
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:
Where column is the name of the column that will act as the final column stored in the leaf node.
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
CONSTRAINT pk_coin PRIMARY KEY (coin_id));
PCTTHRESHOLD 50 INCLUDING denomination
OVERFLOW TABLESPACE iot_overflow;
In the next lesson, you will learn about deleting and modifying an index-organized table.