Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to understand the oracle index organization table, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Today, learn about index organization tables in oracle. Through this article, you can learn what index organization tables are. Under what circumstances can index organization be used? What are the advantages of organizing tables by indexes? What are the disadvantages of organizing tables by index?

One: when to index the organization table (IOT)

Index organization table (index organized table): index organization tables are stored in a B* tree structure. We know that oracle default tables are heap tables, and heap tables are stored in an unorganized way (data can be put as long as space is available). Unlike IOT, data in IOT is stored and sorted in the order of primary keys. For applications, IOT is no different from conventional heap tables. You need to use only sql to access IOT correctly, to put it simply: the index organizes the table-"the index is the data, and the data is the index, because the data is stored according to the B* tree structure. The following figure shows the structure of a typical B*tree index (see my other article, http://blog.itpub.net/29654823/viewspace-2150192/, for an understanding of oracle b*tree indexes).

The index organization table we discuss today also stores data according to this structure. The difference between B*tree index leaf node and B*tree index is that the B*tree index leaf node stores the index key value + rowid;, while the index organization table leaf node stores the whole row data, which is very similar to the table of mysql innodb engine. It should be noted that IOT is particularly strict in setting primary keys, and requires that you specify a clear primary key column when creating a table, because the data in IOT is stored and sorted in the order of the primary key.

Second, the advantages of organizing tables by index

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) in addition, when the unique scan or range scan is performed according to the primary key, because the columns are arranged according to the index order, and the operation of ROWID back to the table is less than the normal index, then the speed will be faster.

3) secondly, according to the characteristics of the data, such as an ID number ID and a bank card number, it is obvious that an ID card number ID can have multiple bank card numbers. If we set up an index to organize the table structure (ID number ID and bank card number), obviously if we use ID=** when querying, then the advantage of organizing the table by index will come out. First of all, he has one less ROWID operation to return the table. Secondly, the arrangement of the index organization table is orderly, so the card number information of the ID of the same ID card must be stored in the adjacent block, which is actually a column of the second point.

4) in the heap organization table, the possibility of two rows of data on the same database block is almost zero, while the iot table is arranged according to the order in which the primary key is sorted, so the data queried by time range or primary key range is on the same block or on adjacent blocks, so the logical io and physical io needed to query these data will be reduced.

5) improve buffer caching efficiency because a given query requires fewer blocks in the cache and reduces buffer cache access, which improves scalability.

Third, the disadvantages and applicable scenarios of organizing tables by index:

The index organization table (IOT) can store not only the data, but also the indexes established for the table. The data of the index organization table is arranged according to the order in which the primary key is sorted, which improves the speed of access. However, due to the reordering after each write and update, resulting in poor insert and update performance, I personally think that it is not suitable to use IOT tables in oltp systems.

IOT is most useful for information acquisition, spatial systems, and OLAP applications. If between queries are often used on a primary key or unique key, the performance of these queries can be improved if the data is physically stored in an orderly manner.

Four: talk about the overflow section of the oracle index organization table (overflow section)

1) the significance of the existence of overflow segment

In order to enable the index leaf block (the block containing specific index data) to store data efficiently, the index is generally on a subset of columns, and the number of rows on the index block is usually several times more than that on the heap table block. The index expects multiple rows per block, otherwise oracle spends a lot of time maintaining the index, because each insert or update can cause index blocks to be shredded.

When creating an IOT, the overflow clause allows you to create another segment (which is equivalent to making IOT a multi-segmented object, just like having a CLOB column). If the row data of the IOT becomes too large, it can overflow into this segment. When reading the data, oracle will read the "head" of the row, find the pointer to the rest of the row, and then read those parts.

In addition, because all the data is put into the index, when the amount of data in the table is very large, it will reduce the query performance of the index organization table. At this point, the overflow segment is set to separate the primary key from the overflow data to improve efficiency. Note that the overflow segment is useless if it is SELECT * FROM for a long time.

2) overflow segment implementation; PCTTHRESHOLD and INCLUDING

PCTTHRESHOLD n: set the percentage of a data block. When the amount of data in the row exceeds this percentage of the block, the remaining columns in the row will be stored in the overflow segment, for example, PCTTHRESHOLD is 10%, and the block size is 8kb, so rows longer than 800byte will store some of the column values elsewhere, rather than on the index block.

INCLUDING column_name: index blocks are placed in the row from the first column to the column specified in the INCLUDING statement (including this column), and subsequent columns are placed in the overflow segment

3) selection criteria for the implementation of overflow segments

1. If you always (or almost always) use the first four columns of a table in your application, and rarely access the last five columns, INCLUDING is more appropriate.

two。 If you cannot clearly indicate which columns are always accessed and which columns are generally not accessed, you can consider using PCTTHRESHOLD. Once you have determined the average number of rows that may be stored on each index block, it is easy to set up PCTTHRESHOLD; for example, you want to store 20 rows on each index block, well, this means that each row should be 20 rows (5%), and your PCTTHRESHOLD is 5, so that each row of data occupies a maximum of 5% of the block size, which ensures that 20 rows of data can be stored.

For oracle index organization table, data warehouse and other olap system can be used, not very suitable for oltp system, and index organization table can also create an index, called secondary index, and the secondary index compared with the regular table index efficiency is low, because IOT, usually need two scans, once scan the secondary index structure, another scan IOT itself. Then the overflow segment of the index organization table can be used with ALTER TABLE. OVERFLOW statement to change the properties of the overflow segment.

The above is how to understand the oracle index organization table, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.