Iot table and heap organization table

Creates an organizational structure for a common table in a heap Organizational table in Oracle.

 

For example
We create such a table
Create Table test_iot
(ID int primary key,
Value varchar2 (100)
)

 

Here is a normal heap organization table,

When creating a table, a B + tree-based index is created for the primary key ID. However, only the data of this column is indexed, without the value information in the index, there is no such thing as a disordered data set for data storage.

SQL> insert into test_iot values (1, '1 ');
1 row created.
SQL> insert into test_iot values (2, '1 ');
1 row created.
SQL> insert into test_iot values (4, '1 ');
1 row created.
SQL> insert into test_iot values (3, '1 ');
1 row created.

SQL> select * From test_iot;

ID value
--------------------
1 1
2 1
4 1
3 1

We can insert data to see that the insertion order is unordered.

The index organization table (IOT) has a type of B-tree storage organization method. A normal heap organization table is stored in an unordered set. The data in Iot is stored in the B-Tree Index Structure in sequence by primary key. Different from the general B-tree index, in IOT, each leaf node has the primary key column values of each row and the non-primary key column values. In Oracle, the organization index is used to specify an Iot

Create Table test_iot_1
(ID int primary key,
Value varchar2 (100)
)
Organization index;

SQL> insert into test_iot_1 values (1, '1 ');
1 row created.
SQL> insert into test_iot_1 values (3, '1 ');
1 row created.
SQL> insert into test_iot_1 values (2, '1 ');
1 row created.
SQL> insert into test_iot_1 values (5, '1 ');
1 row created.

SQL> select * From test_iot_1;

ID value
--------------------
1 1
2 1
3 1
5 1

The order is already in place.

In rowid, the two are also different because of different organizational structures.
The physical rowid is used for common heap tables, while the IOT table uses the logical rowid.

SQL> select a. *, rowid from test_iot;

ID value rowid
--------------------------------------
1 aaalmbaaoaaaaxcaaa
2 1 aaalmbaaoaaaaxcaab
4 1 aaalmbaaoaaaaxcaac
3 1 aaalmbaaoaaaaxcaad

SQL> select a. *, rowid from test_iot_1;

ID value rowid
----------------------------------------
1 * baobpbocwql +
2 1 * baobpbocwqp +
3 1 * baobpbocwqt +
4 1 * baobpbocwqx +
5 1 * baobpbocwqb +

The structure of Iot also determines that it is better than the heap organization table in some aspects.

 

The primary keys and values of Iot tables are stored twice, saving space.

The Iot index items are stored together with the data, so the primary key we mentioned can be overwritten in IOT, and the primary key contains information about other fields, therefore, querying non-primary key overwrites can greatly save disk access time.

Because Iot is already an ordered structure, positioning is much faster than normal organization tables. A smaller B tree and a leaf node containing more rows can be obtained.