Oracle数据库中的索引详解

一 ROWID的概念

创新互联建站是创新、创意、研发型一体的综合型网站建设公司，自成立以来公司不断探索创新，始终坚持为客户提供满意周到的服务，在本地打下了良好的口碑，在过去的十载时间我们累计服务了上千家以及全国政企客户，如成都门窗定制等企业单位，完善的项目管理流程，严格把控项目进度与质量监控加上过硬的技术实力获得客户的一致赞扬。

存储了row在数据文件中的具 *** 置 位编码的数据 A Z a z + 和 /

row在数据块中的存储方式

SELECT ROWID last_name FROM hr employees WHERE department_id = ;

比如 OOOOOOFFFBBBBBBRRR

OOOOOO data object number 对应dba_objects data_object_id

FFF file# 对应v$datafile file#

BBBBBB block#

RRR row#

Dbms_rowid包

SELECT dbms_rowid rowid_block_number( AAAGFqAABAAAIWEAAA ) from dual;

具体到特定的物理文件

二 索引的概念

类似书的目录结构

Oracle 的 索引 对象 与表关联的可选对象 提高SQL查询语句的速度

索引直接指向包含所查询值的行的位置 减少磁盘I/O

与所索引的表是相互独立的物理结构

Oracle 自动使用并维护索引 插入 删除 更新表后 自动更新索引

语法 CREATE INDEX index ON table (column[ column] );

B tree结构(非bitmap)

[一]了解索引的工作原理

表 emp

目标 查询Frank的工资salary

建立索引 create index emp_name_idx on emp(name);

　[试验]测试索引的作用

运行/rdbms/admin/utlxplan 脚本

建立测试表

create table t as select * from dba_objects;

insert into t select * from t;

create table indextable

as select rownum id owner object_name subobject_name

object_id data_object_id object_type created

from t;

set autotrace trace explain

set timing on

分析表 可以得到cost

查询 object_name= DBA_INDEXES

在object_name列上建立索引

再查询

[思考]索引的代价

插入 更新

　三 唯一索引

何时创建 当某列任意两行的值都不相同

当建立Primary Key(主键)或者Unique constraint(唯一约束)时 唯一索引将被自动建立

语法 CREATE UNIQUE INDEX index ON table (column);

演示

四 组合索引

何时创建 当两个或多个列经常一起出现在where条件中时 则在这些列上同时创建组合索引

组合索引中列的顺序是任意的 也无需相邻 但是建议将最频繁访问的列放在列表的最前面

演示(组合列 单独列)

　五 位图索引

何时创建

列中有非常多的重复的值时候 例如某列保存了 性别 信息

Where 条件中包含了很多OR操作符

较少的update操作 因为要相应的跟新所有的bitmap

结构 位图索引使用位图作为键值 对于表中的每一数据行位图包含了TRUE( ) FALSE( ) 或NULL值

优点 位图以一种压缩格式存放 因此占用的磁盘空间比标准索引要小得多

语法 CREATE BITMAP INDEX index ON table (column[ column] );

掩饰

create table bitmaptable as select * from indextable where owner in( SYS PUBLIC );

分析 查找 建立索引 查找

　六 基于函数的索引

何时创建 在WHERE条件语句中包含函数或者表达式时

函数包括 算数表达式 PL/SQL函数 程序包函数 SQL函数 用户自定义函数

语法 CREATE INDEX index ON table (FUNCTION(column));

演示

必须要分析表 并且query_rewrite_enabled=TRUE

或者使用提示/*+ INDEX(ic_index)*/

七 反向键索引

目的 比如索引值是一个自动增长的列

多个用户对集中在少数块上的索引行进行修改 容易引起资源的争用 比如对数据块的等待 此时建立反向索引

性能问题

语法

重建为标准索引 反之不行

　八 键压缩索引

比如表landscp的数据如下

site feature job

Britten Park Rose Bed Prune

Britten Park Rose Bed Mulch

Britten Park Rose Bed Spray

Britten Park Shrub Bed Mulch

Britten Park Shrub Bed Weed

Britten Park Shrub Bed Hoe

……

