How to select an appropriate index type in Oracle Database

The index is like a dictionary directory. With the dictionary directory, we can quickly find the entries we need. The same is true for databases. With the Oracle database index, related statements can quickly locate the record location without having to locate the entire table.

Whether to create an index in a table does not affect the use of the Oracle database or the use of 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 the data table records add, delete, and update changes. Therefore, indexes in database tables are definitely not more beneficial. Specifically, in terms of index creation, I have the following suggestions for you.
Recommendation 1: Use Bitmap indexes for fields with a small base.
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 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 bitmap 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 greater than or not equal to the number as the limit condition in the Condition Statement, the standard index is often used to have a greater advantage.
Therefore, in 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 limit item for query. 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.
Suggestion 2: some restrictions on creating an index.
Not to mention, the more indexes a table or column creates, the better. On the contrary, the more indexes you create, sometimes it will affect the overall performance of the database. Therefore, there are still some restrictions when creating an index.
First, do not index tables with few records. When designing an application system, for example, designing an ERP system database, it has thousands of tables. However, not every table has a large number of records. On the contrary, there are about half of data tables, and it may not store more than data records. Such as the employee Logon account password table and Enterprise Department information table. For tables with few records, we recommend that you do not index them. Do not create indexes for tables or fields.
Second, if the content in the table is relatively large, but this table is basically not very difficult to query, you only need to create an index on the table; instead, you do not need to create an index on the field. For example, in the ERP system, there is a table named "AD_Table ". It stores information about related tables in the database. This table is used only when the database is designed. Therefore, although there are many records in this table, there is usually no need to create a column-level index for this table because users use less. Instead of using Table indexes.
Third, in some NULL fields, you must determine whether to create an index based on the actual situation. For example, there is a personnel file table with two fields on it: "ID card number" and "region ". Sometimes, for some reason, the company requires all employees to register their ID card numbers in the system to facilitate their payroll card and social insurance. Therefore, personnel management may need to frequently query the system to see if there is any employee information without an ID card number. In this case, we need to use the condition "is null" to query the records we need. Therefore, to improve query efficiency, if a record may be empty and needs to be queried with NULL as the condition, it is best to add a cable reference to this field, and it is best to create a bitmap index. On the contrary, although the condition NULL may be used as the limit statement for queries, if not many queries are used, there is no need to create an index for them.
Recommendation 3: Index Design for multi-table join queries.
For example, there is a human affairs management system. The personnel ideally know the social security payment of employees. He needs to know the employee's name, position, household registration nature (the farmer's household registration fee is different from the resident's household registration fee), the payment situation, and so on. However, the information is contained in different tables. To improve the database performance, some sequence numbers may be stored in the table, rather than the specific content. For example, in the Social Security table, the employee ID is stored, rather than the employee name. Therefore, to obtain this report, you may need to associate the basic employee information table, company organization structure table, and other tables to query the content required by the user.
Therefore, we need to use Join statements to associate these tables. To improve the query efficiency of the database, it is best to create indexes for the fields used for association. This can significantly improve the query speed.
Recommendation 4: find a balance between the table update speed and the query speed.
As we all know, indexes do not affect the use of databases. They are mainly used to improve the query efficiency of databases. However, when the data in the database table is updated, including the addition, deletion, and change of records, all indexes that are existing are updated.
Obviously, indexes can increase the query speed. However, it will also have a negative impact on some table update operations. The more indexes you create in a table, the greater the negative impact. Therefore, when setting indexes, the database administrator must note that a balance point is required between the two.
According to the general theory, when most of a table is used for query and update, more indexes are required. On the contrary, when a table has fewer records and fewer queries, do not create too many indexes to avoid adverse effects on the update speed.
In practice, if a table is frequently called by a view, it is better to set more indexes.