In-depth explanation of oracle index organization table

The index organization table (IOT) can not only store data, but also store the indexes created for the table. The data in the index organization table is arranged according to the order after the primary key is sorted, which improves the access speed. However, this is at the cost of insert and update Performance (each write and update must be re-ordered ).
The format for creating an index organization table is as follows:
Copy codeThe Code is as follows: create table indexTable (
ID varchar2 (10 ),
NAME varchar2 (20 ),
Constraint pk_id primary key (ID)
)
Organization index;

Note:
● When creating an IOT platform, you must set a primary key. Otherwise, an error is returned.
● The index organization table actually puts all the data into the index.
Index organization table attributes
1. OVERFLOW clause (row OVERFLOW)
Because all data is indexed, when the data volume of the table is large, the query performance of the index organization table is reduced. In this case, the overflow section is set to store the primary key and overflow data separately to improve efficiency. Overflow segments can be set in either of the following formats:
PCTTHRESHOLD n:Specify the percentage of a data block. When the data usage of a row exceeds the upper limit, other column data of the row is placed in the overflow segment.
INCLUDING column_name:All columns before the specified column are placed in the index block, and all columns after the specified column are placed in the overflow segment.
● Use PCTTHRESHOLD when the data volume of a field in the row cannot be determined.
● If all rows exceed the size specified by PCTTHRESHOLD, use INCLUDING.
Copy codeThe Code is as follows: create table t88 (
ID varchar2 (10 ),
NAME varchar2 (20 ),
Constraint t88_pk_id primary key (ID)
)
Organization index
PCTTHRESHOLD 20
Overflow tablespace users
INCLUDING name;

As shown in the preceding example, the name and subsequent columns must be placed in the overflow column, while other columns follow the PCTTHRESHOLD rule.
2. COMPRESS clause (Key compression)
Like an ordinary index, the index organization table can also use the COMPRESS clause to COMPRESS keys to eliminate duplicate values.
The specific operation is to add the COMPRESS n clause after the organization index
Used to compress index columns and extract common factors at the block level to avoid repeated values.
For example:Copy codeThe Code is as follows: create table iot (
Owner VARCHAR2 (8 ),
Object_type VARCHAR2 (8 ),
Object_name VARCHAR2 (8 ),
Constraint iot_pk primary key (owner, object_type, object_name ))
Organization index
NOCOMPRESS;

Indicates that each primary key combination is physically stored. If compress n is used, duplicate columns are no longer physically stored.
● N indicates the number of compressed columns. The default value is infinity.
For example, for data (, 3), (, 4), (, 5), (, 4), (, 5 ),
If COMPRESS is used, the repeated (1, 2) and (1, 3) are compressed.
If COMPRESS 1 is used, only data (1) is compressed.
Index organization table maintenance
(1) The index organization table can perform INSERT, UPDATE, DELETE, and SELECT operations like normal heap tables.
(2) You can use the alter table... OVERFLOW statement to change the attributes of OVERFLOW segments.Copy codeThe Code is as follows: Alter table indextable add overflow;

(3) to ALTER any OVERVIEW attributes, overflow must be defined first.Copy codeThe Code is as follows: alter table t88 pctthreshold 15 including name; -- Adjust the overflow Parameter
Alter table t88 initrans 2 overflow initrans 4; -- modify the initrans feature of data blocks and overflow segments

Application of indexed organization tables
A Heap Table is a normal Table, and the data in the Table is obtained based on the hit rate. There is no clear sequence. During a full table scan, data is not inserted first. The storage of data is random, of course, determined by available free space.
IOT is similar to a table that is full of indexes. All the fields in the table are placed on the index, so it is agreed that the data is stored in strict accordance with the rules, the location of data insertion is determined before data insertion. Therefore, regardless of the insertion sequence, the physical location of data insertion is irrelevant to the insertion sequence. In this way, you can access a lot of blocks During query, but the insert speed is slower than that of a common table. The index organization table is mainly applicable to information retrieval, space, and OLAP programs.
Application of the index organization table:
1. Code query table.
2. tables that are frequently accessed through the primary code.
3. Build your own index structure.
4. Strengthen the common positioning of data and require physical storage of data in a specific order.
5. between is often used... And... Query the master code or unique code.
Frequently updated tables are not suitable for IOT, because oracle constantly maintains indexes, and the cost of multi-index fields is high. If you do not frequently use primary keys to access tables, do not use IOT.
IOT provides the following benefits:
· Improve the buffer cache efficiency, because Given Queries require fewer blocks in the cache.
· Reduce buffer cache access, which improves scalability.
· Less data acquisition work, because data acquisition is faster.
· Less physical I/O is performed for each query.