do not build indexes unless necessary. 索引是非常占资源的
To maintain optimal performance, drop indexes that an application is not using. 不用的索引可以drop掉, 通过toad可以生成监控脚本, 监控一段时间内, 哪些脚本没有被使用 (ALTER INDEX MONITORING USAGE)
什么时候创建索引
Consider indexing keys that appear frequently in WHERE clauses.
Consider indexing keys that frequently join tables in SQL statements. For more information on optimizing joins.
Choose index keys that have high selectivity. The selectivity of an index is the percentage of rows in a table having the same value for the indexed key. An index's selectivity is optimal if few rows have the same value.'
当然, 如果你的索引列上的值, 99%都一样, 那么恐怕就没有必要在这个列上建立索引了.
Do not use standard B-tree indexes on keys or expressions with few distinct values.
Do not index frequently modified columns.
Do not index keys that appear only in WHERE clauses with functions or operators.
要给外键建立索引 (大师有提过)
When choosing to index a key, consider whether the performance gain for queries is worth the performance loss for INSERTs, UPDATEs, and DELETEs and the use of the space required to store the index.
组合索引
CREATE INDEX comp_ind ON table1(x, y, z);
那么, 可以走索引的方式是: x, xy, and xyz combinations of columns are leading portions of the index
你想避免使用索引时: 比如, 你知道 full table scan 效率更高时:
Use the NO_INDEX hint to give the query optimizer maximum flexibility while disallowing the use of a certain index.
Use the FULL hint to instruct the optimizer to choose a full table scan instead of an index scan.
Use the INDEX or INDEX_COMBINE hints to instruct the optimizer to use one index or a set of listed indexes instead of another.
重建索引
Usually, ALTER INDEX ... REBUILD is faster than dropping and re-creating an index,
Function 索引
CREATE INDEX uppercase_idx ON employees (UPPER(last_name));
SELECT * FROM employees WHERE UPPER(last_name) = 'MARKSON';
Partitioned Indexes
类似 partitioned table
Oracle Database supports both range and hash partitioned global indexes. In a range partitioned global index, each index partition contains values defined by a partition bound.
In a hash partitioned global index, each partition contains values determined by the Oracle Database hash function.