Indexes play a huge role in database access performance. Designing a proper index is crucial to system performance adjustment. Using indexes is the most difficult aspect in the database development process. Pay attention to the following aspects when designing indexes:
- In the OLTP system, try to avoid full table scanning and try to make the vast majority of operations accessible through indexes.
- The number of indexes on a table with a large data volume and frequent changes is not too large. Excessive indexes will slow down the insert, update, and delete operations, resulting in a large amount of Io. If there are more than eight indexes on a table, check whether these indexes are necessary. (However, it is worth noting that this principle may be overly exaggerated. Because the majority of OLTP systems have less than 10% write operations, and most of the operations are read. Therefore, if the design is reasonable, it is unreasonable to say that the number of indexes on a table exceeds the maximum value)
- If the number of indexes is too large, it is recommended to delete some columns with composite indexes with independent indexes. Composite queries can be obtained by using the index result set of two independent columns, which can also ensure query efficiency.
- Do not create indexes for small tables. Index access may be slower. It is more efficient to put small tables into the keep pool.
- The pctfree, inittrans, and maxtrans parameter settings of indexes are very important, especially for indexes with great changes.
- For large indexes, using index partitions improves efficiency.
- Bitmap indexes have good effects on the list-type values (the index size is also small), but Bitmap indexes are not suitable for tables with frequent changes.
- Using function indexes can avoid a large number of unnecessary full table scans.
- If the index contains all the information required for the query, the query does not need to access the table data, which can greatly improve the access efficiency. Therefore, you must establish a reasonable composite index and pay attention to the rationality of SQL statements.
- Make good use of indexes to organize tables
- You can use reverse index to remove index-related hot blocks.
- As the data changes, the index efficiency will decrease. Therefore, regular index reconstruction can greatly improve the performance.
- When the CBO optimizer is used, ensuring that the table and index data are well analyzed is the key to ensuring that the optimizer selects the best execution plan.
- Delete all unnecessary indexes. The new features provided by Oracle 9i allow DBAs to track index usage, use this function to locate unused indexes, and delete these indexes
This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/wyzxg/archive/2008/08/27/2835619.aspx