查询时 以上 列均在where条件中同时出现 所以建立基于以上 列的组合索引 但是发现重复值很多 所以考虑压缩特性

Create index zip_idx

on landscp(site feature job)

press ;

将索引项分成前缀(prefix)和后缀(postfix)两部分 前两项被放置到前缀部分

Prefix : Britten Park Rose Bed

Prefix : Britten Park Shrub Bed

实际所以的结构为

Prune

Mulch

Spray

Mulch

Weed

Hoe

特点 组合索引的前缀部分具有非选择性时 考虑使用压缩 减少I/O 增加性能

九 索引组织表(IOT)

将表中的数据按照索引的结构存储在索引中 提高查询速度

牺牲插入更新的性能 换取查询性能 通常用于数据仓库 提供大量的查询 极少的插入修改工作

必须指定主键 插入数据时 会根据主键列进行B树索引排序 写入磁盘

　十 分区索引

簇:

A cluster is a group of tables that share the same data blocks because they share mon columns and are often used together

lishixinzhi/Article/program/Oracle/201311/17769

oracle 索引什么时候重建和重建方法讨论

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4

oracle 索引什么时候重建和重建方法讨论　

分类：数据库技术 字号： 大大中中小小 索引什么时候需要重建和重建的方法

一提到索引，大家都知道，但是怎样建索引，什么时候重建索引，重建索引用什么方法，可能有的就不太清楚了，我根据一些资料简单的整理一点，如果哪里不对或是不妥请大家指点，希望大家有更好经验也share出来。

索引的目的是为了加快寻找数据的速度，但是如果对表经常做改动，则索引也会相应改动，时间长了，查询速度的效率就会降低，就有可能要重建索引，那么什么时候需要重建索引和用什么方法重建索引可能是大家关心的。

一. 索引在内部进行自身的管理以确保对数据行的快速访问。但是数据表中大量的活动会导致oracle索引动态地对自身的进行重新配置，这些配置包括三个方面：

1．索引分割

当新数据行产生的索引节点要建立在现有级别上时，出现此动作。

2．索引生成

在某些位置，索引达到此级索引的最大容量的时候，就会生成更深一级的索引结构。

3．索引节点的删除

你可能了解到，删除表中的数据行后，索引中相应的节点不会从物理意义上删除，也没有从索引中删除此项目。而是从逻辑上删除此索引项目，并在索引树中留下了一个“死“节点，当索引删除了叶节点或是生成了过深的的级别层次后，就需要进行重建。

二 索引的种类：

a.B-tree（B树）索引

b.压缩B树索引

c.Bitmap（位图）索引

d.函数索引

e.Reverse Key Index（反向键索引）

f.Index Organized Table（索引组织表）

三 下面分别对各种索引进行说明

在进行介绍前先说明几个术语：

高基数：简单理解就是表中列的不同值多

低基数：建单理解就是表中的列的不同值少

以删除的叶节点数量：指得是数据行的delete操作从逻辑上删除的索引节点的数量，要记住oracle在删除数据行后，将“死“节点保留在索引中，这样做可以加快sql删除操作的速度，因此oracle删除数据行后可以不必重新平衡索引。

索引高度：索引高度是指由于数据行的插入操作而产生的索引层数，当表中添加大量数据时，oracle将生成索引的新层次以适应加入的数据行，因此， oracle索引可能有4层，但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目，而具备4层或是更多层的需要重建。

每次索引访问的读取数：是指利用索引读取一数据行时所需要的逻辑I/O操作数，逻辑读取不必是物理读取，因为索引的许多内容已经保存在数据缓冲区，然而，任何数据大于10的索引都需要重建。

1. B-tree（B树）索引

是现代关系型数据库中最常用的索引。除了存储索引数据外，还存储一个行ID，用来指出该行其余数据存储在这个被索引表中的什么地方。该索引以一种数结构格式存储这些值。

