Seven precautions for creating an Oracle Index

When creating an Oracle index, we need to pay attention to some issues. The following describes some precautions for creating an Oracle index. We hope this will help you learn how to create an Oracle index.

1. Generally, you do not need to create an index for a small table;

2. Even for large tables, if the data frequently needs to be queried does not exceed 10% to 15%, there is no need to create an index for them. At this time, the indexing overhead may be much higher than the performance improvement. This ratio is just an empirical data. If the database administrator needs to draw a more accurate conclusion, test and analysis are required.

3. For example, for columns with few duplicates, especially those columns with unique constraints. Creating an index on these columns often results in very good results. For example, if you want to query all columns with non-null values in a mix of Null values and non-Null values, it is best to set an index for these columns. If you frequently need multi-table join queries, you can set indexes on the connected columns to get twice the result with half the effort.

4. The database administrator needs to optimize the database index over a period of time, such as one year. Remove the removed items and adjust them to improve the database performance.

5. Generally, the more indexes a table has, the faster the query speed. However, the table update speed is reduced. This is mainly because the table update speed (such as inserting a record into the table) increases with the increase of indexes. This is mainly because related index information needs to be updated while updating records. Therefore, if it is appropriate to create an index in a table, a balance between the update speed and the query speed needs to be obtained.

6. Some data warehouses or decision-making database systems are mainly used for query. Related records are often imported during database initialization. In this case, you can set more indexes to improve the query performance of the database. At the same time, because the record is not updated much, the update speed will not be affected when there are many indexes. Even if you need to import a large amount of data at the beginning, you can disable the index first. After the data is imported, enable the index. This method can be used to reduce the impact of indexes on data updates. On the contrary, if the tables often need to update records, such as some transaction application systems, data update operations are common. If you create too many indexes in a table, the update speed will be affected.

7. Bitmap indexes.

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.

How to view Oracle indexes and views

Connect to the Oracle database through the OCI8 Interface

Instances that use oracle Stored Procedure Paging

C # connect to the Oracle database to query the changed data

Three methods for automatic Oracle Database Backup