如何选择和优化oracle索引？ [英] How to choose and optimize oracle indexes?

问题描述

我想知道是否有创建索引的一般规则。
如何选择我应该在此索引中包含哪些字段或何时不包括它们？

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

报价：

• 考虑索引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.

当选择索引键时，请考虑查询的性能增益值得INSERT，UPDATE和DELETE的性能损失以及使用存储索引所需的空间。您可能希望通过比较带有和不带索引的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.

这篇关于如何选择和优化oracle索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！