Oracle建议如果表经过排序，当返回40%一下的数据时使用索引，如果高于40%则使用全表扫描，如果没有经过排序，则当返回7%以下时，使用索引。看表是否排序，可以看dba_indexes字典中的CLUSTERING_FACTOR列，如果与表占用的数据块数相近，则经过了排序，如果与行数相近，则没有排序。那么什么时候重建呢？我们可以利用analyze index …….. compute statistics 对表进行分析。然后察看dba_indexes中的blevel。这列是说明索引从根块到叶快的级别，或是深度。如果级别大于等于4。则需要重建，如下：

Select index_name,blevel from dba_indexeswhere blevel=4.

另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时，也应当重建，如下

SQLanlyze index ------ validatestructure

SQLselect(del_lf_rows_len/lf_rows_len)*100 from index_stats where 删除并从头开始建立索引。

b. 使用alter index -------- rebuild 命令重建索引

c. 使用alter index -------- coalesce命令重建索引。

下面讨论一下这三种方法的优缺点：

1).删除并从头开始建索引：方法是最慢的，最耗时的。一般不建议。

2).Alter index ---- rebuild 快速重建索引的一种有效的办法，因为使用现有索引项来重建新索引，如果客户操作时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题，alter index ------- rebuild online.但是,由于新旧索引在建立时同时存在，因此，使用这种技巧则需要有额外的磁盘空间可临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引以到新的表空间。

Alter index ------ rebuild tablespace -----。

这个命令的执行步骤如下：

首先，逐一读取现有索引，以获取索引的关键字。

其次，按新的结构填写临时数据段。

最后，一旦操作成功，删除原有索引树，降临时数据段重命名为新的索引。

需要注意的是alterindex ---rebuild 命令中必须使用tablespace字句，以保证重建工作是在现有索引相同的表空间进行。

3).alter index ----- coalesce 使用带有coalesce参数时重建期间不需要额外空间，它只是在重建索引时将处于同一个索引分支内的叶块拼合起来，这最大限度的减少了与查询过程中相关的潜在的加锁问题，但是，coalesce选项不能用来讲一个索引转移到其他表空间。

2．压缩B树索引

当B树索引基于大表时，尤其是当基于数据仓库或决策支持系统中的大表时，这些索引会耗费大量的存储空间，压缩（compressed）B树索引用来最大限度的减少某些类型的B树索引使用的空间。当一个B树索引得到压缩时，被索引的猎的重复出现就被消除掉，进而减少了存储索引的总的存储空间。例如：

压缩前：smith每次出现还要存储它的相关的rowid.

姓 关联rowid

smith AAABSOAAEAAAABTAAB

smith AAABSOAAEAAAABTAAC

smith AAABSOAAEAAAABTAAD

压缩后:smith项和rowid指存储一次。

smith AAABSOAAEAAAABTAAB,AAABSOAAEAAAABTAAB, AAABSOAAEAAAABTAAB

创建方法：

SQLcreate index index_name ontable_name(column_name)

tablespace tablespace_name

compress;

另一种方法：

SQLalter index index_name rebuildcompress;

3． itmap(位图)索引。

B树索引在数据具有高基数的列工作的最好，对于低基数的列，位图索引可能是更有效的选择。位图索引创建表行的一个二进制映像，并把映像存储在索引块中，这种类型的索引的DML操作少，长度大并且含有极少不同的值得列特别有用。位图索引不应当用在频繁发生insert,update,delete操作的表上，这些dml操作在性能方面的代价很高，因为，他们会引起位图级的加锁发生，而且要求动态的重建所有可能值的位图。为图索引最适合数据仓库和决策支持系统。

4．基于函数的索引

当把一个函数运用于被索引的列上时，该列德索引都变得无效，基于函数的索引就是为了解决这个问题。

5．反向键索引

是一种特殊类型的B树索引，在索引基于含有序数的列时使非常有用的，如果一个传统的B树索引基于一个含有这种数据的列，往往会产生许多级，由于B树索引有 4级以上的深度会降低性能，因此反向键索引更适合这种类型，反向键索引通过简单的烦象被索引的列中的数据来解决问题，他首先反向每个列键值的字节，然后在反向后的新数据上进行索引，而新数据在值的范围上的分布通常比原来的有序数更均匀。

6．索引组织表

