| Lesson 5 | Working with larger rows |
| Objective | Allocate overflow space for use by large rows. |
Working with Larger Database Table Rows to 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 undercut 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.
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 undercut 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]` clause in Oracle 19c
The `PCTTHRESHOLD pct [OVERFLOW TABLESPACE tablespace]` clause is still used in Oracle 19c for Index-Organized Tables (IOTs) even when all data is stored in the leaf nodes. Here's why:
However, the behavior of `PCTTHRESHOLD` differs slightly based on whether you explicitly define an overflow tablespace:
In summary, even with complete leaf node storage, `PCTTHRESHOLD` remains relevant in Oracle 19c for handling large rows, maintaining efficiency, and future-proofing your IOTs.
- Overflow for Large Rows: Even though all data is stored in leaf nodes, it's possible for individual rows to be larger than the available space in a single leaf block. In such cases, the `PCTTHRESHOLD` parameter determines how much of the row (including the key) is stored in the main leaf block and the remaining data is stored in an overflow segment. This prevents performance degradation due to excessive fragmentation.
- Efficiency Considerations: While storing all data in a single leaf might seem ideal, it can create performance issues when fetching small amounts of data or performing partial updates. Overflow allows efficient retrieval of specific columns without fetching the entire row, saving I/O operations.
- Future Growth: While your current data size fits within leaf blocks, future data growth might exceed the block size. Using `PCTTHRESHOLD` proactively avoids potential issues down the line and provides an easy way to handle unexpected large rows.
However, the behavior of `PCTTHRESHOLD` differs slightly based on whether you explicitly define an overflow tablespace:
- No Overflow Tablespace: If you omit the `OVERFLOW TABLESPACE` clause, you'll get an error if a row exceeds the threshold limit. This forces you to either adjust the threshold or create an overflow segment.
- Overflow Tablespace Defined: If you specify an overflow tablespace, rows exceeding the threshold will be split and stored partly in the leaf block and partly in the overflow segment. This allows larger rows to be accommodated without data loss.
In summary, even with complete leaf node storage, `PCTTHRESHOLD` remains relevant in Oracle 19c for handling large rows, maintaining efficiency, and future-proofing your IOTs.
Syntax to store some of the data outside the Leaf Node
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:
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
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.
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.
Example
INCLUDING column
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
In the next lesson, you will learn about deleting and modifying an index-organized table.
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;
In the next lesson, you will learn about deleting and modifying an index-organized table.
