1. Create Indexes After Inserting Data in Tables
Data is often inserted or loaded into a table using either the SQL*Loader or an import utility. It is more efficient to create an index for a table after inserting or loading the data. If you create one or more indexes before loading data, the database then must update every index as each row is inserted.
Data is often inserted or loaded into a table using either the SQL*Loader or an import utility. It is more efficient to create an index for a table after inserting or loading the data. If you create one or more indexes before loading data, the database then must update every index as each row is inserted.
2. Index the Correct Tables and Columns
- To improve performance on joins of multiple tables, index columns used for joins.
- Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.
- Non indexed Foreign key can lock child table(s) when we need to delete from parent table.
- Small tables do not require indexes. If a query is taking too long, then the table might have grown from small to large.
3. Some Columns should consider for indexing
- To improve performance on joins of multiple tables, index columns used for joins.
- Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.
- Non indexed Foreign key can lock child table(s) when we need to delete from parent table.
- Small tables do not require indexes. If a query is taking too long, then the table might have grown from small to large.
3. Some Columns should consider for indexing
Some columns (depending on values) are Strong candidates for indexing when
- There is a wide range of values (good for regular/btree/default indexes).
- There is a small range of values (good for bitmap indexes).
- The column contains many nulls, but queries often select all rows having a value.
- There is a wide range of values (good for regular/btree/default indexes).
- There is a small range of values (good for bitmap indexes).
- The column contains many nulls, but queries often select all rows having a value.
4. Some Columns should NOT consider for indexing
Some columns (depending on values) should not consider for indexing when -
- There are many nulls in the column and you do not search on the NOT NULL values.
- LONG and LONG RAW columns cannot be indexed.
5. Order Index Columns for Performance
The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.
If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also seeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.
- There are many nulls in the column and you do not search on the NOT NULL values.
- LONG and LONG RAW columns cannot be indexed.
5. Order Index Columns for Performance
The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.
If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also seeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.
6. Limit the Number of Indexes for Each Table
A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated. Thus, there is a trade-off between the speed of retrieving data from a table and the speed of updating the table.
For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.
A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated. Thus, there is a trade-off between the speed of retrieving data from a table and the speed of updating the table.
For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.
7. Drop Indexes That Are No Longer Required
Consider dropping an index if:
- It does not speed up queries.
- The index must be dropped before being rebuilt.
Consider dropping an index if:
- It does not speed up queries.
- The index must be dropped before being rebuilt.
- Regularly perform Index Monitoring. Check if the indexes are using by the application queries or not.
8. After adding a new index
- Analyze the index and compute statistics.
