There are several types of indexes in oracle

Oracle has six types of indexes, namely: 1. B* tree index, whose structure is similar to a binary tree and can provide fast access to a row or a set of rows based on the key; 2. Bitmap index, suitable for highly repetitive applications And usually read-only data; 3. Function-based index, the result of a function calculation can be stored in the column of the row; 4. Application domain index, an index built and stored by yourself; 5. HASH index, must be used HASH cluster; 6. Partitioned index, simply divide an index into multiple fragments.

The operating environment of this article: Windows 10 system, Oracle version 19c, Dell g3 computer.

Oracle provides six different types of indexes for use.

Oracle includes the following indexes:

1, B* tree index

These are what I call "traditional" indexes. This is by far the most commonly used index in Oracle and most other databases.

B* The tree is constructed like a binary tree and can provide fast access to a row or a set of rows based on the key, usually requiring only a few reads to find the correct row. However, it is important to note that the "B" in "B* tree" does not represent binary, but represents balanced (balanced). A B* tree index is not a binary tree, as you will learn when we introduce how to physically store a B* tree on disk. B* tree indexes have the following subtypes:

Index organized table (index organized table): Index organized tables are stored in a B* tree structure. Unlike heap tables, where data rows are stored in an unorganized manner (data can be placed as long as there is available space), data in IOT is stored and sorted in the order of primary keys. To the application, the IOT behaves like a "regular" table; SQL is required to access the IOT correctly. IOT is most useful for information acquisition, spatial systems, and OLAP applications. IoT has been discussed in detail in the previous chapter.

B*tree cluster index (B*tree cluster index) These are a variant of the traditional B*tree index (with only slight changes). B* tree clustered indexes are used to index clustered keys (see the “Indexing Clustered Tables” section in Chapter 11.), so they will not be discussed in this chapter. In traditional B* trees, keys point to one row; unlike B* tree clustering, a clustering key points to a block that contains multiple rows related to this clustering key.

Descending index: Descending index allows data to be sorted in the index structure in "from large to small" order (descending order), rather than in "small to large" order (ascending order). We explain why descending indexes are important and explain how descending indexes work.

Reverse key index: This is also a B* tree index, except that the bytes in the key will be "reversed". With an inverted key index, the index entries can be more evenly distributed in the index if the index is populated with increasing values. For example, if you use a sequence to generate a primary key, the sequence will generate values ​​such as 987500, 987501, 987502, and so on. The values ​​are sequential, so if a traditional B* tree index was used, these values ​​might be placed on the same right-hand block, which increases contention for this block. Using the reverse key, Oracle will logically index 205789, 105789, 005789, etc. Oracle will byte-reverse the stored data before placing it in the index, so that values ​​that may have been adjacent to each other in the index will be far apart after the byte-reversal. By reversing the bytes, insertions into the index are spread over multiple blocks.

2. Bitmap index (bitmap index)

In a B* tree, there is usually a one-to-one relationship between index entries and rows: an index entry points to One line. For bitmap indexes, one index entry uses a bitmap to point to multiple rows at the same time. Bitmap indexes are suitable for data that is highly repetitive and usually read-only (highly repetitive means that the data has only a few distinct values ​​relative to the total number of rows in the table). Consider a table with 1 million rows, where each column has only three possible values: Y, N, and NULL. For example, if you need to frequently count how many rows have the value Y, this is suitable for building a bitmap index. However, this does not mean that if a certain column in this table has 11.000 different values, a bitmap index cannot be created. Of course, a bitmap index can also be created for this column. In an OLTP database, bitmap indexes cannot be considered due to concurrency-related issues (we will discuss this later). Note that bitmap indexing requires Oracle Enterprise or Personal Edition.

Bitmap join index (bitmap join index): This provides a method of denormalizing data in an index structure (rather than a table). For example, consider the simple EMP and DEPT tables. Someone may ask this question: "How many people work in the department located in Boston?" EMP has a foreign key pointing to DEPT. To count the number of employees in the department with the LOC value of Boston, you usually have to complete a table join and LOC Column joins to EMP records to answer this question. By using a bitmap join index, you can index the LOC column on the EMP table.

3. Function-based index (function-based index)

These are B* tree indexes or bitmap indexes, which store the result of a function calculation in the column of the row instead of storing The column data itself. You can think of a function-based index as an index on a virtual column (or derived column); in other words, the column is not physically stored in the table. Function-based indexes can be used to speed up queries like SELECT * FROM T W HERE FUNCTION(DATABASE_COLUMN) = SAME_VALUE because the value FUNCTION(DATABASE_COLUMN) has been calculated in advance and stored in the index.

4. Application domain index (application domain index)

The application domain index is an index that you build and store yourself. It may be stored in Oracle or outside Oracle. You have to tell the optimizer how selective the index is and how expensive the execution is, and the optimizer will decide whether to use your index based on the information you provide. Oracle text indexes are an example of an application domain index; you can also build your own using the same tools you use to build Oracle text indexes. It should be pointed out that the "index" created here does not require the use of a traditional index structure. For example, Oracle text indexes use a set of tables to implement their index concepts.

5. HASH index

To use HASH index, you must use HASH cluster. When you create a cluster or HASH cluster, you also define a cluster key. This key tells Oracle how to store the table on the cluster. When storing data, all rows related to this cluster key are stored on a database block. If the data is stored in the same database block and a HASH index is used, Oracle can access the data by executing a HASH function and I/O - and by applying a binary height 4 B-tree index. data, you need to use 4 I/O when retrieving the data.

Tips: HASH indexes are very useful when there are restrictions (need to specify a certain value rather than a value range).

6. Partitioned index

Partitioned index simply divides an index into multiple fragments, so that smaller fragments can be accessed and these fragments can be stored on different hard disks ( avoid I/O problems). Both B-number indexes and bitmap indexes can be partitioned, but HASH indexes cannot be partitioned.

There are two types of partitioned indexes: local partitioned indexes and global partitioned indexes. Each type has two subtypes, prefixed and unprefixed. If a bitmap index is used, it must be a local index.

The main reason for partitioning the index is to reduce the size of the index that needs to be read. In addition, placing the partitions in different table spaces can improve the availability and reliability of the partitions.

The above is the detailed content of There are several types of indexes in oracle. For more information, please follow other related articles on the PHP Chinese website!