In-depth understanding of Oracle Table (6): differences between heap organization table (hot) and index organization table (IOT)

(I) origins of hot and Iot

The storage speed of heap organization tables is faster because sorting is not required. However, to find records that meet certain conditions, you must read all the records for filtering.
At this time, the index will appear to speed up the query. The index is used to sort and store the values of a few specific fields and record the position in the table,
Because the index is ordered, it is easy to query the specific record location through the index, and then directly read the record from the table based on the record location.
At the same time, because there are few index fields, the index is usually much smaller than its base table.

We can see from the index Access Table Record method that when the amount of data to be accessed is large, the original record is accessed through the location of each record,
Every qualified record needs to be accessed by the index before accessing the base table. This takes a lot of time,
Similarly, if you directly query a table without going through the index, it may also take a lot of time to read all the data when there are too many table fields and large records.

What should we do?
At this time, we will think that if the data in the table itself is ordered, we can quickly find the location of the records that meet the conditions when querying the table,
However, it is easy to determine the location of the qualified record. In this way, you only need to read a small amount of data and do not need to read the full table record for determination.
The index table is generated in this way. Of course, when inserting an index table, the data may be reorganized due to the need to be sorted. At this time, the data insertion or update speed will be slower than the heap organization table.
If the heap organization table has an index, the insertion of the heap organization table will also slow down because the index needs to be modified.

We can see that both the heap organization table + index method and the index table can achieve rapid data search. Why not all index tables are used? Isn't that easy?
What I can think of is that we can create multiple indexes for different search conditions, but the latter cannot, and the latter can only be valid for a group of query conditions.

Of course, any query record on an index is indexed because it is an index structure.
If the query condition does not meet the specified condition, index full SACN must be used and all indexes must be scanned.
This means that you cannot use the fact that the index table has been sorted by certain fields. That is, sorting is useless for this query.
When the specified conditions are met, index range scan is used.


(Ii) Definitions

① The heap organization table records the rowid of the record location in its index. When searching, find the index first, and then find the row data in the Block Based on the index rowid. The index and table data are separated.
② The index organizes the table, and its row data is stored as an index. Therefore, finding an index means finding the row data. The index and data are together.



(Iii) search process

① Heap organization table

Generally, the index has three to five layers. For example, if the rowid is 007, you may first find <1000 at the first layer, and then <100 at the second layer.
Then find 007 on the third layer (note that this is already three times I/O)
Then, read the row data in the block (4th I/O) on the corresponding disk using the rowid identified in 007)

② Index the Organizational table

The preceding steps are the same as above, but after 007 is found, the rowid does not need to be found because the data and the index are together, and the result is returned directly.