How to select an appropriate index type in Oracle Database

The index function is like a dictionary directory. Based on the dictionary directory, we can quickly find the required items. The role of the database is also. Through the Oracle database index, we can quickly locate the record location without having to locate the entire table.

Whether to create an index in the table does not affect the use of the Oracle database or the use of the Oracle database statements. It seems that even if the dictionary does not have a directory, you can still use it. However, if the dictionary does not have a directory, you can imagine that if you want to query an entry, you have to go through the entire dictionary. The same is true for databases.

If no index is created, the database has to query the entire table when querying records. When there are many records in the table, the query efficiency will be very low. Therefore, an appropriate index is a good tool to improve the efficiency of database operation.

However, it does not mean that the more indexes on the table, the better. . Therefore, in the database design process, you still need to select some suitable indexes for the table. This is a standard for indexing. Theoretically, an infinite index can be set for a table. However, the database administrator needs to know that the more indexes in a table, the higher the overhead required for index maintenance.

All indexes must be updated in the database system whenever there is a change in the number of records added, deleted, or updated in the data table. Therefore, indexes in database tables are definitely not more beneficial. Specifically, in terms of index creation, I have the following suggestions for you. Bitmap indexes must be good at using small-base fields.

The base number is a basic definition in the bitmap index. It refers to a non-repeated value in the content of a field in the Oracle database table. For example, the gender field in the employee information table generally has only two values: Male and female. Therefore, the base number is 2. For the Marital Status field, there are only three States: Married, unmarried, and divorced, the base number is 3, and there are only a limited number of values in the national list.

For fields with a small base number to be queried, if you want to search for "female" whose marital status is "married", bitmap indexes can improve the query efficiency. This is mainly because standard indexes are implemented by saving sorted index columns and corresponding rowids in indexes. If we create a standard index on a column with a small base, a large number of records will be returned.

When creating a bitmap index, we will scan the entire table in Oracle and create a bitmap for each value in the index column. If the content is the same, the in-place graph is represented by an identical number. In this case, if the base of this field is small, the efficiency will be very high if you need to query the entire field. Because at this time, the database only needs to find out the same numbers in the bitmap.

In addition to Bitmap indexes when the base of a column in a data table is small, we recommend Bitmap indexes in some special situations. The most common condition is that Bitmap indexes are also recommended if we use the and or condition multiple times in the Where condition. Because when a query consumes some columns with bitmap indexes, these bitmaps can be easily combined with the AND Or operator to quickly find the records you need.

However, it is important to note that Bitmap indexes are not used when conditional statements contain operators. In general, bitmap indexes are advantageous only when the and or operator is used. If the user uses a value greater than or not equal to the number as the limit condition in the Condition Statement, the use of standard indexes has a greater advantage.

Therefore, in Oracle Database settings, bitmap indexes are generally used only in three cases. First, the column base is relatively small, AND you may need to find related records based on the content of these fields; second, when the and or operator is used in the Condition Statement. In addition to these two cases, it is best to use other suitable indexes.

The third case is that NULL is used as the query restriction. Because standard queries generally ignore all NULL value columns. That is to say, if you need to query the information of all employees without an ID card number, the standard index does not accelerate the query speed. In this case, bitmap indexes are used. Because Bitmap indexes record related NULL value columns.