`
czllfy
  • 浏览: 108116 次
  • 来自: ...
社区版块
存档分类
最新评论

Oracle中索引的使用 索引性能优化调整

阅读更多
索引是由Oracle维护的可选结构,为数据提供快速的访问。准确地判断在什么地方需要使用索引是困难的,使用索引有利于调节检索速度。 当建立一个索引时,必须指定用于跟踪的表名以及一个或多个表列。一旦建立了索引,在用户表中建立、更改和删除数据库时, Oracle就自动地维护索引。创建索引时,下列准则将帮助用户做出决定:
       1) 索引应该在SQL语句的"where"或"and"部分涉及的表列(也称谓词)被建立。假如personnel表的"firstname"表列作为查询结果显示,而不是作为谓词部分,则不论其值是什么,该表列不会被索引。
       2) 用户应该索引具有一定范围的表列,索引时有一个大致的原则:如果表中列的值占该表中行的2 0 %以内,这个表列就可以作为候选索引表列。假设一个表有36 000行且表中一个表列的值平均分布(大约每12000行),那么该表列不适合于一个索引。然而,如果同一个表中的其他表列中列值的行在1 0 0 0~1 5 0 0之间(占3 %~4 % ),则该表列可用作索引。

       3)如果在S Q L语句谓词中多个表列被一起连续引用,则应该考虑将这些表列一起放在一个索引内, O r a c l e将维护单个表列的索引(建立在单一表列上)或复合索引(建立在多个表列上)。复合索引称并置索引。

一、主关键字的约束

  关系数据库理论指出,在表中能唯一标识表的每个数据行的一个或多个表列是对象的主关键字。由于数据字典中定义的主关键字能确保表中数据行之间的唯一性,因此,在O r a c l e 8 i数据库中建立表索引关键字有助于应用调节。另外,这也减轻了开发者为了实现唯一性检查,而需要各自编程的要求。

  提示使用主关键字索引条目比不使用主关键字索引检索得快。

  假设表p e r s o n把它的i d表列作为主关键字,用下列代码设置约束:
alter table person add constraint person_pk primary key (id) using index storage (initial 1m next 1m pctincrease 0) tablespace prd_indexes ;

  处理下列S Q L语句时:select last_name ,first_name ,salary from person where id = 289 ;

  在查找一个已确定的“ i d”表列值时, O r a c l e将直接找到p e r s o n _ p k。如果其未找到正确的索引条目,O r a c l e知道该行不存在。主关键字索引具有下列两个独特之处:

 1.1因为索引是唯一的, 所以O r a c l e知道只有一个条目具有设定值。如果查找到了所期望的条目,则立即终止查找。

 1.2一旦遇到一个大于设定值的条目,索引的顺序搜索可被终止;

二、ORDER BY中用索引

  ORDER BY 子句只在两种严格的条件下使用索引.
  ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
  ORDER BY中所有的列必须定义为非空.
  WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.

例如:
  表DEPT包含以下列:
dept_code pk not null
dept_desc not null
dept_type null


非唯一性的索引(dept_type) ,
  低效: (索引不被使用)
select dept_code from dept order by dept_type
explain plan: sort order by table access full

  高效: (使用索引)
select dept_code from dept where dept_type > 0
explain plan:
table access by rowid on emp
index range scan on dept_idx

三、避免改变索引列的类型

  当比较不同数据类型的数据时, oracle自动对列进行简单的类型转换.

假设 empno是一个数值类型的索引列:
  select …from emp where empno = '123'
  实际上,经过ORACLE类型转换, 语句转化为: select … from emp where empno = to_number('123')

  幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.

  现在,假设emp_type是一个字符类型的索引列: select … from emp where emp_type = 123
  这个语句被oracle转换为: select … from emp where to_number(emp_type)=123

  因为内部发生的类型转换, 这个索引将不会被用到! 为了避免oracle对你的sql进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, oracle会优先转换数值类型到字符类型.

四、需要当心的where子句

某些select 语句中的where子句不使用索引. 这里有一些例子:

1、IS NULL 与 IS NOT NULL
  不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。
  任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

2、'!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中

不使用索引: select * from employee where salary<>3000;
使用索引: select account_name from transaction where amount >0;
使用索引: select * from employee where salary<3000 or salary>3000;

3、联接列,'||'是字符连接函数. 就象其他函数那样, 停用了索引

不使用索引: select account_name,amount from transaction where account_name||account_type='AMEXA';
使用索引: select account_name,amount from transaction where account_name = 'AMEX' and account_type=' A';

4、'+'是数学函数. 就象其他数学函数那样, 停用了索引

不使用索引: select account_name, amount from transaction where amount + 3000 >5000;
使用索引: select account_name, amount from transaction where amount > 2000 ;

5、相同的索引列不能互相比较,这将会启用全表扫描

不使用索引: select account_name, amount from transaction where account_name = nvl(:acc_name,account_name);
使用索引: select account_name, amount from transaction where account_name like nvl(:acc_name,'%');

6、带通配符(%)的like语句

不使用索引: select * from employee where last_name like '%cliton%';
使用索引: select * from employee where last_name like 'c%'

7、IN和EXISTS

不使用索引: ... where column in(select * from ... where ...);
使用索引: ... where exists (select 'X' from ...where ...);
同时应尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度),NOT EXISTS要比NOT IN查询效率更高。

如果一定要对使用函数的列启用索引:
1、oracle新的功能: 基于函数的索引(function-based index) 也许是一个较好的方案:
create index emp_i on emp (upper(ename)); /*建立基于函数的索引*/
select * from emp where upper(ename) = 'BLACKSNAIL'; /*将使用索引*/
2、MS SQL Server显示申明指定索引:
SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN ('男','女')

五、怎样监控无用的索引

  Oracle 9i以上,可以监控索引的使用情况,如果一段时间内没有使用的索引,一般就是无用的索引

  语法为:
  开始监控:alter index index_name monitoring usage;
  检查使用状态:select * from v$object_usage;
  停止监控:alter index index_name nomonitoring usage;
  当然,如果想监控整个用户下的索引,可以采用如下的脚本:

set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql

SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;' FROM dba_indexes WHERE owner = USER;

spool off
set heading on
set echo on
set feedback on
------------------------------------------------------------------------------------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql

SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;' FROM dba_indexes WHERE owner = USER;

spool off
set heading on
set echo on
set feedback on
分享到:
评论
发表评论

文章已被作者锁定,不允许评论。

相关推荐

    oracle数据库性能优化.pdf

    Oracle数据库性能优化是确保系统高效运行的关键环节,尤其是在大数据量和高并发的环境中。Oracle数据库因其先进、完整和集成的特性,在市场中占据主导地位,因此深入理解和掌握Oracle的优化技术至关重要。 首先,...

    oracle中索引的使用

    索引的使用是数据库性能优化的关键环节,尤其是在大型企业级应用中,对索引的合理设计和管理对于系统的运行速度至关重要。这篇博文将深入探讨Oracle中索引的基本概念、类型、创建与管理,以及如何通过索引来优化查询...

    Oracle数据库索引优化方法探析.pdf

    在 Oracle 生产系统中,索引优化是一项重要的工作。 Oracle 数据库管理员需要根据实际情况,选择合适的索引优化方法,提高查询效率和性能。 Oracle 数据库索引优化方法探析还包括 SQL 语句优化方法。 SQL 语句优化...

    Oracle Database 性能调整与优化 系列书.rar

    Oracle数据库是全球广泛使用的大型企业级关系型数据库管理系统,其性能调整与优化是数据库管理员(DBA)和系统管理员的核心技能。"Oracle Database 11gR2性能调整与优化"与"Oracle Database 12cR2性能调整与优化"这...

    Oracle索引优化

    Oracle索引优化是数据库性能调优的关键环节,它关乎到数据检索速度和整体系统的效率。在Oracle数据库中,索引的种类繁多,包括B*Tree索引、反向索引、降序索引、位图索引和函数索引等。理解并正确使用这些索引类型...

    Oracle性能优化绝版好书:高级OWI与ORACLE性能调整

    《Oracle性能优化绝版好书:高级OWI与ORACLE性能调整》这本书深入探讨了Oracle数据库性能优化的关键技术和策略,特别关注了Oracle Wait Interface (OWI)和性能调整的实践应用。OWI是Oracle数据库中用于监控和诊断...

    ORACLE索引详解及SQL优化

    总的来说,Oracle索引详解及SQL优化是一个深度广度兼具的主题,需要结合实际数据库结构和业务需求,灵活应用各种索引类型和优化策略,以实现数据库性能的最大化。通过深入学习和实践,你可以更好地驾驭Oracle数据库...

    ORACLE19c数据库性能优化说明.docx

    从Oracle 10g开始,RBO(基于规则的优化器)已被淘汰,现在主要使用CBO(基于成本的优化器),它会根据表的物理大小、索引状态等信息来估算成本,选择最低成本的执行计划。 索引是提升查询性能的关键手段。B-tree...

    ORACLE索引介绍与高性能SQL优化

    索引是数据库中一种重要的辅助结构,它可以帮助加快数据检索速度,优化SQL性能。在处理大量的数据时,索引的作用尤其明显。Oracle中的索引主要基于B树数据结构,并且包括位图索引、分区索引等多种类型。通过高效地...

    Oracle索引优化相关

    ### Oracle索引优化相关知识点详解 ...综上所述,正确地理解和运用Oracle索引的相关概念和技巧对于优化数据库性能至关重要。开发人员应该仔细考虑索引的设计和使用方式,避免由于不当使用而导致的性能问题。

    ORACLE SQL性能优化系列

    下面我们将详细介绍 ORACLE SQL 性能优化系列中的一些重要知识点。 一、访问表的方式 ORACLE 采用两种访问表中记录的方式:全表扫描和通过 ROWID 访问表。 1. 全表扫描 全表扫描就是顺序地访问表中每条记录。...

    oracle 性能调整 sql性能优化大全

    Oracle数据库性能调整与SQL性能优化是数据库管理员和开发人员日常工作中不可或缺的部分。Oracle数据库系统以其高效、稳定和可扩展性闻名,但同时也需要通过精心的调整和优化来确保最佳性能。以下是一些关键的知识点...

    数据库 创建索引 sql oracle

    * 用索引优化向导创建索引:索引优化向导是 SQL Server 2000 提供的新的创建索引工具,使用查询优化器分析工作负荷中的查询任务,向有大量工作负荷的数据库推荐最佳的索引混合方式,以加快数据库的查询。 索引的...

    oracle数据库系统性能和应用性能优化

    Oracle数据库系统性能和应用性能优化是数据库管理员和开发人员关注的重要领域,它涉及到多个层面的调整和优化,以确保系统的高效运行。Oracle数据库是全球广泛使用的数据库管理系统,其性能直接影响到业务应用程序的...

    Oracle 创建索引的基本规则

    - **覆盖索引**: 覆盖索引指的是包含了查询所需所有列的索引,这样可以直接从索引中获取数据而无需访问表本身,可以大大减少I/O操作。 - **索引统计信息更新**: 定期更新索引的统计信息,确保优化器能够做出更准确的...

    索引调整优化Oracle 9i工作性能的研究.pdf

    Oracle 9i 是一款广泛使用的数据库管理系统,其性能优化至关重要,其中索引调整是提升数据库性能的关键环节。本文主要探讨了Oracle 9i的索引原理和优化策略,以确保系统运行效率。 **1. 索引的重要性** 索引在...

    09 oracle的索引 PPT

    1. 空值处理:Oracle中的空值在索引中处理特殊,可能影响索引的使用。 2. 数据分布:索引性能受数据分布影响,均匀分布的数据通常比高度倾斜的数据更适合索引。 总结,Oracle的索引是提升数据库性能的关键工具,...

    Oracle 12C OCM 性能优化考试教材.zip

    3. 读写性能提升:使用闪回技术、redo log buffer大小调整、高速缓存策略优化等手段提升读写性能。 五、数据库维护与调优 1. 优化器(Optimizer):理解CBO(Cost-Based Optimizer)工作原理,学习如何使用...

    Oracle 索引 使用方法

    Oracle 索引 使用方法,索引 使用原理, 索引 使用顺序过程

    oracle索引使用样例

    ### Oracle索引使用样例详解 #### 一、索引并行创建 在Oracle数据库中,并行创建索引可以显著提高创建索引的速度,尤其是在处理大量数据时。下面的SQL语句展示了如何并行创建一个索引: ```sql CREATE INDEX IDX_GD...

Global site tag (gtag.js) - Google Analytics