Iot (index organized table)

We know that normal tables are organized in the form of heap, which is an unordered way. Oracle also provides an ordered table, which is an index organization table (IOT table. The Iot table must have a primary key, but the IOT table itself does not correspond to a segment. All the data in the table is stored in the leaf node of the index where the primary key is located. In other words, the index entries in the index leaf node not only store the value of the indexed column, but also the value of other columns.
For tables that always access data through primary keys, Iot tables are more suitable. If you use a common table, you must read at least two data blocks when accessing the table through the primary key index: one index block and the other table data block. If you use an Iot table, because the table data is stored in the index block, you only need to read one block. An Iot table is a virtual table, but it has all the features of a common table. Even though the data in the IOT table is actually stored in the index segment, we can create another index on other columns Based on the IOT table. This index is called a secondary index.

When using secondary indexes to search for data, there are two phases:
1. Physical prediction: scan the index nodes in the index segment attached to the IOT table that it points to based on the physical rowid recorded in the secondary index.
2. Logical prediction: because the data in the IOT table is stored in the leaf node of the index and the data in the index is stored in an orderly manner, the data will be inserted in the middle of the leaf node, therefore, the splitting to the leaf node exists. Once a leaf node is split, the index block of the data changes, and the secondary index points to an incorrect position. Data cannot be found when you access the IOT table by using the physical rowid. At this time, Oracle performs logical speculation, and then does not use rowid. Instead, it uses the value of the primary key column to scan the IOT table.

 

An example of creating an Iot table is as follows:
Create Table iot_test (ID number, C1 varchar2 (40), C2 varchar2 (40), C3 varchar2 (40), C4 varchar2 (40), primary key (ID ))
Organization Index
Tablespace indx
Pctthreshold 10
Including C2
Overflow tablespace users;

 

When defining an Iot table, you must create a primary key constraint and specify the Organization Index option. You can also specify the tablespace name of the index corresponding to the primary key. In the preceding example, if the ID and C1 columns are frequently accessed, the other C2, C3, and C4 columns are also placed in the index block, which is a waste of space. Because these three columns are not frequently accessed, there is no need to put them together with ID and C1. So I can set the attributes pctthreshold and including to store ID and C1 only in the index block, and put the three columns in overflow, (overflow is a segment ).

 

Pctthreshold indicates the percentage of data space left in the index block to the total data block size, from 0 to 50%. Assume that in the iot_test table, the ID column and C1 column require a total of about 400 bytes, and the index block size is 8 KB. We want the ID column and C1 column to stay in the index block. The data left in the index block accounts for 5% (500/8192) of the size of the entire index block. Therefore, you can set pctthreshold to 5. we can also use including. For the above example, including C2 indicates all columns (namely, C2, C3, and C4 columns) starting from column C2) put them in overflow. Pctthreshold is preferred. That is to say, if the percentage of the value of the ID column in the data block size exceeds the specified pctthreshold, even though all columns after column c2 are specified in overflow, however, because of the violation of pctthreshold, all columns after c1 are put into overflow (C1, C2, C3, and C4 are all put into overflow)