Tips for using Oracle indexes

Indexes have a great effect on the performance of database access, and designing a reasonable index is very important for system performance adjustment. Using indexes is the most difficult point in the database development process. There are several things to keep in mind when designing an index: • In OLTP systems, avoid full table scans as much as possible, making the majority of operations accessible through the index L large volumes of data and frequently variable indexes on tables are not easy to overload, and excessive indexing causes insertions, updates, and deletions to slow down, resulting in a large number of Io, and if more than 8 indexes are on a table, it is necessary to check whether these indexes are required. (It should be noted, however, that this principle is likely to be exaggerated excessively.) Because most OLTP systems have less than 10% write operations, the vast majority of operations are read. Therefore, if the design is reasonable, it cannot be said that the index on a table is more or less unreasonable. L If the number of indexes is too high, it is recommended that all columns have a composite index with independent indexes, the composite query operation can be obtained by merging the index result set of two independent columns, and the query efficiency can also be ensured L Small table not indexed, may be more slow through the index access, the small table into the keep pool more efficient L index Pctfree, Inittrans, Maxtrans parameter settings are very important, especially for very large index changes L Use index partitioning for larger indexes improves efficiency L bitmap indexes are better for list class values (smaller index size), but bitmap indexes are not suitable for frequently changing tables L Use functional indexes to avoid a large number of unnecessary full table scans L If the index contains all the information required by the query, the query does not need to access the data of the table, can greatly improve the efficiency of the access, so pay attention to establish a reasonable composite index, and pay attention to the rationality of SQL statements L Organize tables with good index L can use inverse Sao value Index to eliminate index-related hot blocks • Indexing is less efficient as data changes, so periodically rebuilding the index can be a great help for performance improvement L using the CBO optimizer, ensuring that the data for tables and indexes is well analyzed is key to ensuring that the optimizer chooses the best execution plan L Delete all unnecessary indexes. The new features provided by ORACLE 9i enable DBAs to track the usage of indexes, find unused indexes with this feature, and delete these indexes