Oracle index organization table

Oracle index organization table

I learned how to index organizational tables in Oracle today. Through this article, you can understand what is an index Organizational table? Under what circumstances can I use the index organization? What are the advantages of indexed organizational tables? What are the disadvantages of indexing an Organizational table?

I. When to index the Organization table (IOT)

Index organized table: The index organization table is stored in the B * tree structure. We know that the default Oracle table is a heap table, heap tables are stored in an unorganized manner (data can be stored as long as there is available space), and IOT is different from it, data in IOT is stored and sorted in the order of primary keys. For applications, IOT performance is no different from that in conventional heap tables. You only need to use SQL to access IOT correctly, an overview: index organization table ---- index is data, and data is index, because data is stored in the B * tree structure. This is a typical B * tree index structure (for more information about Oracle B * tree indexes, see my article ).

The index organization table we discussed today stores data according to this structure. The difference between it and B * tree index is that B * tree index leaf node stores the index key value + rowid; the leaf node of the index organization table stores the whole row of data, which is similar to the innodb Engine table of mysql. Note that IOT platform has strict primary key settings. You must specify a specific primary key column when creating a table, because data in IOT is stored and sorted in the order of primary keys.

Ii. Advantages of indexed organization tables

1) First of all, it is obvious that the index organization table can save space, because the index and the table are combined into one,

2) When performing a unique scan or range scan based on the primary key, these columns are sorted by index and the ROWID is returned to the table once less than the average index, so the speed will be faster,

3) if, based on data characteristics such as an ID card number ID and a bank card number, it is clear that an ID card number can have multiple bank card numbers, if the structure of the index organization table is (ID card number ID and bank card number), it is clear that if ID = ** is used during query, then the advantages of the index organization table come out, first, he does not perform the ROWID back-to-table operation once, and then the index organization table is sorted. Therefore, the card numbers of IDs of the same ID card must be stored in neighboring blocks, this is actually a column in the second vertex.

4) In the heap organization table, the possibility of two rows of data on the same database block is almost 0, while the iot table is sorted according to the order of primary keys, therefore, when the data queried by time range or by primary key range is on the same block or adjacent block, the logical io and physical io required for the data to be queried are reduced.

5) Improve the buffer cache efficiency, because the given query requires fewer blocks in the cache, · reduce the buffer cache access, which improves scalability.

Iii. Disadvantages and applicable scenarios of indexed organizational tables:

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, the performance of insertion and update is reduced because you have to re-Sort each write and update, so I personally think that the IOT table is not suitable in the oltp system,

IOT is most useful for information retrieval, spatial systems, and OLAP applications. If between is frequently used for queries on a primary key or unique key, if data is stored physically in an orderly manner, to improve the performance of these queries,

Iv. overflow Section of the Oracle index organization table)

1) Significance of the existence of the overflow segment

To enable the index leaf blocks (blocks containing specific index data) to efficiently store data, indexes are generally located in a column subset, the number of rows on the index block is several times more than that on the heap table block. The index is expected to obtain multiple rows for each partition. Otherwise, Oracle will spend a lot of time maintaining the index, because each insert or update may cause index block breakdown.

When creating IOT, the overflow clause allows you to create another segment (which is equivalent to making IOT a multi-segment object, just like a CLOB column). If the row data of IOT becomes too large, it can overflow into this section. When reading data, Oracle will read the "first" of the row, locate the pointer for the remaining part of the row, and then read these parts.

In addition, 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. Note that if SELECT * FROM is used for a long time, overflow segments are useless;

2) Implementation of overflow segments; PCTTHRESHOLD and INCLUDING

PCTTHRESHOLD n: Specify the percentage of a data block. When the data volume in the row exceeds the block percentage, the columns in the remainder of the row are stored in the overflow segment. For example, PCTTHRESHOLD is 10%, the block size is 8 KB. Therefore, a row with a length greater than 800 bytes stores some column values elsewhere, and cannot store them on the index block.

INCLUDING column_name: the columns in the row from the first column to the specified column in The INCLUDING clause (INCLUDING this column) are all placed in the index block, and the subsequent columns are placed in the overflow segment.

3) Selection criteria for implementing the overflow section

1. If your application always (or almost always) uses the first four columns of the table, but seldom accesses the last five columns, it is more appropriate to use INCLUDING;

2. If you cannot clear which columns are always accessed and which columns are not accessed, you can use PCTTHRESHOLD. Once you determine how many rows can be stored on an average index block, it is easy to set PCTTHRESHOLD. For example, if you want to store 20 rows on each index block, well, this indicates that each row should be 1/20 (5%), and your PCTTHRESHOLD is 5. In this way, each row occupies up to 5% blocks of data, and 20 rows of data can be saved;

Summary:

It can be used in Oracle index organization tables, data warehouses, and other olap systems. It is not suitable for oltp systems, and index organization tables can also be used to create indexes, which are called secondary indexes, in addition, secondary indexes are less efficient than regular table indexes, because IOT usually requires two scans, one scan of the secondary index structure, and the other scan of IOT itself. Then, you can use the alter table... OVERFLOW statement to modify the OVERFLOW section attributes of the index organization TABLE.