Bookshelf Home | Contents | Index | Search | PDF |
Siebel Analytics Performance Tuning Guide > The Database Servers > Periodic Tuning Tasks >
Drop Indexes
Indexes can greatly improve query performance and are among the most effective means to improve query performance. They should always be considered in a data warehousing environment which is a "read-mostly" environment. Nevertheless, indexes do incur significant costs, and you should drop any indexes which are not being used by queries.
Indexes incur the following costs:
- Occupy physical database storage.
The amount of space an index occupies depends on the size of the table, the size and number of columns in the index, and the kind of index.
- Require more processing time for update operations.
- Require periodic maintenance.
- Can require more query compilation time.
Indexes provide alternative access paths. The cost-based optimizers generate execution plans for potential indexes, calculate the cost of executing the alternative, and then compares the cost of each plan. If the database server never references an index, then these indexes are adding time to the compilation of some queries, and this can bear on the query's performance.
Which Indexes Are Used
You can determine whether queries are using an index with tools provided by database vendors. You can use the EXPLAIN PLAN command to determine whether a specific query uses an index. You can reduce the time require when you follow this approach by looking at a representative sample of you queries.
Oracle 9i Databases
Oracle includes an ALTER INDEX MONITORING USAGE command that collects statistics over a period of time. The most effective approach is to monitor your query workload during a representative interval.
Bookshelf Home | Contents | Index | Search | PDF |
Siebel Analytics Performance Tuning Guide Published: 18 April 2003 |