Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以合理使用资源,使那些已经运行了几个小时甚至几天的进程在几分钟得以完成,这样会使您立刻成为一位英雄。这篇文章就将简单的讨论每个索引选项。主要有以下内容:
[1] 基本的索引概念
查询DBA_INDEXES视图可得到表中所有索引的列表,注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。
[2] 组合索引
当某个索引包含有多个已索引的列时,称这个索引为组合(concatented)索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前,查询只能在有限条件下使用该索引。比如:表emp有一个组合索引键,该索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中对第一列(empno)指定一个值,否则就不能使用这个索引键进行一次范围扫描。
特别注意:在Oracle9i之前,只有在使用到索引的前导索引时才可以使用组合索引!
[3] ORACLE ROWID
通过每个行的ROWID,索引Oracle提┝朔梦实バ惺莸哪芰ΑOWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用,可以在任何表中使用和指定rowid列。
[4] 限制索引
限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题:
4.1 使用不等于操作符(<>、!=)
下面的查询即使在cust_rating列有一个索引,查询语句仍然执行一次全表扫描。
select cust_Id,cust_name
from customers
where cust_rating <> 'aa';
把上面的语句改成如下的查询语句,这样,在采用基于规则的优化器而不是基于代价的优化器(更智能)时,将会使用索引。
select cust_Id,cust_name
from customers
where cust_rating < 'aa' or cust_rating > 'aa';
特别注意:通过把不等于操作符改成OR条件,就可以使用索引,以避免全表扫描。
4.2 使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开 发人员在建表时,把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值,就不会使用这个索引(除非索引是一个位图索 引,关于位图索引在稍后在详细讨论)。
4.3 使用函数
如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。
下面的查询不会使用索引(只要它不是基于函数的索引)
select empno,ename,deptno
from emp
where trunc(hiredate)='01-MAY-81';
把上面的语句改成下面的语句,这样就可以通过索引进行查找。
select empno,ename,deptno
from emp
where hiredate<(to_date('01-MAY-81')+0.9999);
4.4 比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。
注意下面查询的例子,account_number是一个VARCHAR2类型,在account_number字段上有索引。下面的语句将执行全表扫描。
select bank_name,address,city,state,zip
from banks
where account_number = 990354;
Oracle可以自动把where子句变成to_number(account_number)=990354,这样就限制了索引的使用,改成下面的查询就可以使用索引:
select bank_name,address,city,state,zip
from banks
where account_number ='990354';
特别注意:不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一 次“全表扫描”。
[5] 选择性
使用USER_INDEXES视图,该视图中显示了一个distinct_keys列。比较一下唯一键的数量和表中的行数,就可以判断索引的选择性。选择性越高,索引返回的数据就越少。
[6] 群集因子(Clustering Factor)
Clustering Factor位于USER_INDEXES视图中。该列反映了数据相对于已索引的列是否显得有序。如果Clustering Factor列的值接近于索引中的树叶块(leaf block)的数目,表中的数据就越有序。如果它的值接近于表中的行数,则表中的数据就不是很有序。
[7] 二元高度(Binary height)
索引的二元高度对把ROWID返回给用户进程时所要求的I/O量起到关键作用。在对一个索引进行分析后,可以通过查询DBA_INDEXES的B-level列查看它的二元高度。二元高度主要随着表的大小以及被索引的列中值的范围的狭窄程度而变化。索引上如果有大量被删除的行,它的二元高度也会增加。更新索引列也类似于删除操作,因为它增加了已删除键的数目。重建索引可能会降低二元高度。
[8] 快速全局扫描
在Oracle7.3后就可以使用快速全局扫描(Fast Full Scan)这个选项。这个选项允许Oracle执行一个全局索引扫描操作。快速全局扫描读取B-树索引上所有树叶块。初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目。
[9] 跳跃式扫描
从Oracle9i开始,索引跳跃式扫描特性可以允许优化器使用组合索引,即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多。下面的程序清单显示出性能的差别:
create index skip1 on emp5(job,empno);
index created.
select count(*)
from emp5
where empno=7900;
Elapsed:00:00:03.13
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE(Cost=4 Card=1 Bytes=5)
1 0 SORT(AGGREGATE)
2 1 INDEX(FAST FULL SCAN) OF 'SKIP1'(NON-UNIQUE)
Statistics
6826 consistent gets
6819 physical reads
select /*+ index(emp5 skip1)*/ count(*)
from emp5
where empno=7900;
Elapsed:00:00:00.56
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=1 Bytes=5)
1 0 SORT(AGGREGATE)
2 1 INDEX(SKIP SCAN) OF 'SKIP1'(NON-UNIQUE)
Statistics
21 consistent gets
17 physical reads
[10] 索引的类型
B-树索引
位图索引
HASH索引
索引编排表
反转键索引
基于函数的索引
分区索引
本地和全局索引
特别说明:关于这部分内容大家感兴趣可以自己查找资料学习!
分享到:
相关推荐
### ORACLE-SQL性能优化大全知识点详述 #### 一、优化基础知识概述 - **性能管理**: - **尽早开始**:性能优化应该在项目的早期就开始考虑,而不是等到后期出现性能瓶颈时才去处理。 - **设立合适目标**:设定...
Oracle数据库中的索引是提升数据查询效率的关键工具,它们...同时,理解索引的工作原理以及其对存储效率的影响,对于数据库管理员和开发人员来说至关重要,这有助于他们做出更明智的决策,以提高系统整体的运行效率。
5. **ora_arch.pdf**:这个名字可能指的是Oracle数据库的架构文档,可能会详细解析Oracle数据库的内部工作原理和设计。 6. **Oracle9i_DBA手册(三)-计划和管理表空间.pdf**:表空间管理是Oracle数据库管理的重要...
中级篇的Oracle SQL优化通常涉及更深入的技术和策略,包括但不限于查询优化、索引优化、表设计优化以及资源管理。以下是对这些知识点的详细说明: 1. 查询优化:查询优化是SQL优化的核心,它涉及如何最有效地执行...
4. **查询优化**:讨论查询执行计划、索引策略、分析工具(如EXPLAIN PLAN)和SQL优化器的工作原理,以提高查询性能。 5. **集合操作**:涵盖UNION、INTERSECT和EXCEPT等集合操作,以及JOIN(包括内连接、外连接和...
本文将深入探讨Oracle索引的原理、创建方法,并结合SQL优化策略,帮助你提升数据库查询速度,降低资源消耗。 首先,我们要理解索引的基本概念。在数据库中,索引类似于书籍的目录,它为数据提供快速访问的途径。...
通过对《Oracle查询优化改写技巧与案例》相关内容的深入探讨,我们不仅了解了Oracle查询优化的基本原理和技术要点,还学习了一些实用的技巧和方法。希望这些知识能够帮助读者更好地理解和掌握Oracle查询优化的核心...
5. **数据量比例**: 如果查询返回的记录数量占表总记录的较大比例(通常超过20%),Oracle优化器可能会认为全表扫描(FULL TABLE SCAN)比索引扫描更有效率。全表扫描能一次性读取多个数据块,对于大数据量查询,...
Oracle是全球最大的数据库管理...通过解决这些试题,学习者将能够深化对Oracle数据库原理和实践的理解,为实际工作或考试做好准备。务必仔细研究每个问题,深入探究背后的数据库理论,以便更好地掌握Oracle数据库技术。
理解Oracle优化器的工作原理和案例对于数据库管理员来说至关重要,可以帮助他们调整和优化SQL执行,从而提高数据库的性能和效率。本篇分析将基于盖国强先生的著作《Oracle优化器案例与原理分析》进行深入探讨。 ...
标题中的“HDU数据库单元原理(Oracle)”是指在华为的HLR(Home Location Register)系统中,关于数据库单元的设计和实现原理,特别是基于Oracle数据库系统的部分。HLR是移动通信网络中的核心组件,用于存储用户的...
PPT可能包含SQL查询优化、索引策略、表分区、数据库调优工具(如SQL Tuning Advisor)的使用等内容。这些技巧对于提升系统性能至关重要。 故障恢复和备份是保持数据完整性的关键。PPT会讲解Oracle的备份类型(如...
8. **后台进程解析**:深入解析Oracle的后台进程,如DBWn(数据库写入进程)、LGWR(日志写入进程)和SMON(系统监视器)等,理解它们的工作原理和优化方法。 9. **性能优化最佳实践**:总结了Oracle性能优化的实践...
7. 性能优化:Oracle包含许多性能监控工具,如SQL*Plus、DBA_HISTORIAN、ASH(Active Session History)等,通过这些工具可以分析性能瓶颈,进行SQL调优、索引调整等优化工作。 8. 安全性:Oracle提供用户权限管理...
3. 优化器的使用:Oracle优化器负责生成执行计划,决定如何以最有效的方式访问数据库中的数据。理解优化器的工作原理及其优化模式(如ALL_ROWS、FIRST_ROWS、FIRST_ROWS_n等)对于编写和调优SQL语句至关重要。 4. ...
《基于成本的Oracle优化法则》是数据库专家Jonathan Lewis的一部经典著作,该书深入探讨了Oracle数据库的性能优化策略,特别是在成本基础优化方面。Oracle数据库是全球广泛使用的大型企业级数据库系统,其性能优化...
Oracle数据库的经典优化中,索引原理是至关重要的一环。索引能够显著提升数据检索速度,但正确使用和理解各种索引选项才能发挥其最大效益。本文主要讨论了Oracle数据库中的一些关键索引概念和技术。 首先,基本的...
总结而言,ORACLE-SQL优化不仅要求优化者具有坚实的SQL语法知识和优化技巧,还要求深入理解ORACLE优化器和执行计划的工作原理。通过持续地学习、监控和调整,开发者可以逐步掌握SQL优化的过程,进而在实际项目中提高...
"基于成本的Oracle优化法则"是一个重要的概念,它涉及到Oracle数据库查询优化器的工作原理。在这个主题中,我们将深入探讨这一法则,理解其背后的机制,并学习如何利用这些知识来提升数据库性能。 1. **基于成本的...