Shulou(Shulou.com)06/01 Report--

Today, you will study the btree index of oracle. Through this article, you will learn about the types of oracle btree index, the implementation principle of oracle btree index, the process of oracle retrieving data through btree index, and the limitations of b*tree index, and the difference between oracle and mysql btree index. One: the subtype of btree index in oracle: b*tree index is the most commonly used index in oracle and most other databases. The construction of b*tree is similar to binary tree, but the "B" here does not represent binary, but represents balanced. B*tree index has the following subtypes: 1) Index organization table (index organized table): index organization table is stored in B* tree structure, we know that oracle default table is heap table. Unlike IOT, where data is stored in an unorganized way (as long as space is available), data in IOT is stored and sorted in the order of primary keys. For applications, IOT is no different from conventional heap tables, and it is necessary to use sql to access IOT correctly. IOT is most useful for information acquisition, spatial systems and OLAP applications. A simple summary: 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. 2) b*tree clustering index (B*tree cluster index): based on clustering keys (such as age=27), in traditional btree indexes, the keys all point to one row, while B* tree clustering is different, a cluster key points to a block, which contains multiple rows related to the cluster key. 3) descending index: allows data to be sorted in the index structure in descending order (descending order), rather than from smallest to largest (ascending order). When you query the data and finally sort the oder by A desc,B asc, creating a descending index avoids the expensive sort order by operation by creating the following statement: SQL > create index idex_name on table_name (A desc,B asc) 4) reverse key index (reverse key index): this is also the btree index, but the bytes of the key will be "reversed". Using the reverse key index, if the index is filled with incremental values, the index entries can be more evenly distributed in the index. The main purpose is to solve the "right" index leaf block competition, for example, in an oracle RAC environment, some columns are filled with a sequence value or timestamp, and the index on these columns belongs to the "right" index, that is, the data distribution is relatively concentrated. The biggest advantage of using reverse index is to reduce the contention of index leaf blocks, reduce index hotspot blocks and improve system performance. 1. Reverse indexing application 1) it is commonly used when index leaf blocks become hot blocks, and when using data (common in batch insert operations), they are more concentrated in a continuous data range. Then it is easy to overheat the index leaf block when using a normal index, and when it is serious, it will lead to system performance degradation. 2) use in RAC environment when several nodes in RAC environment access data is centralized and dense, the probability of index hotspot blocks will be very high. If the system does not have high requirements for range retrieval, we can consider using reverse indexing technology to improve the performance of the system. Therefore, this technology is more common in RAC environment, and it can significantly reduce the contention of index blocks. two。 Disadvantages of using reverse indexing due to the characteristics of the reverse index structure, if range scanning is often used in the system to read data (for example, using "between and" statement or comparison operator ">" in the where clause

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.