Oracle-specific index type resolution

Oracle-specific index type resolution
2.1 B-tree index (default type)
The B-tree index is a common index in Oracle. When creating an index, it is the default index type. The B-tree index can be a (simple) or composite (multiple columns) index. The B-tree index can contain up to 32 columns.
In the example, the B-tree index is located in the last_name column of the employee table. The binary height of this index is 3. Next, Oracle will pass through two branch blocks to reach the leaf blocks containing ROWID. Each branchblock contains the ID of the next block in the chain.
The leaf block contains the index value, ROWID, and pointer to the forward and next leaf blocks. Oracle can traverse this binary tree in two directions. The B-tree index stores the ROWID value of each data row with values in the index column. Oracle does not index rows with NULL values in the index column. If the index is a combination of multiple columns, and the column contains NULL values, this row will be in the index column containing NULL values, it will be processed as NULL (treated as NULL ).
Tip: the value of the index column is stored in the index. Therefore, you can create a composite index that can directly meet queries without accessing the table. This removes the need to retrieve data from the table, thus reducing the I/O volume.
B-tree features:
Suitable for large numbers of OLTP)
Queries that contain the OR operator cannot be used;
Suitable for columns with a high base (multiple unique values)
Typical tree structure;
Each node is a data block;
Most of them are physical, two or three layers, but logical;
The leaf block data is sorted, increasing from left to right;
The range of the index is placed in the branch block and the root block;


