`
lerry_ma
  • 浏览: 6085 次
  • 性别: Icon_minigender_1
  • 来自: 上海
最近访客 更多访客>>
文章分类
社区版块
存档分类
最新评论

Oracle Index 索引介绍

阅读更多
Oracle Index 索引介绍

    索引在各种关系型数据库系统中都是举足轻重的组成部分,其对于提高检索数据的速度起至关重要的作用。在Oracle中,索引基本分为以下几种:B*Tree索引,反向索引,降序索引,位图索引,函数索引,interMedia全文索引等。

一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以合理使用资源,使那些已经运行了几个小时甚至几天的进程在几分钟得以完成,这样会使您立刻成为一位英雄。这篇文章就将简单的讨论每个索引选项。主要有以下内容:

[1] 基本的索引概念

查询DBA_INDEXES视图可得到表中所有索引的列表,注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。

[2] 组合索引

当某个索引包含有多个已索引的列时,称这个索引为组合(concatented)索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前,查询只能在有限条件下使用该索引。比如:表emp有一个组合索引键,该索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中对第一列(empno)指定一个值,否则就不能使用这个索引键进行一次范围扫描。

[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子句中。索引跳跃式扫描比全索引扫描要快的多。

[10] 索引的类型

  B-树索引

  位图索引

  HASH索引

  索引编排表

  反转键索引

  基于函数的索引

  分区索引

  本地和全局索引
简要解释:
    b*tree index:几乎所有的关系型数据库中都有b*tree类型索引,也是被最多使用的。其树结构与二叉树比较类似,根据rid快速定位所访问的行。
   反向索引:反转了b*tree索引码中的字节,是索引条目分配更均匀,多用于并行服务器环境下,用于减少索引叶的竞争。
    降序索引:8i中新出现的索引类型,针对逆向排序的查询。
    位图索引:使用位图来管理与数据行的对应关系,多用于OLAP系统。
    函数索引:这种索引中保存了数据列基于function返回的值,在select * from table where function(column)=value这种类型的语句中起作用。

函数索引

基于函数的索引也是8i以来的新产物,它有索引计算列的能力,它易于使用并且提供计算好的值,在不修改应用程序的逻辑上提高了查询性能。使用基于函数的索引有几个先决条件:

1)必须拥有QUERY REWRITE(本模式下)或GLOBAL QUERY REWRITE(其他模式下)权限。

2)必须使用基于成本的优化器,基于规则的优化器将被忽略。

3)必须设置以下两个系统参数:

QUERY_REWRITE_ENABLED=TRUE

QUERY_REWRITE_INTEGRITY=TRUSTED

可以通过alter system set,alter session set在系统级或线程级设置,也可以通过在init.ora添加实现。

这里举一个基于函数的索引的例子:

SQL> create index test.ind_fun on test.testindex(upper(a));

  索引已创建。

SQL> insert into testindex values('a',2);

  已创建 1 行。

SQL> commit;

  提交完成。

SQL> select /*+ RULE*/* FROM test.testindex where upper(a)='A';

A       B

-- ----------

a       2

Execution Plan

----------------------------------------------------------

   0  SELECT STATEMENT Optimizer=HINT: RULE

   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'

  (优化器选择了全表扫描)

--------------------------------------------------------------------

SQL> select * FROM test.testindex where upper(a)='A';

A       B

-- ----------

a       2

Execution Plan

----------------------------------------------------------

   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=

   1 Bytes=5)

   2    1     INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car

      d=1)(使用了ind_fun索引)

各种索引的创建方法

    (1)*Tree索引。

    Create index indexname on tablename(columnname[columnname...])

    (2)反向索引。

    Create index indexname on tablename(columnname[columnname...]) reverse

    (3)降序索引。

    Create index indexname on tablename(columnname DESC[columnname...])

    (4)位图索引。

    Create BITMAP index indexname on tablename(columnname[columnname...])

    (5)函数索引。

    Create index indexname on tablename(functionname(columnname))

    注意:创建索引后分析要索引才能起作用。

    analyze index indexname compute statistics;

各种索引使用场合及建议

(1)B*Tree索引。

常规索引,多用于oltp系统,快速定位行,应建立于高cardinality列(即列的唯一值除以行数为一个很大的值,存在很少的相同值)。

(2)反向索引。

B*Tree的衍生产物,应用于特殊场合,在ops环境加序列增加的列上建立,不适合做区域扫描。

(3)降序索引。

B*Tree的衍生产物,应用于有降序排列的搜索语句中,索引中储存了降序排列的索引码,提供了快速的降序搜索。

(4)位图索引。

位图方式管理的索引,适用于OLAP(在线分析)和DSS(决策处理)系统,应建立于低cardinality列,适合集中读取,不适合插入和修改,提供比B*Tree索引更节省的空间。

(5)函数索引。

B*Tree的衍生产物,应用于查询语句条件列上包含函数的情况,索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。

索引什么时候不工作

首先要声明两个知识点:

(1)RBO&CBO。

Oracle有两种执行优化器,一种是RBO(Rule Based Optimizer)基于规则的优化器,这种优化器是基于sql语句写法选择执行路径的;另一种是CBO(Cost Based Optimizer)基于规则的优化器,这种优化器是Oracle根据统计分析信息来选择执行路径,如果表和索引没有进行分析,Oracle将会使用RBO代替CBO;如果表和索引很久未分析,CBO也有可能选择错误执行路径,不过CBO是Oracle发展的方向,自8i版本来已经逐渐取代RBO.

(2)AUTOTRACE。

要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径,我们能看到Oracle内部是怎么执行sql的,这是一个非常好的辅助工具,在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE:

① 由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话,请运行utlxplan.sql脚本(它在$ORACLE_HOME/rdbms/admin目录中)。

② AUTOTRACE可以通过运行plustrce.sql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置,用sys用户登陆然后运行plustrce.sql后会建立一个PLUSTRACE角色,然后给相关用户授予PLUSTRACE角色,然后这些用户就可以使用AUTOTRACE功能了。

③ AUTOTRACE的默认使用方法是set autotrace on,但是这方法不总是适合各种场合,特别当返回行数很多的时候。Set autotrace traceonly提供了只查看统计信息而不查询数据的功能。

SQL> set autotrace on

SQL> select * from test;

         A

----------

         1

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'TEST'

Statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

          0 consistent gets

          0 physical reads

          0 redo size

          0 bytes sent via SQL*Net to client

          0 bytes received via SQL*Net from client

          0 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

rows processed

SQL> set autotrace traceonly

SQL> select * from test.test;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'TEST'

Statistics

----------------------------------------------------------

          0 recursive calls

          0 db block gets

          0 consistent gets

          0 physical reads

          0 redo size

          0 bytes sent via SQL*Net to client

          0 bytes received via SQL*Net from client

          0 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

rows processed

Hints是Oracle提供的一个辅助用法,按字面理解就是‘提示’的意思,确实它起得作用也是提示优化器按它所提供的关键字来选择执行路径,特别适用于sql调整的时候。使用方法如下:

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

具体可参考Oracle SQL Reference。

有了前面这些知识点,接下来让我们来看一下什么时候索引是不起作用的。以下列出几种情况。

(1)类型不匹配时。

SQL> create table test.testindex (a varchar(2),b number);

表已创建。

SQL> create index ind_cola on test.testindex(a);

索引已创建。

SQL> insert into test.testindex values('1',1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> analyze table test.testindex compute statistics for all indexes;

表已分析。

SQL> set autotrace on;

SQL> select /*+RULE */* FROM test.testindex where a='1';(使用基于rule的优化器,数据类型匹配的情况下)

A           B

-- ----------

1           1

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: RULE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX'

   2    1     INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE)(使用了索引ind_cola)

――――――――――――――――――――――――――――――――――

SQL> select /*+RULE */* FROM test.testindex where a=1;(数据类型不匹配的情况)

A           B

-- ----------

1           1

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: RULE

   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'(优化器选择了全表扫描)

(2)条件列包含函数但没有创建函数索引。

SQL> select /*+ RULE */* FROM test.testindex where upper(a)= 'A';(使用了函数upper()在列a上);

A           B

-- ----------

a           2

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: RULE

   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'(优化器选择全表扫描)

----------------------------------------------------------

创建基于函数的索引

SQL> create index test.ind_fun on test.testindex(upper(a));

索引已创建。

SQL> insert into testindex values('a',2);

已创建1行。

SQL> commit;

提交完成。

SQL> select /*+ RULE*/* FROM test.testindex where upper(a)='A';

A           B

-- ----------

a           2

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: RULE

   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'

(在RULE优化器下忽略了函数索引选择了全表扫描)

-----------------------------------------------------------

SQL> select * FROM test.testindex where upper(a)

='A';

A           B

-- ----------

a           2

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=

          1 Bytes=5)

   2    1     INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car

          d=1)(CBO优化器使用了ind_fun索引)

(3)复合索引中的前导列没有被作为查询条件。

创建一个复合索引

SQL> create index ind_com on test.testindex(a,b);

索引已创建。

SQL> select /*+ RULE*/* from test.testindex where a='1';

A           B

-- ----------

1           2

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: RULE

   1    0   INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE)(条件列表包含前导列时使用索引ind_com)

SQL> select /*+ RULE*/* from test.testindex where b=1;

未选定行

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: RULE

   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'(条件列表不包括前导列是选择全表扫描)

-----------------------------------------------------------

(4)CBO模式下选择的行数比例过大,优化器采取了全表扫描。

SQL> select * from test.testindex where a='1';

A           B

-- ----------

1           2

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)

   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=1 Bytes=5)

(表一共2行,选择比例为50%,所以优化器选择了全表扫描)

――――――――――――――――――――――――――――――――――

下面增加表行数

SQL> declare i number;

2 begin

3 for i in 1 .. 100 loop

4 insert into test.testindex values (to_char(i),i);

5 end loop;

6 end;

7 /

PL/SQL 过程已成功完成。

SQL> commit;

提交完成。

SQL> select count(*) from test.testindex;

COUNT(*)

----------

102

SQL> select * from test.testindex where a='1';

A             B

---- ----------

1             1

1             2

Execution Plan

SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)

1    0   INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)

(表一共102行,选择比例为2/102=2%,所以优化器选择了索引扫描)

(经过分析后优化器选择了正确的路径,使用了ind_cola索引)
分享到:
评论

相关推荐

    Oracle_Index 索引

    Oracle_Index 索引

    oracle索引与分区索引介绍

    Oracle 索引与分区索引介绍 Oracle 索引与分区索引是 Oracle 数据库中两个重要的概念,它们都是为了提高查询性能和数据存储效率而设计的。在本文中,我们将详细介绍 Oracle 索引与分区索引的概念、特点、分类、创建...

    数据库 创建索引 sql oracle

    "数据库创建索引SQL Oracle" 数据库索引是数据库性能优化的重要手段之一。创建索引可以提高查询速度,降低数据库的负载,提高数据的安全性。本文将详细介绍数据库创建索引的原则、分类、创建方法、管理和优化等方面...

    oracle约束和索引笔记

    本笔记主要探讨了两个关键概念:约束和索引,这些都是Oracle数据库中的基础但至关重要的元素。 **1. 约束(Constraints)** 约束是Oracle数据库中用于确保数据完整性的规则。它们分为以下几种类型: - **非空约束...

    Oracle数据库索引的维护

    在Oracle数据库中,最常用的索引类型是B树索引(B-tree index),它支持范围查询和精确匹配查询。此外,还有其他类型的索引如位图索引、散列索引等,但B树索引因其高效性而被广泛使用。 #### 二、索引结构与存储 1...

    ORACLE重建索引总结

    Oracle数据库中的索引是提升查询性能的关键工具,但随着时间推移和数据操作,索引可能会变得效率低下,需要重建以优化其性能。本文主要总结了重建Oracle索引的相关知识点。 一、重建索引的前提条件 当表上的数据...

    Oracle_Index 索引3

    本文将基于标题“Oracle_Index 索引3”及描述“Oracle_Index 索引”,深入探讨Oracle索引的基础知识、B树索引的构造原理及其对数据库性能的影响,旨在帮助读者更全面地理解Oracle索引的运作机制。 #### Oracle索引...

    oracle中索引的使用

    创建索引有多种方式,如通过CREATE INDEX语句直接创建,或在创建表时定义为NOT NULL并指定唯一约束来自动创建唯一索引。在创建索引时,我们需要考虑索引的覆盖性,即索引是否包含查询所需的所有列,如果覆盖性高,...

    《Oracle_全文索引_详细介绍》_40页.pdf

    ### Oracle全文索引详解 #### 一、Oracle全文索引概述 Oracle全文索引是一种特殊类型的索引,它主要用于提高基于文本的查询性能。自从Oracle 7.3版本开始引入了这一特性,允许用户通过Oracle服务器提供的上下文...

    09 oracle的索引 PPT

    Oracle数据库是世界上最广泛使用的数据库系统之一,其性能优化的一个关键因素就是索引的使用。索引可以帮助快速查找和访问数据库中的数据,显著提高查询效率。本篇将详细讲解Oracle数据库中的索引及其重要性。 一、...

    从oracle用户取全部索引的方法 index sql

    oracle 用户 全部 索引 all index sql

    ORacle 全文索引

    Oracle全文索引是Oracle数据库系统中的一个重要特性,它允许用户对数据库中的文本数据进行高效的全文搜索。全文索引使得在海量文本数据中查找特定词汇或短语变得快速且简便,对于那些需要处理大量文本信息的应用程序...

    ORACLE索引详解及SQL优化

    此外,了解并运用Oracle的索引优化特性也很重要,如索引组织表(Index-Organized Table, IOT)、覆盖索引(Covering Index)以及物化视图(Materialized View)等。 总的来说,Oracle索引详解及SQL优化是一个深度...

    Oracle解决索引碎片功能.txt

    在Oracle中,通常使用`ALTER INDEX`命令来重建索引,从而消除碎片。具体命令格式如下: ```sql ALTER INDEX ind_1 REBUILD [ONLINE] [TABLESPACE name]; ``` - **ind_1**:待重建的索引名称。 - **ONLINE**:表示...

    Oracle_Index 索引2

    #### 五、位图索引介绍 位图索引是一种特殊的索引类型,主要用于OLAP系统,其特点是使用位图来表示数据行的存在状态。位图索引的大小通常远小于其他类型的索引,适合于低基数(low cardinality)的列,即数据列的值...

    Oracle在线建立超大表的索引

    本文将详细介绍如何为一个核心大表(INFO_CUSTOMER)创建一个全局B树索引,并在此过程中尽可能减小对业务的影响。 #### 表信息及使用场景 - **数据库名称**:***DB1** - **表名**:INFO_CUSTOMER - **表规模**:...

    Oracle 索引练习语句程序

    根据提供的文件信息,我们可以归纳出一系列关于Oracle数据库中索引使用的知识点。这些知识点不仅包括了基本的索引创建与查询,还涉及到了分区表及其索引的管理。 ### Oracle索引的基本概念 #### 1. 创建索引 索引...

    Oracle Index索引无效的原因与解决方法

    Oracle Index索引无效的原因可能涉及多种因素,这些因素可能导致索引无法被数据库有效利用,从而影响SQL查询的性能。在解决此类问题时,首先要确定索引是否被启用和使用。以下是一些常见的索引无效或未被利用的原因...

Global site tag (gtag.js) - Google Analytics