Table index is one of the most important areas, while optimizing a query.
Here is the best practice of using these indexes, to get the optimal performance gain.
1. Consider creating indexes 'only' on those columns which are frequently used in WHERE, ORDER BY and GROUP BY clauses.
2. Do not create duplicate or un-necessary indexes. These indexes hampers insert, delete and update performance of any query. Consider dropping any such indexes, if already there.
3. Make sure table/index statistics are updated regularly.
4. Try having your indexes on small number of columns as much as possible.
(Consider this while creating Covered indexes and composite indexes)
5. Try having a clustered index for each table. Highly active tables without a clustered index can observe a 'hot spot' at the end of the table memory, as each insert will try to claim the same point in memory.
While choosing clustered index,
Consider columns on which "range" query is expected.
Consider columns used in 'order by' , 'group by' clause or in 'joins'.
Consider primary key, if it is used in 'where' clauses and if it has randomize inserts.
Avoid columns which are frequently 'updated' or 'inserted with steadily increasing value'
(identity column).
6. Non clustered indexes are good, where the queries return few rows or not in range.
7. Covered queries (using composite index) can provide excellent response time for specific queries when the leading columns are used.
8. Columns with very few data set values should be avoided while creating index.
E.g. A 'bit' type column.
9. Recompile the table whenever a table or associated index definition is modified.
10. Numeric columns perform better than string types, when indexed.
11. Indexes on temporary tables which are created inside a SP might not get used in same SP.
Create the temp table in a wrapper SP, and make a call to main SP within this wrapper SP.
12. If a query is getting used more frequently, consider having a 'covered index'.
It is a non clustered index which includes all the columns involved in the query, thus avoids reading data pages.
Note: Unless you see the query is used, more frequently avoid covered index as, this impacts insert and update performance on the other hand.
13. If you think, the available index should be used, but any how optimizer is skipping it, try forcing the index to be used.
e.g.
14. If the index key of a table has unique values, define this index as an unique. This way query optimizer knows only one row is expected to match this key. No further scanning will be done.
15. Whenever possible, use fixed-length, non-null types of columns to index.
16. Make sure, the data types of the join columns in different tables are compatible. Optimizer may not use an index in such cases.
17. If the index size becomes too big, (includes too many columns), consider to have a artificial column defined (lookup table) to map these columns.
18. Avoid creating nonclustered indexes before and then clustered indexes. When you create the clustered index all previous nonclustered indexes are rebuilt.
SQL CREATE INDEX Syntax
SQL CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE INDEX Example
The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:
If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:
Use hints which cause the cost based optimizer to be used.
Here is the best practice of using these indexes, to get the optimal performance gain.
1. Consider creating indexes 'only' on those columns which are frequently used in WHERE, ORDER BY and GROUP BY clauses.
2. Do not create duplicate or un-necessary indexes. These indexes hampers insert, delete and update performance of any query. Consider dropping any such indexes, if already there.
3. Make sure table/index statistics are updated regularly.
4. Try having your indexes on small number of columns as much as possible.
(Consider this while creating Covered indexes and composite indexes)
5. Try having a clustered index for each table. Highly active tables without a clustered index can observe a 'hot spot' at the end of the table memory, as each insert will try to claim the same point in memory.
While choosing clustered index,
Consider columns on which "range" query is expected.
Consider columns used in 'order by' , 'group by' clause or in 'joins'.
Consider primary key, if it is used in 'where' clauses and if it has randomize inserts.
Avoid columns which are frequently 'updated' or 'inserted with steadily increasing value'
(identity column).
6. Non clustered indexes are good, where the queries return few rows or not in range.
7. Covered queries (using composite index) can provide excellent response time for specific queries when the leading columns are used.
8. Columns with very few data set values should be avoided while creating index.
E.g. A 'bit' type column.
9. Recompile the table whenever a table or associated index definition is modified.
10. Numeric columns perform better than string types, when indexed.
11. Indexes on temporary tables which are created inside a SP might not get used in same SP.
Create the temp table in a wrapper SP, and make a call to main SP within this wrapper SP.
12. If a query is getting used more frequently, consider having a 'covered index'.
It is a non clustered index which includes all the columns involved in the query, thus avoids reading data pages.
Note: Unless you see the query is used, more frequently avoid covered index as, this impacts insert and update performance on the other hand.
13. If you think, the available index should be used, but any how optimizer is skipping it, try forcing the index to be used.
e.g.
select t1.col_1, t1.col_2, t2.col_1
from TAB_A t1 (index idx_one),--Here we are forcing the index 'idx_one'on TAB_A.
TAB_B t2
where t2.col_1 = t2.col_1
from TAB_A t1 (index idx_one),--Here we are forcing the index 'idx_one'on TAB_A.
TAB_B t2
where t2.col_1 = t2.col_1
14. If the index key of a table has unique values, define this index as an unique. This way query optimizer knows only one row is expected to match this key. No further scanning will be done.
15. Whenever possible, use fixed-length, non-null types of columns to index.
16. Make sure, the data types of the join columns in different tables are compatible. Optimizer may not use an index in such cases.
17. If the index size becomes too big, (includes too many columns), consider to have a artificial column defined (lookup table) to map these columns.
18. Avoid creating nonclustered indexes before and then clustered indexes. When you create the clustered index all previous nonclustered indexes are rebuilt.
SQL CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name ON table_name (column_name) |
SQL CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:CREATE UNIQUE INDEX index_name ON table_name (column_name) |
CREATE INDEX Example
The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:CREATE INDEX |
CREATE INDEX |
Use hints which cause the cost based optimizer to be used.
select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias
