oracle索引如何选择和优化?
我想知道是否有创建索引的一般规则.我如何选择应在此索引中包含哪些字段或何时不包含它们?
I would like to know if there are general rules for creating an index or not. How do I choose which fields I should include in this index or when not to include them?
我知道它总是取决于环境和数据量，但我想知道我们是否可以制定一些全球公认的关于在 Oracle 中创建索引的规则.
I know its always depends on the environment and the amount of data, but I was wondering if we could make some globally accepted rules about making indexes in Oracle.
推荐答案
Oracle 文档为索引选择提供了一组出色的注意事项:http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004
The Oracle documentation has an excellent set of considerations for indexing choices: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004
19c 更新:https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/designing-and-developing-for-performance.html#GUID-99A7FD1B-CEFD-4E91-9486-2CBBFC2B7A1D
引用:
考虑在 WHERE 子句中频繁使用的索引键.
Consider indexing keys that are used frequently in WHERE clauses.
考虑经常用于连接 SQL 语句中的表的索引键.有关优化连接的更多信息，请参阅使用哈希集群提高性能"部分.
Consider indexing keys that are used frequently to join tables in SQL statements. For more information on optimizing joins, see the section "Using Hash Clusters for Performance".
选择具有高选择性的索引键.索引的选择性是表中具有相同索引键值的行的百分比.如果少数行具有相同的值，则索引的选择性是最佳的.注意:Oracle 会在您使用完整性约束定义的唯一键和主键的键和表达式上自动创建索引或使用现有索引.如果数据分布有偏差，因此一两个值的出现频率远低于其他值，那么索引低选择性列会很有帮助.
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. Note: Oracle automatically creates indexes, or uses existing indexes, on the keys and expressions of unique and primary keys that you define with integrity constraints. Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.
不要在具有很少不同值的键或表达式上使用标准 B 树索引.此类键或表达式通常具有较差的选择性，因此不会优化性能，除非经常选择的键值出现的频率低于其他键值.在这种情况下，您可以有效地使用位图索引，除非在高并发 OLTP 应用程序中频繁修改索引.
Do not use standard B-tree indexes on keys or expressions with few distinct values. Such keys or expressions usually have poor selectivity and therefore do not optimize performance unless the frequently selected key values appear less frequently than the other key values. You can use bitmap indexes effectively in such cases, unless the index is modified frequently, as in a high concurrency OLTP application.
不要索引经常修改的列.修改索引列的 UPDATE 语句和修改索引表的 INSERT 和 DELETE 语句比没有索引时花费的时间更长.此类 SQL 语句必须修改索引中的数据以及表中的数据.它们还会生成额外的撤消和重做.
Do not index columns that are modified frequently. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.
不要索引只出现在带有函数或运算符的 WHERE 子句中的键.使用除 MIN 或 MAX 以外的函数的 WHERE 子句或具有索引键的运算符不会使使用该索引的访问路径可用，除非使用基于函数的索引.
Do not index keys that appear only in WHERE clauses with functions or operators. A WHERE clause that uses a function, other than MIN or MAX, or an operator with an indexed key does not make available the access path that uses the index except with function-based indexes.
在大量并发 INSERT、UPDATE 和 DELETE 语句访问父表和子表的情况下，考虑索引参照完整性约束的外键.这样的索引允许对父表进行 UPDATE 和 DELETE 操作，而无需共享锁定子表.
Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.
选择索引键时，请考虑查询的性能提升是否值得插入、更新和删除的性能损失以及存储索引所需空间的使用.您可能希望通过比较带索引和不带索引的 SQL 语句的处理时间来进行试验.您可以使用 SQL 跟踪工具测量处理时间.
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. You might want to experiment by comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL trace facility.
相关文章