2.1 B-Tree index (default type)
The B-Tree index is a common index in Oracle. It is the default index type when the index is created. A B-Tree index can be a (simple) index of a column, or it can be an index of a composite/composite (multiple columns). A B-tree index can include up to 32 columns.
In the example, the B-Tree index is on the last_name column of the employee table. The index has a two-yuan height of 3; Next, Oracle passes through two branch blocks (branch block) to reach the leaf block containing the rowid. In each branch block, the branch row contains the ID number of the next block in the chain.
Leaf blocks contain index values, ROWID, and pointers to the previous and next leaf blocks. Oracle can traverse this binary tree in two directions. The B-Tree index holds the ROWID value for each data row that has a value on the index column. Oracle does not index rows that contain null values on an indexed column. If the index is a combined index of more than one column, and the column contains a null value, the row is in an indexed column that contains a null value and will be processed to null (treated as NULL).
Tip: The values of the indexed columns are stored in the index. Therefore, you can create a composite (composite) index that can directly satisfy the query without having to access the table. This reduces the amount of I/O by not retrieving data from the table.
B-tree Features:
Suitable with a large number of increases, deletions, and changes (OLTP)
Cannot use a query that contains an OR operator;
Suitable for high cardinality columns (multiple unique values)
Typical tree-like structure;
Each node is a data block;
Most are physically one, two or three layers, logically three layers;
The leaf block data is sorted, incrementing from left to right;
The scope of the index is placed in the branch block and the root block;
2.2-bit Graph index
Bitmap indexes are well suited for decision support systems (decision supports SYSTEM,DSS) and data warehouses, and they should not be used for tables accessed through transactional applications. They can access very large tables using a column that is less than the medium cardinality (the number of different values). Although the bitmap index can be up to 30 columns, they are usually used only for a small number of columns.
For example, your table might contain a column called Sex, which has two possible values: male and female. This cardinality is only 2 if the user frequently queries the table based on the value of the sex column, which is the base column of the bitmap index. When more than one bitmap index is contained within a table, you can appreciate the true power of the index of the map. If there are multiple bitmap indexes available, Oracle can combine result sets from each bitmap index to quickly delete unnecessary data.
BITMAPT Features:
Suitability and decision support system;
The cost of doing the update is very high;
Very suitable for queries of the OR operator;
A bitmap index can be built when the base ratio is low;
Tip: You need to use a bitmap index for columns with lower cardinality. The sex column is an example of two possible values: male or female (base is only 2). Bitmaps are very fast for low cardinality (a small number of different values), because the size of the index is much smaller relative to the B-tree index. Because these indexes are low-cardinality B-tree indexes, they are very small, so you can frequently retrieve more than half of the rows in a table, and still use bitmap indexes.
When most entries do not add a new value to the bitmap, the bitmap index is usually better than B-tree to load the table (insert operation) in a batch (single user) operation. The bitmap index should not be used when multiple sessions are inserting rows into a table at the same time, which can occur in most transactional applications.
Example
Here's a sample table participant, which contains survey data from individuals. The columns Age_code, Income_level, Education_level, and marital_status all include their respective bitmap indexes. Shows the data balance in each histogram, and the execution path to the query that accesses each bitmap index. The execution path in the diagram shows how many single-bit graph indexes have been merged, and you can see that performance has been significantly improved.
As shown, the optimizer uses 4 separate bitmap indexes, which are referenced in the WHERE clause. Each bitmap record pointer, such as 0 or 1, is used to indicate which rows in the table contain the known values in the bitmap. With this information, Oracle performs a bitmap and operation to find which rows will be returned from all 4 bitmaps. The value is then converted to the ROWID value, and the query continues to complete the remaining processing work. Note that all 4 columns have a very low cardinality, and the index can be used to return a matching row very quickly.
Tip: After merging multiple bitmap indexes in a single query, you can make a significant performance improvement. Bitmap indexes use fixed-length data types that are better than variable-length data types. Larger blocks also improve the storage and reading performance of bitmap indexes.
The B-Tree index is listed as normal, whereas the bitmap index has a type value of bitmap.
Tip: If you want to query the bitmap index list, you can query the Index_type column in the user _indexes view.
We recommend that you do not use bitmap indexing in some online transaction processing (OLTP) applications. The index value of the B-tree index contains ROWID so that Oracle can lock the index at the row level. The bitmap index is stored as a compressed index value, which contains a range of ROWID, so Oracle must lock all ranges of rowid for a given value. This type of locking may cause a deadlock in some DML statements. The SELECT statement is not affected by this locking issue.
Usage restrictions for bitmap indexes:
The rule-based optimizer does not consider bitmap indexing.
When you execute an ALTER TABLE statement and modify a column that contains a bitmap index, the bitmap index is invalidated.
The bitmap index does not contain any column data and cannot be used for any type of integrity check.
Bitmap indexes cannot be declared as unique indexes.
The maximum length of a bitmap index is 30.
Tip: Don't use bitmap indexing in a heavy OLTP environment
2.3 Hash Index
Hash clusters must be used to use hash indexes. When a cluster or hash cluster is established, a cluster key is defined. This key tells Oracle how to store tables on a cluster. When the data is stored, all the rows associated with the cluster key are stored on a database block. If the data is stored on the same database block, and the hash index is the exact match in the WHERE clause, Oracle can access the data by executing a hash function and I/O-while accessing the data by using a B-tree index with a two-yuan height of 4. You need to use 4 I/O when retrieving data. As shown, the query is an equivalent query that matches the hash column and the exact value. Oracle can quickly use this value to determine the physical storage location of a row based on the hash function.
A hash index can be the quickest way to access data in a database, but it also has its drawbacks. The number of different values on the cluster key must be known before the hash cluster is created. You need to specify this value when creating a hash cluster. A number that underestimates the different values of cluster keys can cause cluster conflicts (the key values of two clusters have the same hash value). This conflict is very resource-intensive. Collisions cause a buffer overflow to store additional rows and then cause additional I/O. If the number of different hash values has been underestimated, you must change this value after rebuilding the cluster.
The ALTER CLUSTER command cannot change the number of hash keys. Hash clusters may also waste space. If you cannot determine how much space is required to maintain all the rows on a cluster key, you can create a waste of space. If additional space cannot be allocated for future growth of the cluster, the hash cluster may not be the best choice. Hash clusters may not be the best choice if the application frequently makes full table scans on the cluster tables. Full table scans can be very resource-intensive due to the need to allocate the remaining amount of space in the cluster for future growth.
Be careful before you implement a hash cluster. You need to look at the application thoroughly to ensure that you already know a lot of information about tables and data before you implement this option. In general, hashing is very effective for some static data that contains ordered values.
Tip: A hash index is useful in situations where there are restrictions that require specifying a certain value rather than a range of values.
2.4 Index Organization table
The Index organization table changes the table's storage structure to a B-tree structure, sorted by the table's primary key. This special table, like other types of tables, can execute all DML and DDL statements on the table. Because of the special structure of the table, ROWID is not associated with the rows of the table.
For some statements involving exact match and range search, the indexed organization table provides a fast, key-based data access mechanism. The performance of update and DELETE statements based on primary key values is also improved because the rows are physically orderly. Because the values of the key columns are not duplicated in the table and index, the amount of space required for storage decreases.
If you do not frequently query data based on primary key columns, you need to create a level two index on other columns in the indexed organization table. Applications that do not frequently query tables based on primary keys do not understand the full benefits of using index-organized tables. For tables that are always accessed by an exact match or a range scan of the primary key, you need to consider using an indexed organization table.
Tip: You can create a level two index on the Index organization table.
2.5 Invert key Index
When loading some ordered data, the index is bound to encounter some I/O related bottlenecks. During data loading, certain parts of the index and disk will certainly be used much more frequently than other parts. To solve this problem, you can store the index table space on a disk architecture that can physically partition the files on multiple disks.
To solve this problem, Oracle also provides a way to reverse the key index. If the data is stored in a reverse key index, the value of the data is reversed from the previously stored value. In this way, data 1234, 1235, and 1236 are stored as 4321, 5321, and 6321. The result is that the index updates different index blocks for each newly inserted row.
Tip: If you have a limited disk capacity and you want to perform a large number of sequential loads, you can use the Reverse key index.
You cannot use a Reverse key index in conjunction with a bitmap index or an indexed organization table. Because the bitmap index and Index organization table cannot be reversed key processing.
2.6 Function-based indexes
You can create a function-based index in a table. If there is no function-based index, any query that executes a function on a column cannot use the column's index. For example, the following query cannot use the index on the job column unless it is a function-based index:
SELECT * from emp where UPPER (Job) = ' MGR ';
The following query uses the index on the job column, but it will not return a row with a MGR or MGR value for the job column:
SELECT * from emp where job = ' MGR ';
You can create indexes that allow index access to support function-based columns or data. Instead of indexing directly on the job column, you can create an index on the column expression upper (job), such as:
Create INDEX Emp$upper_job on EMP (UPPER (JOB));
Although function-based indexes are useful, you must consider the following questions before you create them:
Can I restrict the functions that are used on this column? If so, can you restrict all functions that are executed on this column?
Is there enough storage space to handle additional indexes?
What is the effect of increasing the number of indexes on each column on the performance of DML statements executed against that table?
Function-based indexing is useful, but you must be careful when you implement it. The more indexes you create on a table, the more time it takes for the INSERT, update, and DELETE statements to execute.
Note: For a function-based index used by the optimizer, the initial parameter query _rewrite _ enabled must be set to true.
2.7 Partition Index
A partitioned index simply divides an index into multiple fragments. By dividing an index into multiple fragments, you can access smaller fragments (and faster), and you can store the fragments on separate disk drives (avoiding I/O problems). Both the B-tree and the bitmap index can be partitioned, and the hash index cannot be partitioned. There are several partitioning methods: tables are partitioned, indexes are not partitioned, tables are not partitioned, indexes are partitioned, and tables and indexes are partitioned. Whichever method you take, you must use a cost-based optimizer. Partitioning provides more possibilities to improve performance and maintainability
There are two types of partition indexes: Local partition index and global Partition index. Each type has two subtypes, prefixed index and no prefix index. The indexes on each column of a table can have combinations of various types of indexes. If a bitmap index is used, it must be a local index. The primary reason for partitioning the index is to reduce the size of the index that is required to read, and to put the partition in a different tablespace to improve the availability and reliability of the partition.
Oracle also supports parallel queries and parallel DML when using partitioned tables and indexes. This allows you to execute multiple processes at the same time, speeding up the processing of this statement.
2.7.1. Local partition index (commonly used index)
You can use the same partition key and scope bounds as the table to partition the local index. The partition for each local index contains only the keys and rowid of the table partition it is associated with. A local index can be a B-tree or a bitmap index. If it is a B-tree index, it can be a unique or not unique index.
This type of index supports partition independence, which means that for a separate partition, you can add, intercept, delete, split, go offline, and so on, without having to delete or rebuild the index at the same time. Oracle automatically maintains these local indexes. Local index partitions can also be rebuilt separately, while other partitions are not affected.
2.7.1.1 Index with prefix
The prefixed index contains the keys from the partition keys and takes them as the leading index. For example, let's review the participant table again. After you create the table, use the two columns of survey_id and survey_date for scope partitioning, and then create a local index with a prefix on the survey_id column, as shown in. All partitions of this index are equally divided, meaning that the partitioning of the index is created using the same range bounds of the table.
Tip: A local prefix index allows Oracle to quickly remove unnecessary partitions. That is, a partition that does not contain any value in the WHERE Condition clause will not be accessed, which also improves the performance of the statement.
2.7.1.2 Index of the prefix
The prefix index does not have the leading column of the partition key as the leading column of the index. If you use the same partition table with the same partition key (SURVEY_ID and Survey_date), the index built on the survey_date column is a local prefix index, as shown in. You can create a local prefix index on any column of the table, but each partition of the index contains only the key values for the corresponding partition of the table.
If you want to set an index that is not prefixed to a unique index, the index must contain a subset of the partition keys. In this example, we must combine the columns that contain survey and/or survey_id (as long as the survey_id is not the first column of the index, it is a prefixed index).
Tip: For a unique prefix index, it must contain a subset of the partition keys.
2.7.2. Global Partition Index
The global Partitioned index contains keys from multiple table partitions in an index partition. A partition key for a global partition index is a value that differs from or specifies a range in the partition table. When you create a global partitioned index, you must define the range and value of the partition key. The global index can only be a B-tree index. Oracle does not maintain the global partition index by default. If a partition is intercepted, added, split, deleted, and so on, the global partition index must be rebuilt unless you specify the UPDATE global indexes clause of the ALTER TABLE command when modifying the table.
2.7.2.1 Index with prefix
Generally, the globally prefixed index does not have a peer partition in the underlying table. There is no reason to limit the peer partitioning of an index, but Oracle does not take full advantage of peer partitioning when generating query plans or performing partition maintenance operations. If the index is partitioned, it must be created as a local index, so that Oracle can maintain the index and use it to remove unnecessary partitions, as shown in. In the 3 index partitions of the graph, each partition contains index entries that point to rows in multiple table partitions.
Partitioned, globally prefixed index
Tip: If a global index is partitioned by a peer, it must be created as a local index so that Oracle can maintain the index and use it to remove unnecessary partitions.
2.7.2.2 Index of the prefix
Oracle does not support non-prefixed global indexes.
2.8-bit Graph connection index
The bitmap join index is a bitmap index of connections based on two tables, which is used in a data warehouse environment to improve the performance of queries that connect dimension tables and fact tables. When creating a bitmap connection index, the standard method is to connect the dimension tables and fact tables that are commonly used in the index. When users combine query fact tables and dimension tables in a single query, they do not need to perform a connection because there is already a connection result available in the bitmap connection index. rowID further improves performance by compressing bitmap connections in the index, and reduces the amount of I/O required to access the data.
When creating a bitmap connection index, specify the two tables involved. The corresponding syntax should follow the following pattern:
Create bitmap index fact_dim_col_idx on FACT (DIM. Descr_col) from FACT, DIM
where FACT. Joincol = DIM. Joincol;
The syntax for bitmap joins is special, which contains the FROM clause and a WHERE clause, and references two separate tables. An indexed column is typically a description column in a dimension table-that is, if the dimension is a customer and its primary key is customer_id, then the column is usually indexed customer_name. If the fact table is named Sales, you can create an index using the following command:
Create Bitmap Index Sales_cust_name_idx
On SALES (CUSTOMER. customer_name) from SALES, CUSTOMER
where SALES. Customer_id=customer. customer_id;
If the user then queries the sales and customer tables using the WHERE clause of the specified customer_name column value, the optimizer can use the bitmap join index to quickly return rows that match the join condition and the customer_name condition.
The use of bitmap connection indexes is generally limited:
1) Only the columns in the dimension table can be indexed.
2) The column used for the connection must be a primary KEY or a unique constraint in the dimension table, or, in the case of a composite primary key, each column in the connection must be used.
3) You cannot create a bitmap join index on an indexed organization table, and the restrictions that apply to regular bitmap indexes also apply to bitmap join indexes
oracle-specific Index type resolution