2.2 bitmap Index
Bitmap indexes are very suitable for Decision Support systems (DSS) and Data Warehouses. They should not be used for tables accessed by transaction processing applications. They can access very large tables using columns with less to medium base (number of different values. Although Bitmap indexes can contain up to 30 columns, they are usually used only for a small number of columns.
For example, your table may contain a column named Sex, which has two possible values: male and female. This base number is only 2. If you frequently query the table based on the Sex column value, this is the base column of the bitmap index. When a table contains multiple Bitmap indexes, you can understand the real power of the Map Index. If multiple available Bitmap indexes exist, Oracle can merge the result sets obtained from each bitmap index to quickly delete unnecessary data.
Features of Bitmapt:
Applicable and decision support systems;
The UPDATE cost is very high;
It is very suitable for OR operator queries;
Bitmap indexes can be created only when the base number is small;
Tip: Bitmap indexes are required for columns with a lower base. The Gender column is an example of two possible values: male and female (the base is only 2 ). Bitmap is very fast for low-base columns (a small number of different values) because the index size is much smaller than that of B-tree indexes. Because these indexes are low-Base B-tree indexes, they are very small. Therefore, you can often retrieve more than half of the rows in a table and still use Bitmap indexes.
When most entries do not add new values to the bitmap, the bitmap index generally performs better in loading tables (insert operations) in batch (single-user) operations than in Tree B. When multiple sessions Insert rows to the table at the same time, bitmap indexes should not be used. This happens in most transaction processing applications.
Example
The following is an example of a table named particle ant, which contains survey data from individuals. The Age_Code, Income_Level, Education_Level, and Marital_Status columns all contain Bitmap indexes. Shows the data balance in each histogram and the execution path for queries accessing each bitmap index. The execution path in the figure shows how many Bitmap indexes are merged. It can be seen that the performance has been significantly improved.
As shown in, the optimizer uses four separate Bitmap indexes, and these index columns are referenced in the WHERE clause. Each bitmap record pointer (for example, 0 or 1) is used to indicate which rows in the table contain known values in the bitmap. With this information, Oracle performs the bitmap and operation to find the rows that will be returned from all the four-digit charts. The value is then converted to the ROWID value, and the query continues to complete the remaining processing. Note that all four columns have a very low base. Using indexes, you can quickly return matched rows.
Tip: merging multiple Bitmap indexes in a query can significantly improve the performance. Bitmap indexes use a fixed-length data type, which is better than a variable-length data type. Larger blocks also improve the storage and reading performance of Bitmap indexes.
The B-tree index is listed as NORMAL, while the BITMAP index type value is BITMAP.
Tip: 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 indexes 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. Bitmap indexes are stored as compressed index values, which contain a certain range of rowids. Therefore, Oracle must lock rowids in all ranges for a given value. This type of lock may cause deadlocks in some DML statements. The SELECT statement is not affected by this locking problem.
Restrictions on Bitmap indexes:
The rule-based optimizer does not consider Bitmap indexes.
If you execute the alter table statement and modify a column that contains a bitmap index, the bitmap index will become invalid.
Bitmap indexes do not contain any column data and cannot be used for integrity checks of any type.
Bitmap indexes cannot be declared as unique indexes.
The maximum bitmap index length is 30.
Tip: Do not use Bitmap indexes in heavy OLTP Environments


2.3 HASH Index
To use HASH indexes, you must use a HASH cluster. A cluster key is defined when a cluster or HASH cluster is created. This key tells Oracle how to store tables in clusters. When data is stored, all rows related to the cluster key are stored in a database block. If the data is stored on the same database block and the HASH index is used as the exact match in the WHERE clause, oracle can access data by executing a HASH function and I/O-and access data by using a B-tree index with a binary height of 4, you need to use 4 I/O for data retrieval. As shown in, the query is an equivalent query used to match the HASH column and the exact value. Oracle can quickly use this value to determine the physical storage location of rows based on the HASH function.
The HASH index may be the fastest way to access data in the database, but it also has its own shortcomings. The number of different values on the cluster key must be known before the HASH cluster is created. You must specify this value when creating a HASH cluster. If the number of different values of the cluster key is underestimated, the cluster may conflict (the key values of the two clusters have the same HASH value ). Such conflicts consume a lot of resources. Conflicts will cause buffer overflow for storing extra rows, and then cause additional I/O. If the number of HASH values has been underestimated, you must change the value after rebuilding the cluster.
The alter cluster command cannot change the number of HASH keys. The HASH cluster may also waste space. If you cannot determine how much space is required to maintain all rows on a cluster key, the space may be wasted. If you cannot allocate additional space for the future growth of the cluster, the HASH cluster may not be the best choice. If applications often perform full table scans on cluster tables, the HASH cluster may not be the best choice. Due to the need to allocate the remaining space of the cluster for future growth, full table scan may consume a lot of resources.
Be careful before implementing the HASH cluster. You need to fully observe the application to ensure that you have learned a lot about tables and data before implementing this option. Generally, HASH is very effective for some static data containing ordered values.
Tip: the HASH index is useful when it has restrictions (a definite value needs to be specified instead of a value range.


2.4 index the Organizational table
The index organization table will change the storage structure of the table to the B tree structure and sort the table by the primary key. Similar to other types of tables, this type of special table can execute all DML and DDL statements on the table. Because of the special structure of the table, ROWID is not associated with the table's rows.
For some statements involving exact match and range search, the index organization table provides a key-Based Fast data access mechanism. The performance of primary key value-based UPDATE and DELETE statements is also improved because rows are physically ordered. Because the key column values are not repeated in both the table and index, the storage space is also reduced.
If data is not frequently queried based on the primary key column, you need to create a secondary index on other columns in the index organization table. Applications that do not frequently query tables based on primary keys do not understand all the advantages of using indexes to organize tables. For tables that are always accessed through precise matching of primary keys or range scanning, you need to consider using indexes to organize tables.
Tip: You can create a secondary index on the index organization table.


2.5 reverse key index
When loading ordered data, the index will certainly encounter some I/O-related bottlenecks. During data loading, some indexes and disks must be used more frequently than others. To solve this problem, you can store the index tablespace in a disk architecture that physically splits files into multiple disks.
To solve this problem, Oracle also provides a way to reverse the key index. If the data is stored with a reverse key index, the values of the data are the opposite of those originally stored values. 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 your disk capacity is limited and you need to perform a large number of sequential loads, you can use the reverse key index.
You cannot use reverse key indexes with bitmap indexes or index organization tables. Because Bitmap indexes and index organization tables cannot be reversed.


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 the function on the column cannot use the index of this column. 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 of the JOB column, but it does not return the rows in the JOB column with Mgr or mgr values:
Select * from emp where job = 'mgr ';
You can create such indexes to allow indexes to access columns or data that support functions. You can create an index for the column expression UPPER (job) instead of directly creating an index on the JOB column, for example:
Create index EMP $ UPPER_JOB on emp (UPPER (job ));
Although function-based indexes are very useful, you must consider the following before creating them:
Can I restrict the functions used in this column? If yes, can all the functions executed on this column be restricted?
Is there enough storage space for additional indexes?
What is the impact on the performance of DML statements executed on this table when the number of indexes added to each column is greater?
Function-based indexes are very useful, but you must be careful when implementing them. The more indexes a table creates, the more time it takes to execute INSERT, UPDATE, and DELETE statements.
Note: For function-based indexes 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 segments. By dividing an index into multiple segments, you can access smaller segments (faster) and store these segments on different disk drives (to avoid I/O problems ). Both Tree B and bitmap indexes can be partitioned, but HASH indexes cannot. There are several partition methods: The table is partitioned, the index is not partitioned, the table is not partitioned, and the index is partitioned. Both the table and the index are partitioned. Either method requires a cost-based optimizer. Partitions provide more possibilities for improving performance and maintainability.
There are two types of partition indexes: Local partition index and global partition index. Each type has two subtypes: prefix index and no prefix index. The indexes on each column of a table can contain a combination of various types of indexes. If a bitmap index is used, it must be a local index. The primary cause of index partitioning is to reduce the size of the index to be read. In addition, placing partitions in different tablespaces can improve the availability and reliability of the partitions.
When Using Partitioned Tables and indexes, Oracle also supports parallel query and parallel DML. In this way, you can execute multiple processes at the same time to speed up processing this statement.
2.7.1. Local partition index (usually used index)
You can use the same partition key and range as the table to partition the local index. The partition of each local index only contains the key and ROWID of the table partition associated with it. Local indexes can be B-tree or Bitmap indexes. If it is a B-tree index, it can be a unique or non-unique index.
This type of index supports partition independence, which means that you can add, intercept, delete, split, and offline data for individual partitions without deleting or recreating indexes at the same time. Oracle automatically maintains these local indexes. Local index partitions can also be rebuilt separately, but other partitions will not be affected.
2.7.1.1 index with a prefix
An index with a prefix contains keys from the partition key and uses them as the leading index. For example, let's re-review the table 'delete. After creating the table, use the survey_id and survey_date columns to partition the range, and then create a local index with a prefix on the survey_id column, as shown in. All the partitions of this index are equally divided, that is, all the partitions of the index are created using the same range of the table.
Tip: Local prefix indexes allow Oracle to quickly remove unnecessary partitions. That is to say, partitions that do not contain any value in the WHERE Condition Clause will not be accessed, which also improves the statement performance.
2.7.1.2 index without a prefix
The index without a prefix does not use the leading column of the partition key as the leading column of the index. If you use a table with the same partition key (survey_id and survey_date), the index created on the survey_date column is a local index without a prefix, as shown in. You can create a local index without a prefix on any column of the table, but each partition of the index only contains the key value of the corresponding partition of the table.
If you want to set an index without a prefix as a unique index, this index must contain a subset of the partition key. In this example, we must combine the columns containing survey and (or) survey_id (as long as survey_id is not the first column of the index, it is an index with a prefix ).
Tip: For a unique index without a prefix, it must contain a subset of the partition key.


2.7.2. Global partition Index
A global partition index contains keys from multiple table partitions in an index partition. The partition key of a global partition index is a value of different or specified range in the partition table. When creating a global partition index, you must define the range and value of the partition key. The global index can only be a B-tree index. By default, Oracle does not maintain global partition indexes. If a partition is intercepted, added, split, or deleted, the GLOBAL partition index must be rebuilt unless the update global indexes clause of the alter table command is specified when the TABLE is modified.
2.7.2.1 index with a prefix
Generally, the global index with a prefix is not partitioned by a peer partition in the underlying table. There is no limit on the peer partitions of an index. However, when Oracle generates a query plan or performs partition maintenance, it does not take full advantage of the Peer partitions. If an index is a peer-to-peer partition, you must create it as a local index, so that Oracle can maintain this index and use it to delete unnecessary partitions, as shown in. In the three index partitions of the graph, each partition contains an index entry pointing to the row in multiple table partitions.
Partitioned and globally prefixed indexes
Tip: if a global index will be a peer-to-peer partition, you must create it as a local index, so that Oracle can maintain this index and use it to delete unnecessary partitions.
2.7.2.2 index without a prefix
Oracle does not support global indexes without a prefix.


2.8 bitmap join Index
Bitmap join index is a bitmap index connected to two tables. This index is used in the data warehouse environment to improve the query performance of connecting dimension tables and fact tables. When creating a bitmap connection index, the standard method is to connect dimension tables and fact tables commonly used in the index. When you query fact tables and dimension tables in a single query, you do not need to perform connections because there are available connection results in the bitmap connection index. The compressed bitmap is used to connect ROWID in the index to further improve performance and reduce the number of I/O required for data access.
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 bitmap join syntax is special. It contains the FROM clause and the WHERE clause and references two separate tables. An index column is usually a description column in a dimension table. If the dimension is CUSTOMER and its primary key is mermer_id, a column such as Customer_Name is usually indexed. If the fact table name is SALES, you can use the following command to create an index:
Create bitmap index SALES_CUST_NAME_IDX
On SALES (CUSTOMER. Customer_Name) from SALES, CUSTOMER
Where SALES. Customer_ID = CUSTOMER. Customer_ID;
If you use the WHERE clause that specifies the Customer_Name column value to query the SALES and CUSTOMER tables, the optimizer can use bitmap to connect the index to quickly return rows that match the connection condition and Customer_Name condition.
The usage of Bitmap-connected indexes is generally restricted:
1) only the columns in the dimension table can be indexed.
2) The columns used for connection must be the primary key or unique constraint in the dimension table. If the column is a composite primary key, each column in the connection must be used.
3) bitmap join indexes cannot be created for the index organization table, and the limitations applicable to conventional Bitmap indexes are also applicable to bitmap join indexes.