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

In-depth understanding of Oracle Table (6): differences between heap organization table (HOT) and index organization table (IOT) in-depth understanding of Oracle table (5 ): definition, principle, algorithm, cost, mode, and bitmap of Hash Join http://www.bkjia.com/database/201304/203782.html (I) the storage speed of the source heap organization tables of HOT and IOT is faster because you do not need to consider sorting. however, to search for records that meet a certain condition, you must read all the records for filtering. at this time, the index will appear to speed up the query. The index stores and sorts the values of a few specific fields and records the position in the Table. Because the index is ordered, therefore, 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 the table is directly queried without being indexed, it may also take a lot of time to read all the data when there are too many table fields and a large record. 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, and it is easy to determine the location of the records that meet the conditions, in this way, you only need to read a small amount of data and do not need to read all the table records for judgment. the index table is created in this way. of course, when data is inserted in the index table, data may be reorganized due to the need to be sorted. At this time, data insertion or update will be slower than the heap organization table. if the heap organization table has an index, the insertion of the heap organization table slows 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 fast 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 the fact that the index table has been sorted by some fields cannot be used. that is, sorting is useless for this query. when the specified conditions are met, the index range scan (ii) is used to define the ① heap organization table. The index records the rowid of the record location, and the index is first searched, then, locate the row data in the Block Based on the index rowid. The index and table data are separated. ② the index organization table stores the row data in the form of an index. Therefore, finding an index is equivalent to finding the row data. The index and data are in the same way. (3) The search process is ① The heap organization Table generally has 3-5 layers of indexes. For example, if rowid is 007, you may first find 007 at the first layer <1000, find <100 on the second layer and then 007 on the third layer (note that this is already three times I/O) then, read the row data in the block (4th times I/O) from the corresponding disk using the rowid identified in 007. ② the preceding steps are the same as those in the index organization table. However, after 007 is found, because the data and the index are together, you do not need to find any rowid to directly return the result.