`
maincoolbo
  • 浏览: 528179 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Oracle优化经典文章------索引原理篇

阅读更多
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提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对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优化经典文章.docx

    本文主要探讨了Oracle索引的一些核心概念和优化策略。 首先,理解基本的索引概念至关重要。Oracle提供了多种类型的索引,包括B树索引、位图索引、函数索引等。DBA_INDEXES视图允许我们查看所有数据库中的索引信息,...

    Oracle优化器案例与原理分析(盖国强)

    本篇分析将基于盖国强先生的著作《Oracle优化器案例与原理分析》进行深入探讨。 首先,盖国强先生是业界资深的Oracle专家,作为国内第一个Oracle ACE及ACE总监,他拥有超过十年的Oracle从业经验,并且至今仍然活跃...

    chinaunix,net-oracle精华文章-mar2007.rar

    1. **Oracle基础知识**:这可能包括数据库架构、SQL语言基础、表空间和数据文件管理、索引原理等,帮助初学者理解和掌握Oracle的基本操作。 2. **性能优化**:Oracle的性能调优是其核心竞争力之一,文章可能会探讨...

    Oracle优化全攻略一【Oracle 索引概念】.docx

    本篇文章将深入探讨Oracle中的索引类型及其优化策略。 首先,索引是提升数据库查询速度的关键工具。在Oracle数据库中,常见的索引类型包括B*Tree索引、反向索引、降序索引、位图索引、函数索引以及interMedia全文...

    20篇oracle的学习文章

    内容可能涵盖索引策略、查询优化器的工作原理、EXPLAIN PLAN的使用、表和索引的统计信息更新等。了解这些内容对于提升数据库的响应速度和系统整体性能至关重要。 "ORACLE 9i 数据库设计指引全集.doc"专注于Oracle ...

    Oracle索引分析与比较

    本篇文章将深入探讨Oracle中的B*Tree索引、反向索引、降序索引、位图索引和函数索引。 1. **B*Tree索引**: - B*Tree索引是Oracle中最常见的索引类型,其结构类似于二叉树,可以高效地处理高基数数据列,即具有...

    oracle 性能优化文章

    Oracle数据库性能优化是数据库管理员(DBA)的关键任务之一,涉及到多个层面,包括SQL查询优化、索引管理、表空间设计、内存结构调整以及数据库参数调优等。以下是对Oracle性能优化的一些核心知识点的详细说明: 1....

    oracle的索引学习

    本篇文章将深入探讨Oracle索引的学习,重点关注索引的原理、类型、优缺点以及如何使用和分析执行计划。 首先,理解索引的基本概念。索引是一种特殊的数据结构,它存储在表空间中,用于加速对表数据的访问。当执行...

    Oracle索引详解与优化技巧:B*Tree索引、位图索引及其他类型索引的应用

    内容概要:本文详细介绍了 Oracle中的各种索引类型及其使用场景,包括 B*Tree索引、位图索引、索引组织...阅读建议:阅读本文时,建议结合具体的执行计划和实际案例进行练习,以便更好地理解索引的工作原理和优化效果。

    Oracle数据库优化培训.pptx

    本篇文章主要介绍 Oracle 数据库优化的相关知识,涵盖了 SQL 语句执行过程、ORACLE 优化器、表之间的关联、如何得到 SQL 执行计划、如何分析执行计划等内容。 SQL 语句执行过程 SQL 语句处理的基本过程包括查询...

    品悟Oracle性能优化

    本篇文章将围绕Oracle性能优化展开,重点关注罗敏老师在索引介绍方面的实用技巧。 一、索引基础 索引是Oracle数据库中提升查询速度的关键工具。它的工作原理类似于书籍的目录,允许数据库快速定位到所需的数据行。...

    Oracle_Spatial_的空间查询处理机制分析及优化

    原理部分会深入探讨Oracle Spatial如何使用空间索引、空间函数和空间操作符来提升查询效率,而技术支持部分则会涉及具体的优化策略,如如何选择合适的索引类型、如何构建和维护空间索引、以及如何利用Oracle提供的...

    oracle1

    标题“Oracle1”暗示了我们将讨论的是Oracle数据库的相关知识,而描述中提供的链接指向了一篇在ITEYE博客上的文章,虽然具体内容无法直接获取,但我们可以根据Oracle数据库的一般特性和常见问题来展开讨论。...

    oracle索引

    这篇博客文章将深入探讨Oracle索引的基本概念、类型以及如何优化其使用。 首先,我们需要理解索引是如何创建和存储的。在Oracle中,最常见的索引类型是B树索引(B-Tree Index),它按照键值的顺序组织数据。当一个...

    oracle索引分析说明

    总结来说,理解Oracle索引的存储原理和`clustering_factor`的重要性有助于数据库管理员优化查询性能。通过监控和分析`clustering_factor`,可以判断索引是否有效地支持了查询模式,并据此进行必要的索引调整,例如...

    Oracle性能优化技术内幕

    本篇文章将深入探讨Oracle性能优化的各个方面,旨在帮助读者理解并掌握这个复杂而重要的主题。 一、SQL优化 SQL查询优化是性能优化的核心。Oracle通过执行计划分析SQL语句,选择最佳的执行路径。优化器会考虑索引、...

    oracle 基于成本的优化法则

    本篇文章将详细介绍Oracle基于成本的优化法则及其工作原理。 #### 二、成本优化器基础 Oracle的成本优化器是Oracle数据库自Oracle 8i版本以来引入的一种新的查询优化策略,它取代了早期版本中使用的基于规则的优化...

    基于Oracle 10g Spatial空间数据库的索引与查询.pdf

    总之,《基于Oracle 10g Spatial空间数据库的索引与查询》这篇文章深入剖析了Oracle 10g Spatial的空间索引原理和应用,为数据库管理员提供了一套优化空间查询的理论和技术框架。通过理解和应用这些知识,可以有效地...

Global site tag (gtag.js) - Google Analytics