由于B树、位图、反向键索引的使用而引起的性能将会导致这样的事实，这些索引中的项目直接指向索引基表中对应数据的行ID,这是从表行没有按任何特定的顺序来物理地存储表中检索表行的一种有效方法，这种表叫做堆表，oracle大多数表中以一种堆叠方式存储行数据，因为行以一种或多或少的随机方式被分配给表内的块，之所以出现这种随机性，是因为oracle在决定把一个行存储在何处时并不考虑改行的内容，oracle只是把该行存储在它从该表的freelist 上所发现的第一个块中。

如果希望按一种指定顺序来存储一个表的数据，就不能使用堆表，为此oracle提供了索引组织表，索引组织表不是存储一个指向行数据的其余部分存储在了何处的行的ID指针，而是把行数据全部存储在索引本身内，这产生了两个性能好处：

n 表行按索引顺序来存储。

n 使用B树索引时引起的先读取索引后读取表锁使用的额外I/O操作得到消除。

例如：

sqlcreate table emp

(last_name varchar2(9) primary key,

first_name varchar2(9),

hire_date date)

organization index tablespace users

pctthreshold 25

including first name

overflow tablespace qyl

mapping table;

所有索引组织表在将要作为索引基础的那一列上都必须有一个主键约束，索引组织表不能含有唯一性约束或是被聚簇。

下面说明各个参数的含义：

organization index：说明该表是索引组织表

pctthreshold :指定整个数据块的什么百分比要保持打开，以便存储一个与主键值相关联的行数据，其中主键值必须在0到50之间（50是默认值）

including : 指定在行长度超过pctthershold中所设置的大小时按那一列 把行分解成两段

overflow tablespace ：指定在行长度超过pctthreshold中设置的大小时行数的的另一部分存储到的表空间。

Mapping table:致使在创建索引组织表的位图索引时所必需的一个关联映像表的创建。

以上是我根据一些资料对索引的一个简单阐述，大家可能有不同的见解，希望对大家有帮助，那些不妥的地方还希望大家提出来。

参考资料：ocp困惑racle9i性能调整

oracle statspack 高性能调整技术

[@more@]

analyze index t_id_ind validate structure

select (del_lf_rows_len/lf_rows_len)*100 from index_stats

20%

b. 使用alter index t_id_ind rebuild 命令重建索引

c. 使用alter index t_id_ind coalesce命令重建索引。

alter indext_id_ind rebuild online.

但是,由于新旧索引在建立时同时存在，因此，使用这种技巧则需要有额外的磁盘空间可临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引以到新的表空间。

Alter index ------ rebuild tablespace -----。

这个命令的执行步骤如下：

首先，逐一读取现有索引，以获取索引的关键字。

其次，按新的结构填写临时数据段。

最后，一旦操作成功，删除原有索引树，降临时数据段重命名为新的索引。

需要注意的是alter index ---rebuild 命令中必须使用tablespace字句，以保证重建工作是在现有索引相同的表空间进行

alter index ----- coalesce 使用带有coalesce参数时重建期间不需要额外空间，它只是在重建索引时将处于同一个索引分支内的叶块拼合起来，这最大限度的减少了与查询过程中相关的潜在的加锁问题，但是，coalesce选项不能用来讲一个索引转移到其他表空间

Oracle 压缩索引问题

这是不行的，你说的那个2是前缀列，只能是从头往后数，不能是中间

对于唯一索引，取值范围是1-索引关键字减数量1，缺省是索引关键字减数量1

对于非唯一索引，取值范围是1-索引关键字减数量，缺省是索引关键字减数量

oracle 索引使用键压缩的问题？

就是压缩两列的意思，你那个例子就是压缩ename,esex这两例

使用Oracle索引压缩技术,减少空间占用,并提高大数据量访问情况下的速度.

索引压缩仅用于复合索引,即多个字段建立一个索引的情况,通过compress参数指定压缩哪些字段.

虽然压缩后的索引,相对来说需要花费更多的CPU时间来处理,

但是,这样做后,可以在高速缓冲区中缓存更多的索引块,当大范围的扫描时,能够减少物理IO的数量.

网页标题：oracle如何压缩索引 oracle 索引压缩
转载源于：http://pwwzsj.com/article/hhhgop.html