尽管查询优化在查询执行时避免使用非选择性的索引,但SQL引擎必须继续维护表的所有索引,无论它们是否被使用。维护索引在写入密集型应用中会产生一个显著的CPU和I/O资源的需求。换句话说,除非必要,否则不要建立索引。
如果你要决定是否创建新的索引来优化语句,那么你也可以用EXPLAIN PLAN语句来决定优化器是否在应用运行时选择使用这些索引。如果你创建新的索引来优化当前已经解析了的语句,那么Oracle会使该语句失效。
SQLAccess顾问(SQLAccess Advisor)是另外一种手动决定需要哪些索引的手段。当在Oracle企业管理器调用或通过DBMS_ADVISOR包的API运行时,这个顾问会推荐一些索引。SQLAccess顾问为一个特定的方案(schema)推荐一个工作量或产生一个理想的工作量。工作量的来源有很多,如SQL Cache的当前内容,用户定义的一组SQL语句,或者一组SQL优化语句(SQL Tuning set)。根据给定的一个工作量,SQLAccess顾问会产生一组建议来为你选择要实现的索引。一个实现的脚本也会通过Oracle企业管理器来提供作为手动或自动执行。
一个关键点是应该在哪个字段或表达式上建立索引。根据以下指引来选择索引的关键点:
1. 考虑对经常在WHERE子句中使用的字段创建索引。
2. 考虑在SQL语句经常连接表的字段上创建索引。
3. 考虑对高选择性的字段创建索引。索引的选择性是指表中含有索引值的行的百分比。如果很少行含有相同的值,那么索引的选择性就是最优的。
注意:Oracle会在你定义完整性约束的唯一键和主键上自动创建索引或使用已存在的索引。
如果数据分布是不均匀的,在低选择性上的列创建索引是有帮助的,这样,一两个值可能比其他值出现得更少。
4. 不要在很少不同值的字段或表达式上使用标准的B-树索引。那样的字段或表达式的选择性很差,因此不能优化性能,除非经常选择的值出现的次数比其他值少。在那种情况下,你可以使用位图索引,除非索引经常被修改,如在一个高并发性的OLTP应用上。
5. 不要在经常修改的字段上创建索引。修改建立了索引的字段的UPDATE语句和修改建立了索引的表的INSERT、DELETE语句会比没有索引时花费更多的时间。那些语句也必须修改索引里的数据。它们也会产生额外的撤销(undo)和重做(redo)日志。
6. 不要在只以函数或操作符出现WHERE子句中的字段建立索引。一个使用除MIN或MAX以外的函数,或者一个操作符的WHERE子句是不会使用索引的访问路径的,除非建立的是基于函数的索引。
7. 在有大量并行的INSERT,UPDATE和DELETE语句访问父表和子表时,考虑对引用完整性约束的外键创建索引。那样的索引运行更新和删除父表记录的同时,不会产生子表的共享锁。
8. 当选择建立一个索引时,考虑从查询中得到的性能提升是否抵得过INSERT,UPDATE和DELETE的性能损失,还有保存索引所需的空间。你可以比较有索引和没有索引时SQL语句执行时间来体验。计量运行时间可以用SQL跟踪工具。
组合索引包含超过一个字段。组合索引比单字段索引提供额外的优点:
1. 加强选择性。有时,单个字段的选择性很差,但组合起来可以得到更高的选择性。
2. 减小I/O。如果查询的所有字段都在组合索引中,那么Oracle可以从索引中直接返回这些值,而不用去访问表。
如果SQL语句包含的构造使用了组合索引的前面部分,那么这个语句可以使用包含该索引的访问路径。
索引的前面部分是指CREATE INDEX语句指定的字段列表的第一个及后续的字段。以下面的CREATE INDEX语句为例:
CREATE INDEX comp_ind ON table1(x, y, z);
1. x,xy和xyz是索引的前面部分。
2. yz,y和z不是索引的前面部分。
根据以下指引来选择字段创建组合索引:
1. 考虑那些在WHERE子句中用AND操作符来组合的字段,尤其是那些单个选择性差,但组合后的选择性强的字段来创建组合索引。
2. 如果几个查询都选择同一组字段的一个或多个,那么考虑创建一个包含该组字段的组合索引。
当然,考虑这些因素的同时也要考虑一般性能优点和权衡前面描述的各种情况。
组合索引的字段顺序
根据以下指引决定组合索引的字段顺序:
1. 创建的索引以便在WHERE子句中可以组成索引的前面部分。
2. 如果在WHERE子句中的一些字段被使用的更加频繁,那么要确保这些字段在创建的索引中是前面部分,这样语句才能使用该组合索引。
3. 如果在WHERE子句中的所有字段都相同,但数据经常按照一个字段进行排序,那么把该字段放在组合索引的第一位。
写使用索引的语句
尽管在你创建索引后,优化器不会简单地因为索引存在就使用索引的访问路径。优化器只会在语句包含一个使得访问路径可用的构造时才使用那样的访问路径。为了让查询优化器使用索引访问路径,要确保语句含有使得那样的访问路径可用的构造。
写避免使用索引的语句
有些情况下,你想让SQL语句不走索引访问路径。你想这样做,是因为你知道索引的选择性不是很高,而且全表扫描跟有效率。如果语句含有索引访问路径的构造,那么你可以用下面一种方法来强制优化器使用全表扫描:
1. 使用NO_INDEX提示来给查询优化器最大的灵活性,同时不允许使用某个索引。
2. 使用FULL提示来指导优化器选择全表扫描来代替索引扫描。
3. 使用INDEX或INDEX_COMBINE提示来指导优化器使用一个索引或一组索引来代替另一个索引。
平行化执行有效地使用索引。它不会执行平行索引范围扫描,但它会为平行内嵌循环连接执行平行索引查找。如果一个索引的选择非常高(每个索引关键字的行数很少),那么使用顺序索引扫描比平行表扫描更好。
重建索引
你可能想重建一个索引来缩小它,减少碎片空间,或者改变它的存储特性。当创建一个新的索引,而这个索引是一个已经存在的索引的子集时,或者当用新的存储特性来重建一个已经存在的索引时,Oracle会使用已经存在的索引代替基表来提高建立索引的性能。
注意:在索引创建或重建之后,避免调用DBMS_STATS,包括CREATE或REBUILD语句中使用COMPUTE STATISITCS。
尽管如此,有些情况下,使用基表代替已存在的索引可以得到性能上的好处。考虑在一个执行了大量DML语句的表上的一个索引,由于DML的原因,索引的空间大小可能增长到每个块只有50%的使用率,或者更少。如果这个索引引用了表的大部分字段,那么索引的实际大小可能超过了表的大小。在这种情况下,使用基表比索引创建索引的速度更快。
精简索引
你可以使用ALTER INDEX语句加上COALESCE选项来合并子叶块。这个选择让你能够合并一个索引的子叶而便清空一些块来重用。你也可以在线重建索引。
使用非唯一索引来强制唯一性
你可以使用表中已存在的索引来为唯一约束或主键约束的唯一性方面来强制唯一性。这种方法的优点是当约束禁用时,索引仍然可用有效。因此,重新启用一个禁用的唯一约束或主键约束不需要重建与约束相关的唯一索引。这样在大表的启用操作中可以节省非常大的时间。
使用非唯一性索引来强制唯一性还可以让你消除索引冗余。如果一个主键字段已经包含在一个组合索引的前头,你就不必在这个字段上建立唯一索引了。你可以使用已存在的索引来启用并强制约束。不用重复索引也让你节省很多空间。但是,如果已存在的索引是分区的,那么索引的分区关键字也必须是唯一关键字的子集;否则,Oracle会创建一个额外的唯一索引来强制约束。
使用Enabled Novalidated约束
一个enabled novalidated约束对于新数据来说,与一个enabled validated约束相似。将一个约束设置为enabled novalidated意味着任何新的数据想进入表中必须符合约束。但不会检查已经存在的数据。通过将一个索引设置为enabled novalidated,你可以不对表上锁就可以启用约束。
下面展示了创建表的约束和索引的方法:
1. 连同约束一起创建表。NOT NULL约束可以不给定名字,并且应该创建为启用的、有效的。其他约束(CHECK,UNIQUE,PRIMARY KEY和FOREIGN KEY)应该给定名字和创建为禁用。
注意:默认地,约束是创建为ENABLED状态的。
2. 加载旧的数据到表中。
3. 创建所有索引,包括约束所需的索引。
4. 将所有约束设置为enalbed novalidated.这个操作应该先进行主键,再进行外键。
5. 允许用户查询和修改数据。
6. 为每个约束用单独的ALTER TABLE语句来验证所有约束。这个操作应该先进行主键,再进行外键。
例如:
现在你可以用Import或Fast Loader来加载数据到表t中。
这时,用户可以开始在表t上执行INSERT,UPDATE,DELETE和SELECT了。
现在约束是启动的且有效的。
使用基于函数的索引来增强性能
基于函数的索引包含不是通过函数转换,如UPPER函数,就是包含一个表达式,如COL1 + COL2的字段。有了基于函数的索引,你就可以保存与计算相关的表达式到索引中。
例如,用UPPER或者LOWER定义的基于函数的索引允许进行不分大小写的搜索。索引由以下语句创建:
查询的语句如下:
使用分区索引来提高性能
与分区表相似,分区索引可以提高管理性、可用性、可伸缩性和性能。它们既可以独立分区(全局索引(global indexes)),也可以自动与表的分区方法联系(本地索引(local indexes))。
使用索引组织表来提高性能
索引组织表与普通表的区别在于表的数据是存放在与其关联的索引中的。改变表的数据,如增加一行、更新行或删除行,只会更新索引。因为数据行保存在索引中,索引组织表提供了基于关键字的更快的访问那些包含精确匹配或范围搜索或两者都包含的查询的表数据。
索引组织表支持全局哈希索引,并且在多用户OLTP环境中提供性能优势。
使用位图索引提高性能
位图索引可以实质地提高符合以下特性的查询的性能:
1. WHERE子句中包含多个在中低基数字段的判定。
2. 在这些中低基数字段的单个判定会选择到大量的行。
3. 查询中使用的位图索引是在这些中低基数字段的一些或全部上建立的。
4. 查询中的表包含非常多行。
你可以用多个位图索引来评估单个表的查询条件。因此,在包含很长的WHERE子句的复杂的特定的查询中,位图索引有很大优势。位图索引在聚合查询和优化连接方面也提供最优性能。
使用位图连接索引来提高性能
除了单表上的位图索引,你还可以创建位图连接索引来连接两个或多个表。位图连接索引是通过提前执行限制来减少必须连接的数据量的一种节省空间的方法。对于表的一个字段的每个值,位图连接索引在另一个表中保存相应行的rowid。在数据仓库的环境中,连接条件是在维度表的主键字段和实际表的外键字段之间的equi-inner连接。
使用域索引来提高性能
域索引是用由用户定义的索引类型来提供的索引逻辑来建立的。索引类型提供了一种有效的机制来访问那些符合操作符判定的数据。典型地,用户定义的索引类型是Oracle选项的一部分,如Spatial选项。例如,SpatialIndextype允许在给定边界上重复的spatial数据进行有效的搜索。
Cartridge决定了你可以在创建和维护域索引指定的参数。相似地,域索引的性能和存储特性可以在特定cartridge文档中找到。
在合适的cartridge文档中查阅以下信息:
1. 可以对哪些数据类型进行索引?
2. 提供哪些索引类型?
3. 索引类型支持哪些操作符?
4. 域索引如何创建与维护?
5. 我们如何在查询中高效地使用操作符?
6. 性能特性是什么?
注意:你也可以用CREATE INDEXTYPE语句来创建索引类型。
使用簇(Clusters)提高性能
簇是一组由一个或多个表组成的群,这些表物理地保存在一起,因为它们共享公共的字段并且通常在一起使用。因为相关的行物理地保存在一起,所以可以改进磁盘访问时间。
要创建一个簇,可以用CREATE CLUSTER语句。
根据以下指引来决定是否将表簇化:
1. 在连接语句中,将应用经常访问表簇化。
2. 如果应用只是偶尔连接的表或者经常修改它们的公共字段的值,那么不要将这些表簇化。修改行的簇值比修改不是簇化表的值要花更长时间,因为Oracle需要移植行到另外一个块中以便维护簇。
3. 如果应用经常只对其中一个表进行全表扫描,那么不要将这些表簇化。对一个簇化表进行全表扫描比非簇化表的全表扫描要花费更长的时间。Oracle很可能读取更多的块,因为表都保存在一起了。
4. 如果你经常选择一条主记录然后选择相应的详细记录,那么簇化这些主细表(master-detail tables)。详细的记录是和主记录一起保存在相同数据块的,所以当选择它们时,它们很可能还在内存中,这样Oracle就不要执行更多的I/O。
5. 如果你经常选择同一条主记录的许多条详细记录,那么把详细表单独保存到一个簇中。这种方法改进了选择同一条主记录的多条详细记录的查询的性能,但不会降低主表的全表扫描的性能。另一种替代方法是使用索引组织表。
6. 如果所有表的相同簇值的数据超过一个或两个Oracle块的话,不要簇化这些表。访问簇化表的一行,Oracle会读取包含该行值的所有行的块。如果这些行占用了多个块,那么访问单个行比在非簇化表的单行需要读取更多块。
7. 当每个簇关键字的值的行数变化很大,那么不要簇化这些表。这会引起低基数关键字值的空间浪费;它也会为高基数关键字值的冲突。冲突会降低性能。
使用哈希簇(Hash Clusters)来提高性能
哈希簇通过对每行的簇关键字值执行哈希函数后的数据将表分组。所有相同簇关键字值的行一起保存在磁盘上。根据应用的需要来考虑哈希簇的优缺点。你应该要体验并比较一个特定表作为哈希簇保存和用一个索引单独保存的处理时间。
根据以下指引来使用哈希簇:
1. 如果经常通过SQL语句来访问,而该SQL语句的WHERE子句中包含相等条件,且该条件使用相同字段或字
段的组合,就将这些表进行哈希簇。指定这些字段或字段的组合为簇关键字。
2. 如果你能决定需要多少空间来保存一个给定簇关键字值的所有行,包括将要或将来会插入的行,那么将这个表保存到哈希簇中。
3. 当行对应的哈希函数的每个值是以一个特定字段的升序进行排序,且用这些排序了的簇化的数据进行操作的相应时间有所改进的话,使用排序的哈希簇。
4. 如果应用经常进行全表扫描和你必须分配大量空间给哈希簇来预见表的增长时,不要将表保存在哈希簇中。那样的全表扫描必须读取分配给哈希簇的所有块,尽管有些块只包含几行。将这些表单独保存可以减少全表扫描要读取的块。
5. 如果应用经常修改簇关键字值,那么不要将这些表保存到哈希簇中。修改一行的簇关键字值比修改一个非簇化表的值要更长时间,因为Oracle需要移植修改的行到另一个块来维护簇。
将单个表保存到哈希簇中是有用的,不管这个表是否经常与其他表连接,只要表基于以上因素是适合的。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/114170.html