各种Oracle索引类型介绍

程序员文章站 2024-01-22 22:31:04

...

各种Oracle索引类型介绍




逻辑上：



Single column 单行索引



Concatenated 多行索引



Unique 唯一索引



NonUnique 非唯一索引



Function-based函数索引



Domain 域索引



 



物理上：



Partitioned 分区索引



NonPartitioned 非分区索引



B-tree：



Normal 正常型B树



Rever Key 反转型B树 



Bitmap 位图索引



 



索引结构：



B-tree：



适合与大量的增、删、改（OLTP）；



不能用包含OR操作符的查询；



适合高基数的列（唯一值多）



典型的树状结构；



每个结点都是数据块；



大多都是物理上一层、两层或三层不定，逻辑上三层；



叶子块数据是排序的，从左向右递增；



在分支块和根块中放的是索引的范围；



Bitmap:



适合与决策支持系统；



做UPDATE代价非常高；



非常适合OR操作符的查询； 



基数比较少的时候才能建位图索引；



 



树型结构：



索引头 



开始ROWID，结束ROWID（先列出索引的最大范围）



BITMAP



每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT对应的ROWID有值




1. b-tree索引



Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。CREATE 




INDEX语句时，默认就是在创建b-tree索引。没有特别规定可用于任何情况。




2. 位图索引(bitmap index)



位图索引特定于该列只有几个枚举值的情况，比如性别字段，标示字段比如只有0和1的情况。




3. 基于函数的索引



比如经常对某个字段做查询的时候是带函数操作的，那么此时建一个函数索引就有价值了。




4. 分区索引和全局索引



这2个是用于分区表的时候。前者是分区内索引，后者是全表索引




5. 反向索引（REVERSE）



这个索引不常见，但是特定情况特别有效，比如一个varchar(5)位字段(员工编号)含值



（10001,10002,10033,10005,10016..）



这种情况默认索引分布过于密集，不能利用好服务器的并行



但是反向之后10001,20001,33001,50001,61001就有了一个很好的分布，能高效的利用好并行运算。




6.HASH索引




HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。使用HASH索引必须要使用HASH集群。

分类: sql+数据库性能优化,sql语句+数据库,区别对比

https://www.cnblogs.com/langtianya/p/6655050.html

https://blog.csdn.net/mss359681091/article/details/52061307

转

Oracle中的索引详解

2016年07月29日 09:15:46 三天不学习阅读数：521

一、 ROWID的概念

存储了row在数据文件中的具体位置：64位编码的数据，A-Z, a-z, 0-9, +, 和 /，

row在数据块中的存储方式

SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20;

比如：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;

具体到特定的物理文件

二、索引的概念

1、类似书的目录结构

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

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

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

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

6、语法：CREATE INDEX index ON table (column[, column]...);

7、 B-tree结构(非bitmap)：

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

表：emp

目标：查询Frank的工资salary

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

[试验]测试索引的作用：

1. 运行/rdbms/admin/utlxplan 脚本

2. 建立测试表

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;

3. set autotrace trace explain

4. set timing on

5. 分析表，可以得到cost

6. 查询 object_name=’DBA_INDEXES’

7. 在object_name列上建立索引

8. 再查询

[思考]索引的代价：

插入，更新

三、唯一索引

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

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

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

4、演示

四、组合索引

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

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

3、演示(组合列，单独列)

五、位图索引

1、何时创建：

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

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

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

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

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

4、语法：CREATE BITMAP INDEX index ON table (column[, column]...);

5、掩饰：

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

分析，查找，建立索引，查找

六、基于函数的索引

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

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

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

4、演示

必须要分析表，并且 query_rewrite_enabled=TRUE

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

七、反向键索引

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

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

性能问题：

语法：

重建为标准索引：反之不行

八、键压缩索引

比如表landscp的数据如下：

site feature job

Britten Park, Rose Bed 1, Prune

Britten Park, Rose Bed 1, Mulch

Britten Park, Rose Bed 1,Spray

Britten Park, Shrub Bed 1, Mulch

Britten Park, Shrub Bed 1, Weed

Britten Park, Shrub Bed 1, Hoe

……

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

Create index zip_idx

on landscp(site, feature, job)

compress 2;

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

Prefix 0: Britten Park, Rose Bed 1

Prefix 1: Britten Park, Shrub Bed 1

实际所以的结构为：

0 Prune

0 Mulch

0 Spray

1 Mulch

1 Weed

1 Hoe

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

九、索引组织表(IOT)

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

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

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

十、分区索引

簇:

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

https://blog.csdn.net/mss359681091/article/details/52061307

Oracle:索引列压缩的分析对比

2011-09-21 13:39 by Tracy., 2315 阅读, 0 评论, 收藏, 编辑

首先，我们结合index_stats得到的索引分析数据看一下，在不同索引列压缩情况下的效果。然后统一总结实验效果。

1.创建测试用表t_compress_index
create table t_compress_index as select * from all_objects;

2.不使用索引压缩技术创建索引
aaa@qq.com> create index idx_t_compress_index on t(owner,object_type,object_name);

Index created.

aaa@qq.com> analyze index idx_t_compress_index validate structure;

Index analyzed.

aaa@qq.com> select height, lf_blks, br_blks, btree_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

HEIGHT LF_BLKS BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------ ------- ------- ----------- -------------- ----------------
2 64 1 519772 2 28

3.尝试只使用第一列进行压缩
aaa@qq.com> drop index idx_t_compress_index;

Index dropped.

aaa@qq.com> create index idx_t_compress_index on t(owner,object_type,object_name) compress 1;

Index created.

aaa@qq.com> analyze index idx_t_compress_index validate structure;

Index analyzed.

aaa@qq.com> select height, lf_blks, br_blks, btree_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

HEIGHT LF_BLKS BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------ ------- ------- ----------- -------------- ----------------
2 56 1 455580 2 18

4.尝试使用前两列进行压缩
aaa@qq.com> drop index idx_t_compress_index;

Index dropped.

aaa@qq.com> create index idx_t_compress_index on t(owner,object_type,object_name) compress 2;

Index created.

aaa@qq.com> analyze index idx_t_compress_index validate structure;

Index analyzed.

aaa@qq.com> select height, lf_blks, br_blks, btree_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

HEIGHT LF_BLKS BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------ ------- ------- ----------- -------------- ----------------
2 46 1 375660 2 0

5.尝试使用前三列进行压缩
aaa@qq.com> drop index idx_t_compress_index;

Index dropped.

aaa@qq.com> create index idx_t_compress_index on t(owner,object_type,object_name) compress 3;

Index created.

aaa@qq.com> analyze index idx_t_compress_index validate structure;

Index analyzed.

aaa@qq.com> select height, lf_blks, br_blks, btree_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

HEIGHT LF_BLKS BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------ ------- ------- ----------- -------------- ----------------
2 73 1 591444 2 36

6.注意：因为索引列之后三个，所以记住不能使用compress 4进行压缩，这个是显然滴~~
aaa@qq.com> drop index idx_t_compress_index;

Index dropped.

aaa@qq.com> create index idx_t_compress_index on t(owner,object_type,object_name) compress 4;
create index idx_t_compress_index on t(owner,object_type,object_name) compress 4
*
ERROR at line 1:
ORA-25194: invalid COMPRESS prefix length value

7.索引压缩小结
（1）通过上面的这个演示过程，可以得到以下结论：
1）对前两列进行压缩效果最好
2）对全部的三列压缩反倒比不使用压缩技术耗用更多的索引空间，这与压缩机制有关
3）要在实践中反复的测试，得出最佳的压缩系数

（2）索引压缩缺点：
1.维护索引时，更耗时，因为需要更多的计算
2.查询时，搜索索引需要较长的时间，因为需要更多的计算
3.需要更多的CPU处理索引
4.增加了块竞争

（3）索引压缩好处：
1.索引占用的磁盘空间少，这是显然的
2.块缓冲区缓存能存放更多的索引条目
3.缓存命中率较高
4.物理I/O较少

任何一种技术都是一种均衡各种资源后的产物，索引压缩技术就充分的体现了这方的特点，需要在disk和CPU之间做到取舍与平衡，需要具体问题具体分析。
友情提示：如果联合索引的前几列存在大量的重复数据的时候，不妨使用一下索引压缩技术。

-- The End --

魔兽就是毒瘤，大家千万不要玩。

好文要顶关注我收藏该文

Tracy.
关注 - 25
粉丝 - 24

+加关注

« 上一篇：Oracle:在Oracle中使用锁进行并发控制
 » 下一篇：转:Oracle ASM 系列小结

分类: Oracle性能诊断

刷新评论刷新页面返回顶部

https://www.cnblogs.com/sopost/archive/2011/01/09/2190057.html

转

ORACLE 索引概述

2012年11月18日 23:01:55 zdwzzu2006 阅读数：4869

索引是数据库中一种可选的数据结构，她通常与表或簇相关。用户可以在表的一列或数列上建立索引，以提高在此表上执行 SQL 语句的性能。就像本文档的索引可以帮助读者快速定位所需信息一样，Oracle 的索引提供了更为迅速地访问表数据的方式。正确地使用索引能够显著的减少磁盘 I/O。

用户可以为一个表创建多个索引，只要不同索引使用的列或列的组合（combination of columns）不同即可。例如，下列语句中指定的列组合是有效的：

CREATE INDEX employees_idx1 ON employees (last_name, job_id);

CREATE INDEX employees_idx2 ON employees (job_id, last_name);

Oracle 提供了各种类型的索引，她们能够互为补充地提升查询性能：

平衡树索引（B-tree index）
平衡树簇索引（B-tree cluster index）
哈希簇索引（hash cluster index）
反向键索引（reverse key indexes）
位图索引（bitmap index）
位图连接索引（bitmap join index）

Oracle 还支持函数索引（function-based index），以及针对特定应用程序或程序模块（cartridge）的域索引（domain index）。

无论索引是否存在都无需对已有的 SQL 语句进行修改。索引只是提供了一种快速访问数据的路径，因此她只会影响查询的执行速度。当给出一个已经被索引的数据值后，就可以通过索引直接地定位到包含此值的所有数据行。

索引在逻辑上和物理上都与其基表（base table）是相互独立的。用户可以随时创建（create）或移除（drop）一个索引，而不会影响其基表或基表上的其他索引。当用户移除一个索引时，所有的应用程序仍然能够继续工作，但是数据访问速度有可能会降低。作为一种独立的数据结构，索引需要占用存储空间。

当索引被创建后，对其的维护与使用都是 Oracle 自动完成的。当索引所依赖的数据发生插入，更新，删除等操作时，Oracle 会自动地将这些数据变化反映到相关的索引中，无需用户的额外操作。

即便索引的基表中插入新的数据，对被索引数据的查询性能基本上能够保持稳定不变。但是，如果在一个表上建立了过多的索引，将降低其插入，更新，及删除的性能。因为 Oracle 必须同时修改与此表相关的索引信息。

优化器可以使用已有的索引来建立（build）新的索引。这将加快新索引的建立速度。

5.8.1 唯一索引和非唯一索引

索引（index）可以是唯一（unique）的或非唯一（nonunique）的。在一个表上建立唯一索引（unique index）能够保证此表的索引列（一列或多列）不存在重复值。而非唯一索引（nonunique index）并不对索引列值进行这样的限制。
Oracle 建议使用 CREATE UNIQUE INDEX 语句显式地创建唯一索引（unique index）。通过主键（primary key）或唯一约束（unique constraint）来创建唯一索引不能保证创建新的索引，而且用这些方式创建的索引不能保证为唯一索引。

5.8.2 复合索引

复合索引（composite index）（也被称为连结索引（concatenated index））是指创建在一个表的多列上的索引。复合索引内的列可以任意排列，她们在数据表中也无需相邻。

如果一个 SELECT 语句的 WHERE 子句中引用了复合索引（composite index）的全部列（all of the column）或自首列开始且连续的部分列（leading portion of the column），将有助于提高此查询的性能。因此，索引定义中列的顺序是很重要的。大体上说，经常访问的列（most commonly accessed）或选择性较大的列（most selective）应该放在前面。

一个常规的（regular）复合索引（composite index）不能超过 32 列，而位图索引（bitmap index）不能超过 30 列。索引中一个键值（key value）的总长度大致上不应超过一个数据块（data block）总可用空间的一半。

5.8.3 索引和键

索引（index）与键（key）是连个不同的概念，但是这两个术语经常被混用。索引是在数据库中实际存储的数据结构，用户可以使用 SQL 语句对其进行创建（create），修改（alter），或移除（drop）。索引提供了一种快速访问表数据的途径。而键只是一个逻辑概念。键的概念主要在 Oracle 的完整性约束（integrity constraint）功能中使用，完整性约束用于保证数据库中的业务规则（business rule）。

因为 Oracle 也会使用索引（index）来实现某些完整性约束（integrity constraint），因此索引与键（key）这两个术语经常被混用。注意不要将二者混淆。

5.8.4 索引和空值

对于一个数据表的两行或多行，如果其索引列（key column）中全部非空（non-NULL）的值完全相同（identical），那么在索引中这些行将被认为是相同的；反之，在索引中这些行将被认为是不同的。因此使用 UNIQUE 索引可以避免将包含 NULL 的行视为相同的。以上讨论并不包括索引列的列值（column value）全部为 NULL 的情况。

Oracle 不会将索引列（key column）全部为 NULL 的数据行加入到索引中。不过位图索引（bitmap index）是个例外，簇键（cluster key）的列值（column value）全部为 NULL 时也是例外。

5.8.5 函数索引

如果一个函数（function）或表达式（expression）使用了一个表的一列或多列，则用户可以依据这些函数或表达式为表建立索引，这样的索引被称为函数索引（Function-Based Index）。函数索引能够计算出函数或表达式的值，并将其保存在索引中。用户创建的函数索引既可以是平衡树类型（B-tree index）的，也可以是位图类型（bitmap index）的。

用于创建索引的函数可以是一个数学表达式（arithmetic expression），也可以是使用了 PL/SQL 函数（PL/SQL function），包函数（package function），C 外部调用（C callout），或 SQL 函数（SQL function）的表达式。用于创建索引的函数不能包含任何聚合函数（ggregate function），如果为用户自定义函数，则在声明中必须使用 DETERMINISTIC关键字。如果在一个使用对象类型（object type）的列上建立函数索引，则可以使用此对象的方法（method）作为函数，例如此对象的 map 方法。用户不能在数据类型为 LOB，REF，或嵌套表（nested table）的列上建立函数索引，也不能在包含 LOB，REF，或嵌套表等数据类型的对象类型列上建立函数索引。

5.8.5.1 使用函数索引

如果一个 SQL 语句的 WHERE 子句中使用了函数，那么建立相应的函数索引（function-based index）是提高数据访问性能的有效机制。表达式（expression）的结果经过计算后将被存储在索引中。但是当执行 INSERT 和 UPDATE 语句时，Oracle 需要进行函数运算以便维护索引。

例如，如果用户创建了以下函数索引：

CREATE INDEX idx ON table_1 (a + b * (c – 1), a, b);

当 Oracle 处理如下查询时就可以使用之前建立的索引：

SELECT a FROM table_1 WHERE a + b * (c – 1) < 100;

使用 UPPER(column_name) 或 LOWER(column_name) 函数建立函数索引（function-based index）有助于与大小写无关（case-insensitive）的查询。例如创建以下函数索引：

CREATE INDEX uppercase_idx ON employees (UPPER(first_name));

有助于提高以下查询的性能：

SELECT * FROM employees WHERE UPPER(first_name) = ‘RICHARD’;

5.8.5.2 函数索引的优化

用户必须为优化器（optimizer）收集关于函数索引（unction-based index）的统计信息（statistic）。否则处理 SQL 语句时将不会使用此索引。

当一个查询的 WHERE 子句中含有表达式（expression）时，优化器可以对函数索引（function-based index）进行索引区间扫描（index range scan）。例如以下查询：

SELECT * FROM t WHERE a + b < 10;

如果使用表达式（expression） a+b 建立的索引，优化器（optimizer）就能够进行索引区间扫描（index range scan）。如果谓词（predicate，即 WHERE 子句）产生的选择性（selectivity）较低，则对区间扫描极为有利。此外，如果表达式的结果物化在函数索引内（function-based index），优化器将能更准确地估计使用此表达式的谓词的选择性。

优化器（optimizer）能够将 SQL 语句及函数索引（function-based index）中的表达式解析为表达式树（expression tree）并进行比较，从而实现表达式匹配。这个比较过程是大小写无关的（case-insensitive），并将忽略所有空格（blank space）。

5.8.5.3 函数索引的依赖性

函数索引（function-based index）依赖于索引定义表达式中使用的函数。如果此函数为 PL/SQL 函数（PL/SQL function）或包函数（package function），当函数声明（function specification）发生变化时，索引将失效（disabled）。

用户需要被授予（grant）CREATE INDEX 或 CREATE ANY INDEX 权限才能创建函数索引（function-based index）。

要想使用函数索引（function-based index）：

建立索引后，表必须经过分析（analyze）。
必须保证查询的条件表达式不是 NULL 值, 因为 NULL 值不会被存储到索引中。

以下各节将讲述使用函数索引的其他需求。

5.8.5.3.1 DETERMINISTIC 函数

函数索引（function-based index）使用的用户自定义函数（user-written function）必须声明为 DETERMINISTIC，此关键字表明对于一定的输入参数，此函数总会得到相同的输出结果。

5.8.5.3.2 定义函数的权限

函数索引（function-based index）的所有者（owner）必须具备此索引定义中使用的函数的EXECUTE 权限。当 EXECUTE 权限被收回（revoke）后，Oracle 则将索引标识为 DISABLED。索引的所有者无须具备此函数的 EXECUTE WITH GRANT OPTION 权限，即可将索引所在表的SELECT 权限授予（grant）其他用户。

5.8.5.3.3 解决函数索引的依赖性问题

函数索引（function-based index）依赖于她使用的所有函数。如果函数或函数所在包的声明（specification）被修改过（或索引所有者对函数的 EXECUTE 权限被收回)，将会出现以下情况：

索引被标记为 DISABLED。
如果优化器（optimizer）选择了在标记为 DISABLED 的索引上执行查询，那么此查询将失败
使用标记为 DISABLED 的索引而执行的 DML 操作将失败，除非此索引同时被标记为UNUSABLE 且初始化参数（initialization parameter） SKIP_UNUSABLE_INDEXES 被设为TRUE。

函数被修改之后，用户可以使用 ALTER INDEX … ENABLE 语句将索引重新置为 ENABLE 状态。

5.8.6 索引是如何存储的

当用户创建索引时，Oracle 会自动地在表空间（tablespace）中创建索引段（index segment）来存储索引的数据。用户可以通过以下方式控制索引段的空间分配和使用：

设置索引段的存储参数（storage parameter）来控制如何为此索引段分配数据扩展（extent）
为索引段设置 PCTFREE 参数，来控制组成数据扩展的各个数据块（data block）的可用空间情况。

索引段（index segment）使用的表空间（tablespace）既可以是索引所有者（owner）的默认表空间，也可以是在 CREATE INDEX 语句中指定的表空间。索引无需和其相关的表位于同一表空间中。相反，如果将索引与其相关表存储在不同磁盘上能够提升使用此索引的查询性能，因为此时 Oracle 能够并行地（parallel）访问索引及表数据。

5.8.6.1 索引块的格式

一个数据块（data block）内可用于存储索引数据的空间等于数据块容量减去数据块管理开销（overhead），索引条目管理开销（entry overhead），rowid，及记录每个索引值长度的 1 字节（byte）。

当用户创建索引时，Oracle 取得所有被索引列的数据并进行排序，之后将排序后索引值和与此值相对应的 rowid 按照从下到上的顺序加载到索引中。例如，以下语句：

CREATE INDEX employees_last_name ON employees(last_name);

Oracle 先将 employees 表按 last_name 列排序，再将排序后的列及相应的 rowid 按从下到上的顺序加载到索引中。使用此索引时，Oracle 可以快速地搜索已排序的 last_name 值，并使用相应的 rowid 去定位包含用户所查找的 last_name 值的数据行。

5.8.6.2 索引的内部结构

Oracle 使用平衡树（B-tree）存储索引以便提升数据访问速度。当不使用索引时，用户必须对数据进行顺序扫描（sequential scan）来查找指定的值。如果有 n 行数据，那么平均需要扫描的行为 n/2。因此当数据量增长时，这种方法的开销将显著增长。

如果将一个已排序的值列（list of the values）划分为多个区间（range），每个区间的末尾包含指向下个区间的指针（pointer），而搜索树（search tree）中则保存指向每个区间的指针。此时在 n 行数据中查询一个值所需的时间为 log(n)。这就是 Oracle 索引的基本原理。

在一个平衡树索引（B-tree index）中，最底层的索引块（叶块（leaf block））存储了被索引的数据值，以及对应的 rowid。叶块之间以双向链表的形式相互连接。位于叶块之上的索引块被称为分支块（branch block），分枝块中包含了指向下层索引块的指针。如果被索引的列存储的是字符数据（character data），那么索引值为这些字符数据在当前数据库字符集（database character set）中的二进制值（binary value）。

对于唯一索引（unique index），每个索引值对应着唯一的一个 rowid。对于非唯一索引（nonunique index），每个索引值对应着多个已排序的 rowid。因此在非唯一索引中，索引数据是按照索引键（index key）及 rowid 共同排序的。键值（key value）全部为 NULL 的行不会被索引，只有簇索引（cluster index）例外。在数据表中，如果两个数据行的全部键值都为 NULL，也不会与唯一索引相冲突。

5.8.6.3 索引的属性

有两种类型的索引块：

用于搜索的分支块（branch block）
用于存储索引数据的叶块（leaf block）

5.8.6.3.1 分支块

分支块（branch block）中存储以下信息：

最小的键值前缀（minimum key prefix），用于在（本块的）两个键值之间做出分支选择
指向包含所查找键值的子块（child block）的指针（）

包含 n 个键值的分支块（branch block）含有 n+1 个指针。键值及指针的数量同时还受索引块（index block）容量的限制。

5.8.6.3.2 叶块

所有叶块（leaf block）相对于其根分支块（root branch block）的深度（depth）是相同的。叶块用于存储以下信息：

数据行的键值（key value）
键值对应数据行的 ROWID

所有的键值-ROWID 对（key and ROWID pair）都与其左右的兄弟节点（sibling）向链接（link），并按照（key，ROWID）的顺序排序。

5.8.6.4 平衡树结构的优势

平衡树数据结构（B-tree structure）具有以下优势：

平衡树（B-tree）内所有叶块（leaf block）的深度相同，因此获取索引内任何位置的数据所需的时间大致相同。
平衡树索引（B-tree index）能够自动保持平。
平衡树内的所有块容量平均在总容量的 3/4 左右。
在大区间（wide range）范围内进行查询时，无论匹配个别值（exact match）还是搜索一个区间（range search），平衡树都能提供较好的查询性能。
数据插入（insert），更新（update），及删除（delete）的效率较高，且易于维护键值的顺序（key order）
大型表，小型表利用平衡树进行搜索的效率都较好，且搜索效率不会因数据增长而降低。

5.8.7 索引唯一扫描

索引唯一扫描（index unique scan）是效率最高的数据访问方式之一。从平衡树索引（B-tree index）中获取数据时将采用此种方式。当一个唯一索引（采用平衡树结构）的全部列都包含在查询条件中，且查询体条件表达式均为等号（equality）时，优化器将选择使用索引唯一扫描。

5.8.8 索引区间扫描

当访问选择性较大的数据（selective data）时 Oracle 常进行索引区间扫描（index range scan）。扫描区间可以是封闭的（bounded）（两端均封闭），也可以是不封闭的（unbounded）（一端或两端均不封闭）。扫描所返回的数据按照索引列的升序进行排列，对于索引值相同的行将按 ROWID 的升序排列。

5.8.9 键压缩

用户利用键压缩（key compression）可以将索引或索引表（index-organized table）中键值（column value）的部分内容进行压缩，以便减少重复值带来的存储开销。

一般来说，索引的一个键（key）通常由两个片段（piece）构成：分组片段（grouping piece）及唯一片段（unique piece）。如果定义索引的键中不存在唯一片段，Oracle 会以 ROWID 的形式在此键的分组片段后添加一个唯一片段。键压缩（key compression）就是将键的分组片段从键中拆分出来单独存储，供多个唯一片段使用。

5.8.9.1 索引键的前缀和后缀

键压缩（key compression）将一个索引键拆分为前缀（prefix entry）（即分组片段（grouping piece））和后缀（suffix entry）（即唯一片段（unique piece））。压缩是通过一个索引块（index block）中的多个后缀共享一个前缀来实现的。在平衡树索引（B-tree index）中只有位于叶块（leaf block）的键会被压缩。在分支块（branch block）内不必存储键的后缀，因此其中的键也无需压缩。

键压缩（key compression）只能在每个索引块（index block）内分别实现，而不能跨多个索引块。压缩后每个索引行（index row）只保存后缀（suffix entry），而每个后缀将引用一个共享的前缀（prefix entry），后缀与其共享的前缀必须位于同一索引块内。

默认情况下，前缀（prefix entry）由除去最后一列之外的其他键列（key column）构成。例如，一个索引键（index key）由（column1，column2，column3）3 列构成，则默认的前缀为（column1, column2）。如一组索引值为（1，2，3），（1，2，4），（1，2，7），（1，3，5），（1，3，4），（1，4，4），则其中重复出现的前缀（1，2），（1，3）将被压缩。

用户也可以手工设定前缀长度（prefix length），即前缀所包含的列数。例如，如果用户设定前缀长度为 1，则在上述例子中，column1 为前缀，（column2，column3）为后缀，其中重复出现的前缀 1 将被压缩。

非唯一索引（nonunique index）的最大前缀长度（prefix length）为键列的个数，而唯一索引（unique index）的最大前缀长度为键列的个数减 1。

应用键压缩（key compression）后，生成索引时，如果一个键值（key value）的前缀（prefix entry）在索引块（index block）中不存在，此前缀才会被写入索引块中。一个前缀被写入后立即就可以被此索引块内的后缀（suffix entry）共享，直到所有引用此前缀的后缀都被删除为止。

5.8.9.2 性能上及存储上的考虑

键压缩（key compression）能够节约大量存储空间，因此用户可以在一个索引块（index block）内存储更多的索引键（index key），从而减少 I/O，提高性能。

键压缩（key compression）能够减少索引所需的存储空间，但索引扫描时需要重构（reconstruct）键值（key value），因此增加了 CPU 的负担。此外键压缩也会带来一些存储开销，每个前缀（prefix entry）需要 4 字节（byte）的管理开销。

5.8.9.3 使用键压缩

键压缩（key compression）在多种情况下都能够发挥作用，例如：

对于非唯一索引（nonunique index），Oracle 会在每个重复的索引键（index key）之后添加 rowid 以便区分。如果使用了键压缩，在一个索引块（index block）内，Oracle 只需将重复的索引键作为前缀（（prefix entry））存储一次，并用各行的 rowid 作为后缀（suffix entry）。
唯一索引（nonunique index）中也存在相同的情况。例如唯一索引（stock_ticker，transaction_time）的含义是（项目，时间戳），通常数千条记录中stock_ticker 的值是相同的，但她们对应的 transaction_time 值各不相同。使用了键压缩后，一个索引块中每个 stock_ticker 值作为前缀只需存储一次，而各个transaction_time 值则作为后缀存储，并引用一个共享的 stock_ticker 前缀。
在一个包含 VARRAY 或 NESTED TABLE 数据类型（datatype）的索引表（index-organized table）中，这些collection 类型中各个元素（element）的对象标识符（object identifier）是重复的。用户可以使用键压缩以避免重复存储这些对象标识符。

有些情况无法使用键压缩（key compression）。例如，一个只有一个索引键（index key）的唯一索引（unique index）就无法使用键压缩，因为索引键中不存在可供共享的分组片段（grouping piece）。

5.8.10 逆序键索引

用户可以创建逆序键索引（reverse key index），此处的逆序指索引列值（index key value）得各个字节（byte）按倒序排列，而非索引列（index key）逆序排列。在 RAC 环境中，使用这样的排列方式可以避免由于对索引的修改集中在一小部分叶块（leaf block）上而造成的性能下降。通过使索引的键值逆序排列，可以使插入操作分布在索引的全部叶块中。

使用逆序键索引（reverse key index）后将无法对此索引进行索引区间扫描（index range scanning），因为在逆序键索引中，词汇上（lexically）相邻的索引键（index key）在存储上未必相邻。因此在逆序键索引上只能进行确定键扫描（fetch-by-key scan）或全索引扫描（full-index scan）。

有些情况下，使用逆序键索引（reverse key index）可以令 RAC 环境下的 OLTP 应用效率更高。例如，为一个 e-mail 应用中的所有邮件进行索引：由于用户可能保存旧的邮件，因此索引必须做到既能快速访问最新邮件，也能快速访问旧邮件。

用户使用 REVERSE 就可以轻易地创建逆序键索引（reverse key index）。在 CREATE INDEX语句中使用 REVERSE 关键字作为创建索引的选项：

CREATE INDEX i ON t (a,b,c) REVERSE;

用户也可以在 REBUILD 子句后添加 NOREVERSE 关键字将一个逆序键索引（reverse key index）转换为常规的索引：

ALTER INDEX i REBUILD NOREVERSE;

如果 REBUILD 子句后没有使用 NOREVERSE 关键字，那么逆序键索引（reverse key index）被重建后仍将保持逆序。

5.8.11 位图索引

索引的目标是为用户提供指向包含特定键值（key value）的数据行的指针。在常规的索引中，Oracle 将各行的键值及与此键值对应的一组 ROWID 存储在一起，从而实现了上述目标。而在位图索引（bitmap index）中，只需存储每个键值的位图（bitmap），而非一组 ROWID。

位图（bitmap）中的每一位（bit）对应一个可能的 ROWID。如果某一位被置位（set），则表明着与此位对应的 ROWID 所指向的行中包含此位所代表的键值（key value）。Oracle 通过一个映射函数（mapping function）将位信息转化为实际的 ROWID，因此虽然位图索引（bitmap index）内部的存储结构与常规索引不同，但她同样能实现常规索引的功能。当不同值的索引键的数量较少时，位图索引的存储效率相当高。

如果在 WHERE 子句内引用的多个列上都建有位图索引（bitmap index），那么进行位图索引扫描时（bitmap indexing）可以将各个位图索引融合在一起。不满足全部条件的行可以被预先过滤掉。因此使用位图索引能够极大地提高查询的响应时间。

5.8.11.1 数据仓库应用中位图索引的优势

数据仓库应用（data warehousing application）的特点是数据量巨大，执行的多为自定义查询（ad hoc query），且并发事务较少。这种环境下使用位图索引（bitmap index）具备如下优势：

能够减少大数据量自定义查询的响应时间
与其他索引技术相比能够节省大量存储空间
即使硬件配置较低也能显著提高性能
有利于并行 DML 和并行加载

为一个大表建立传统的平衡树索引（B-tree index）可能占用极大的存储空间，索引有可能比数据表还要大数倍。而一个位图索引（bitmap index）所占的空间比被索引数据还要小得多。

位图索引（bitmap index）不适用于 OLTP 系统，因为这样的系统中存在大量对数据进行修改的并发事务。位图索引主要用于数据仓库系统中（data warehousing）的决策支持功能，在这种环境下用户对数据的操作主要是查询而非修改。

主要进行大于（greater than）或小于（less than）比较的列，不适宜使用位图索引（bitmap index）。例如，WHERE 子句中常会将 salary 列和一个值进行比较，此时更适合使用平衡树索引（B-tree index）。位图索引适用于等值查询，尤其是存在 AND，OR，和 NOT 等逻辑操作符的组合时。

位图索引（bitmap index）是集成在 Oracle 的优化器（optimizer）和执行引擎（execution engine）之中的。位图索引也能够和 Oracle 中的其他执行方法（execution method）无缝地组合。例如，优化器可以在利用一个表的位图索引和另一个表的平衡树索引（B-tree index）对这两张表进行哈希连接（hash join）。优化器能够在位图索引及其他可用的访问方法（例如常规的平衡树索引，或全表扫描（full table scan））中选择效率最高的方式，同时考虑是否适合使用并行执行。

位图索引（bitmap index）如同常规索引一样，可以结合并行查询（parallel query）和并行 DML（parallel DML）一起工作。建立于分区表（partitioned table）的位图索引必须为本地索引（local index）。Oracle 还支持并行地创建位图索引，以及创建复合位图索引。

5.8.11.2 基数

在基数（cardinality）小的列上建立位图索引（bitmap index）效果最好。所谓某列的基数小（low cardinality）是指此列中所有不相同的值的个数要小于总行数。如果某列中所有不相同的值的个数占总行数的比例小于 1%，或某列中值的重复数量在 100 个以上，那么就可以考虑在此列上建立位图索引。即便某列的基数较上述标准稍大，或值的重复数量较上述标准稍小，如果在一个查询的 WHERE 子句中需要引用此列定义复杂的条件，也可以考虑在此列上建立位图索引。

例如，一个表包含一百万行数据，其中的一列包含一万个不相同的值，就可以考虑在此列上创建位图索引（bitmap index）。此列上位图索引的查询性能将超过平衡树索引（B-tree index），当此列与其他列作为组合条件时效果尤为明显。

平衡树索引（B-tree index）适用于高基数的数据，即数据的可能值很多，例如CUSTOMER_NAME 或 PHONE_NUMBER 列。在有些情况下，平衡树索引所需的存储空间可能比被索引数据还要大。如果使用得当，位图索引将远远小于同等情况下的平衡树索引。

对于自定义查询（ad hoc query）或相似的应用，使用位图索引（bitmap index）能够显著地提高查询性能。查询的 WHERE 子句中的 AND 和 OR 条件直接对位图（bitmap）进行布尔运算（Boolean operation）得到一个位图结果集（resulting bitmap），而无需将所有的位图转换为 ROWID。如果布尔操作后的结果集较小，那么查询就能够迅速得到结果，而无需进行全表扫描（full table scan）。

5.8.11.3 位图索引和空值

与其他大多数索引不同，位图索引（bitmap index）可以包含键值（key value）为 NULL 的行。将键值为空的行进行索引对有些 SQL 语句是有用处的，例如包含 COUNT 聚合函数的查询。

5.8.11.4 分区表上的位图索引

用户可以在分区表（partitioned table）上创建位图索引（bitmap index）。唯一的限制是位图索引对分区表来说必须是本地的（local），而不能是全局索引（global index）。只有非分区表才能使用全局位图索引。

5.8.12 位图连接索引

除了建立在单个表之上的位图索引（bitmap index），用户还可以创建位图连接索引（bitmap join index），此种索引是为了连接（join）两个或多个数据表而建的。位图连接索引（bitmap join index）可以预先将有连接关系的数据进行保存，且所需的存储空间较小。对于一个表的某列的每个值，位图连接索引为其保存其他表中与此值有连接关系的数据行的 rowid。在数据仓库环境中，连接关系通常是维表（dimension table）中的主键（primary key）与事实表（fact table）中的外键（foreign key）进行等值内连接（equi-inner join）。

物化连接视图（materialized join view）也是一种预先将连接物化的方法，但与之相比位图连接索引（bitmap join index）所需的存储空间更少。因为物化连接视图不会压缩事实表（fact table）中的 rowid。

https://blog.csdn.net/zdwzzu2006/article/details/8197590

原

Oracle-index索引解读

2016年10月27日 21:25:05 小小工匠阅读数：6157 标签： oracle 索引数据库更多

个人分类：【数据库-Oracle基础】

所属专栏： Oralce手札

概述

Oracle-procedure/cursor解读

各种Oracle索引类型介绍

索引是数据库对象之一，用于加快数据的检索
索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式,索引直接指向包含所查询值的行的位置，减少磁盘I/O,，从而提高检索效率
索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表,与所索引的表是相互独立的物理结构
索引一旦建立，Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引,不会对表产生影响.
索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
oracle创建主键时会自动在该列上创建索引

为什么需要索引

数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似，即它们都包含一个数据段和一个指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理上可以相隔很远）。

鉴于很多记录只能做到按一个字段排序，所以要查询某个未经排序的字段，就需要使用线性查找，即要访问N/2个数据块，其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段（也就是说，不包含唯一值），那么就要搜索整个表空间，即要访问全部N个数据块。

然而，对于经过排序的字段，可以使用二分查找，因此只要访问log2 N个数据块。同样，对于已经排过序的非键字段，只要找到更大的值，也就不用再搜索表中的其他数据块了。这样一来，性能就会有实质性的提升。

什么是索引

索引是对记录按照多个字段进行排序的一种方式。对表中的某个字段建立索引会创建另一种数据结构，其中保存着字段的值，每个值又指向与它相关的记录。这种索引的数据结构是经过排序的，因而可以对其执行二分查找。

索引的缺点是占用额外的磁盘空间。所以如果为同一个表中的很多字段都建立索引，那这个文件可能会很快膨胀到文件系统规定的上限。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引的原理

首先，来看一个示例数据库表的模式：

各种Oracle索引类型介绍

注意：这里用char而不用varchar是为了精确地描述数据占用磁盘的大小。

这个示例数据库中包含500万行记录，而且没有建立索引。

接下来我们就分析针对这个表的两个查询：一个查询使用id（经过排序的键字段），另一个查询使用firstName（未经排序的非键字段）。

示例分析一

对于这个拥有r = 5 000 000条记录的示例数据库，在磁盘上要为每条记录分配 R = （4+50+50+100）204字节的固定存储空间（4个字段所占空间的总和）。

默认的数据库块大小为 B = 1024字节。于是，我们可计算出这个表的分块因数为 bfr = (B/R) = 1024/204 = 5，即磁盘上每个数据块保存5条记录。那么，保存整个表所需的数据块数就是 N = (r/bfr) = 5000000/5 = 1 000 000。

使用线性查找搜索id字段——这个字段是键字段（每个字段的值唯一），需要访问 N/2 ＝ 500 000个数据块才能找到目标值。不过，因为这个字段是经过排序的，所以可以使用二分查找法，而这样平均只需要访问log2(1000000 )= 19.93 = 20 个块。显然，这会给性能带来极大的提升。

再来看看firstName字段，这个字段是未经排序的，因此不可能使用二分查找，况且这个字段的值也不是唯一的，所以要从表的开头查找末尾，即要访问 N = 1 000 000个数据块。这种情况通过建立索引就能得到改善。

如果一条索引记录只包含索引字段和一个指向原始记录的指针，那么这条记录肯定要比它所指向的包含更多字段的记录更小。也就是说，索引本身占用的磁盘空间比原来的表更少，因此需要遍历的数据块数也比搜索原来的表更少。

以下是firstName字段索引的模式：

各种Oracle索引类型介绍

示例分析二

对于这个拥有r = 5 000 000条记录的示例数据库，每条索引记录要占用 R = 54字节磁盘空间，而且同样使用默认的数据块大小 B = 1024字节。那么索引的分块因数就是 bfr = (B/R) = 1024/54 = 18。最终这个表的索引需要占用 N = (r/bfr) = 5000000/18 = 277 778个数据块。

现在，再搜索firstName字段就可以使用索引来提高性能了。对索引使用二分查找，需要访问 log2 277778 = 18.09 = 19个数据块。再加上为找到实际记录的地址还要访问一个数据块，总共要访问 19 + 1 = 20个数据块，这与搜索未索引的表需要访问277 778个数据块相比，不啻于天壤之别。

什么时候用索引

创建索引要额外占用磁盘空间（比如，上面例子中要额外占用277 778个数据块），建立的索引太多可能导致磁盘空间不足。因此，在建立索引时，一定要慎重选择正确的字段。

由于索引只能提高搜索记录中某个匹配字段的速度，因此在执行插入和删除操作的情况下，仅为输出结果而为字段建立索引，就纯粹是浪费磁盘空间和处理时间了；这种情况下不用建立索引。

另外，由于二分查找的原因，数据的基数性（cardinality）或唯一性也非常重要。对基数性为2的字段建立索引，会将数据一分为二，而对基数性为1000的字段，则同样会返回大约1000条记录。在这么低的基数性下，索引的效率将减低至线性查找的水平，而查询优化器会在基数性小于记录数的30%时放弃索引，实际上等于索引纯粹只会浪费空间。

另外需要说明：创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

索引的语法

创建索引




<span style="color:#000000"><code>CREATE UNIUQE | BITMAP INDEX <span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>schema</strong></span>></span>.<span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>index_name</strong></span>></span>




      ON <span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>schema</strong></span>></span>.<span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>table_name</strong></span>></span>




           (<span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>column_name</strong></span>></span> | <span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>expression</strong></span>></span> ASC | DESC,



            <span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>column_name</strong></span>></span> | <span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>expression</strong></span>></span> ASC | DESC,...)



     TABLESPACE <span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>tablespace_name</strong></span>></span>




     STORAGE <span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>storage_settings</strong></span>></span>




     LOGGING | NOLOGGING



     COMPUTE STATISTICS



     NOCOMPRESS | COMPRESS<span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>nn</strong></span>></span>




     NOSORT | REVERSE



     PARTITION | GLOBAL PARTITION<span style="color:#006666 !important"><<span style="color:#4f4f4f !important"><strong>partition_setting</strong></span>></span></code></span>

参数说明：

1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4）STORAGE：可进一步设置表空间的存储参数
5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
6）COMPUTE STATISTICS：创建新索引时收集统计信息
7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

修改索引

重命名索引

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>alter</strong></span> index index_sno rename <span style="color:#000088 !important"><strong>to</strong></span> bitmap_index;</code></span>

合并索引

表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>alter</strong></span> index index_sno coalesce;</code></span>

重建索引

方式一：删除原来的索引，重新建立索引

方式二：使用rebuild方式

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>alter</strong></span> index index_sno rebuild [online];</code></span>

rebuild 和 rebuild online的区别

alter index rebuild online:实质上是扫描表而不是扫描现有的索引块来实现索引的重建

alter index rebuild:只扫描现有的索引块来实现索引的重建。

rebuild index online在执行期间不会阻塞DML操作，但在开始和结束阶段，需要请求模式为4的TM锁。因此，如果在rebuild index online开始前或结束时，有其它长时间的事物在运行，很有可能就造成大量的锁等待。也就是说在执行前仍会产生阻塞, 应该避免排他锁.

而rebuild index在执行期间会阻塞DML操作, 但速度较快.

两者重建索引时的扫描方式不同，
rebuild用的是“INDEX FAST FULL SCAN”，
rebuild online用的是“TABLE ACCESS FULL”;
即rebuild index是扫描索引块,而rebuild index online是扫描全表的数据块.

删除索引

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>drop</strong></span> index index_sno;</code></span>

查看索引




<span style="color:#000000"><code>查询all_indexes





<span style="color:#000088 !important"><strong>select</strong></span> index_name, index_type, tablespace_name, uniqueness



  <span style="color:#000088 !important"><strong>from</strong></span> all_indexes



 <span style="color:#000088 !important"><strong>where</strong></span> table_name = <span style="color:#009900 !important">'tablename'</span>;



 



或者查询user_indexes 




<span style="color:#000088 !important"><strong>select</strong></span> a.* <span style="color:#000088 !important"><strong>from</strong></span> user_indexes a ;</code></span>

B树索引的index_type为 NORMAL；而位图索引的index_type类型值为BITMAP

索引分类

B-树索引（默认类型）
位图索引
HASH索引
索引编排表
反转键索引
基于函数的索引
分区索引
本地和全局索引

B树索引

说明

B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。

B树索引最多可以包括32列。

特点

1.oracle中最常用的索引；B树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值

2.所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同

3.能够适应精确查询、模糊查询和比较查询

创建

B树索引的单一索引

单一索引:

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>Create</strong></span> <span style="color:#000088 !important"><strong>Index</strong></span> <<span style="color:#000088 !important"><strong>Index</strong></span>-Name> <span style="color:#000088 !important"><strong>On</strong></span> <Table_Name>(Column_Name)</code></span>

B树索引的复合索引

复合索引:

<span style="color:#000000"><code> <span style="color:#000088 !important"><strong>Create</strong></span> <span style="color:#000088 !important"><strong>Index</strong></span> i_deptno_job <span style="color:#000088 !important"><strong>on</strong></span> emp(deptno,job); —>在emp表的deptno、job列建立索引。</code></span>




<span style="color:#000000"><code>　<span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> emp <span style="color:#000088 !important"><strong>where</strong></span> deptno=<span style="color:#006666 !important">66</span> <span style="color:#000088 !important"><strong>and</strong></span> job=<span style="color:#009900 !important">'sals'</span> ->走索引。




 



　<span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> emp <span style="color:#000088 !important"><strong>where</strong></span> deptno=<span style="color:#006666 !important">66</span> <span style="color:#000088 !important"><strong>OR</strong></span> job=<span style="color:#009900 !important">'sals'</span> ->将进行全表扫描。不走索引



 



　<span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> emp <span style="color:#000088 !important"><strong>where</strong></span> deptno=<span style="color:#006666 !important">66</span> ->走索引。



 



　<span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> emp <span style="color:#000088 !important"><strong>where</strong></span> job=<span style="color:#009900 !important">'sals'</span> ->进行全表扫描、不走索引。




</code></span>

　　如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引，将会进行全表扫描。
　　
　即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列

适合使用场景

适合与大量的增、删、改（OLTP）
不能用包含OR操作符的查询；
列基数（列不重复值的个数）大时适合使用B数索引

位图索引

说明

创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的ROWID的转换.

位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。

它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。
尽管位图索引最多可达30个列，但通常它们都只用于少量的列。

比如：某个表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。

特点

适合与决策支持系统；
做UPDATE代价非常高；
非常适合OR操作符的查询；
基数比较少的时候才能建位图索引；

创建

例子：

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>Create</strong></span> bitmap <span style="color:#000088 !important"><strong>Index</strong></span> <<span style="color:#000088 !important"><strong>Index</strong></span>-Name> <span style="color:#000088 !important"><strong>On</strong></span> <Table_Name>(Column_Name)</code></span>

适合使用场景

对于基数小的列适合建立位图索引（例如性别等）

注意事项

建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。位图索引的索引值中包含ROWID，这样Oracle就可以在行级别上锁定索引。

<p style="margin-left:0px;"><span style="color:#4f4f4f;"><span style="color:#FF0000;">位图索引存储为压缩的索引值，其中包含了一定范围的ROWID，因此Oracle必须针对一个给定值锁定所有范围内的ROWID。这种锁定类型可能在某些DML语句中造成死锁。</span></span></p>

<p style="margin-left:0px;"><span style="color:#4f4f4f;">SELECT语句不会受到这种锁定问题的影响。</span></p>
</li>
<li>
<p style="margin-left:0px;"><span style="color:#4f4f4f;">基于规则的优化器不会考虑位图索引。</span></p>
</li>
<li>当执行ALTER TABLE语句并修改包含有位图索引的列时，会使位图索引失效。</li>
<li>位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。</li>
<li>位图索引不能被声明为唯一索引。</li>
<li>位图索引的最大长度为30。</li>

函数索引

说明

当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度
函数索引既可以使用B树索引，也可以使用位图索引；当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引
函数索引中可以使用len、trim、substr、upper（每行返回独立结果），不能使用如sum、max、min、avg等
基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE和DELETE语句的执行就会花费越多的时间。

创建

例子：




<span style="color:#000000"><code><span style="color:#000088 !important"><strong>CREATE</strong></span> <span style="color:#000088 !important"><strong>INDEX</strong></span> <span style="color:#000088 !important"><strong>index</strong></span> <span style="color:#000088 !important"><strong>ON</strong></span> table (<span style="color:#ff79c6"><strong>FUNCTION</strong></span><span style="color:#4f4f4f !important">(column)</span>);




 



举例：




<span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> student <span style="color:#000088 !important"><strong>where</strong></span> upper(name) =<span style="color:#009900 !important">'XGJ'</span>;</code></span>

分区索引

分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。

B树和位图索引都可以被分区，而HASH索引不可以被分区。

可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。

不管采用哪种方法，都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性

有两种类型的分区索引：本地分区索引和全局分区索引。

每个类型都有两个子类型，有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引，就必须是本地索引。

把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。

在使用分区后的表和索引时，Oracle还支持并行查询和并行DML。这样就可以同时执行多个进程，从而加快处理这条语句。

创建索引的一些规则

1. 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。
这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改。这里需要权衡我们的操作是查询多还是修改多。

2. 把索引与对应的表放在不同的表空间。
当读取一个表时表与索引是同时进行的。如果表与索引和在一个表空间里就会产生资源竞争，放在两个表这空就可并行执行。

3. 最好使用一样大小的块。
Oracle默认五块，读一次I/O，如果你定义6个块或10个块都需要读取两次I/O。最好是5的整数倍更能提高效率。

4. 如果一个表很大，建立索引的时间很长，因为建立索引也会产生大量的redo信息，所以在创建索引时可以设置不产生或少产生redo信息。只要表数据存在，索引失败了大不了再建，所以可以不需要产生redo信息。

5. 建索引的时候应该根据具体的业务SQL来创建，特别是where条件，还有where条件的顺序，尽量将过滤大范围的放在后面，因为SQL执行是从后往前的。

索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询，并且检索行数小于总表行数的5%。则应该考虑。

6. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引

7. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）

8. 小表不要建立索引

9. 对于基数大的列适合建立B树索引，对于基数小的列适合建立位图索引

10. 列中有很多空值，但经常查询该列上非空记录时应该建立索引

11. 经常进行连接查询的列应该创建索引

12. 使用create index时要将最常查询的列放在最前面

13. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引

14. 限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

15 .对于两表连接的字段，应该建立索引。如果经常在某表的一个字段进行Order By 则也应该建立索引。

索引失效的情况

1.使用不等于操作符（not 、<>、!=）

oracle碰到not或者 <> !=会停止使用索引，而采用全表扫描




<span style="color:#000000"><code><span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> student <span style="color:#000088 !important"><strong>where</strong></span> <span style="color:#000088 !important"><strong>not</strong></span> (score=<span style="color:#006666 !important">100</span>);




 




<span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> student <span style="color:#000088 !important"><strong>where</strong></span> score <> <span style="color:#006666 !important">100</span>;



 




<span style="color:#880000 !important">--替换为</span>




 




<span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> student <span style="color:#000088 !important"><strong>where</strong></span> score><span style="color:#006666 !important">100</span> <span style="color:#000088 !important"><strong>or</strong></span> score <<span style="color:#006666 !important">100</span></code></span>

通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描。

2. 使用IS NULL 或IS NOT NULL

使用IS NULL 或IS NOT NULL同样会限制索引的使用。
因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设成 NOT NULL。
如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> student <span style="color:#000088 !important"><strong>where</strong></span> score <span style="color:#000088 !important"><strong>is</strong></span> <span style="color:#000088 !important"><strong>not</strong></span> <span style="color:#000088 !important"><strong>null</strong></span>;</code></span>

索引上使用空值比较将停止使用索引.

3.使用函数

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询不会使用索引（只要它不是基于函数的索引）




<span style="color:#000000"><code><span style="color:#000088 !important"><strong>select</strong></span> empno,ename,deptno <span style="color:#000088 !important"><strong>from</strong></span> emp  <span style="color:#000088 !important"><strong>where</strong></span>  trunc(hiredate)=<span style="color:#009900 !important">'01-MAY-81'</span>;




 




<span style="color:#880000 !important">--把上面的语句改成下面的语句，这样就可以通过索引进行查找。</span>





<span style="color:#000088 !important"><strong>select</strong></span> empno,ename,deptno <span style="color:#000088 !important"><strong>from</strong></span> emp <span style="color:#000088 !important"><strong>where</strong></span>  hiredate<(to_date(<span style="color:#009900 !important">'01-MAY-81'</span>)+<span style="color:#006666 !important">0.9999</span>);</code></span>

4.索引列上进行计算

索引列上不能进行计算

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>SELECT</strong></span> Col <span style="color:#000088 !important"><strong>FROM</strong></span> tbl <span style="color:#000088 !important"><strong>WHERE</strong></span> col / <span style="color:#006666 !important">10</span> > <span style="color:#006666 !important">10</span></code></span>

则会使索引失效，应该改成

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>SELECT</strong></span> Col <span style="color:#000088 !important"><strong>FROM</strong></span> tbl <span style="color:#000088 !important"><strong>WHERE</strong></span> col > <span style="color:#006666 !important">10</span> * <span style="color:#006666 !important">10</span></code></span>

5.比较不匹配的数据类型

假设account_number是一个VARCHAR2类型,在account_number字段上有索引。
下面的语句将执行全表扫描：

<span style="color:#000000"><code> <span style="color:#000088 !important"><strong>select</strong></span> bank_name,address,city,state,zip <span style="color:#000088 !important"><strong>from</strong></span> banks <span style="color:#000088 !important"><strong>where</strong></span> account_number = <span style="color:#006666 !important">990354</span>;</code></span>

Oracle可以自动把where子句变成to_number(account_number)=990354，这样就限制了索引的使用。

改成下面的查询就可以使用索引：

<span style="color:#000000"><code> <span style="color:#000088 !important"><strong>select</strong></span> bank_name,address,city,state,zip <span style="color:#000088 !important"><strong>from</strong></span> banks <span style="color:#000088 !important"><strong>where</strong></span> account_number =<span style="color:#009900 !important">'990354'</span>;</code></span>

特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。

6.通配符在搜索词首出现时，oracle不能使用索引




<span style="color:#000000"><code><span style="color:#880000 !important">--我们在name上创建索引；</span>




 




<span style="color:#000088 !important"><strong>create</strong></span> index index_name <span style="color:#000088 !important"><strong>on</strong></span> student(<span style="color:#009900 !important">'name'</span>);



 




<span style="color:#880000 !important">--下面的方式oracle不适用name索引</span>




 




<span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> student <span style="color:#000088 !important"><strong>where</strong></span> name <span style="color:#000088 !important"><strong>like</strong></span> <span style="color:#009900 !important">'%xgj%'</span>;



 




<span style="color:#880000 !important">--如果通配符出现在字符串的其他位置时，优化器能够利用索引；如下：</span>




 




<span style="color:#000088 !important"><strong>select</strong></span> * <span style="color:#000088 !important"><strong>from</strong></span> student <span style="color:#000088 !important"><strong>where</strong></span> name <span style="color:#000088 !important"><strong>like</strong></span> <span style="color:#009900 !important">'xgj%'</span>;</code></span>

7.用UNION替换OR(适用于索引列)

union:是将两个查询的结果集进行追加在一起，它不会引起列的变化。

由于是追加操作，需要两个结果集的列数应该是相关的，并且相应列的数据类型也应该相当的。

union 返回两个结果集，同时将两个结果集重复的项进行消除。

如果不进行消除，用UNOIN ALL.

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效.

如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.

在下面的例子中, LOC_ID 和REGION上都建有索引.

　　高效:




<span style="color:#000000"><code>　　<span style="color:#000088 !important"><strong>SELECT</strong></span> LOC_ID , LOC_DESC , REGION




　　<span style="color:#000088 !important"><strong>FROM</strong></span> LOCATION



　　<span style="color:#000088 !important"><strong>WHERE</strong></span> LOC_ID = <span style="color:#006666 !important">10</span>




　　<span style="color:#000088 !important"><strong>UNION</strong></span>




　　<span style="color:#000088 !important"><strong>SELECT</strong></span> LOC_ID , LOC_DESC , REGION



　　<span style="color:#000088 !important"><strong>FROM</strong></span> LOCATION



　　<span style="color:#000088 !important"><strong>WHERE</strong></span> REGION = “MELBOURNE”</code></span>

　　低效:
　




<span style="color:#000000"><code>　<span style="color:#000088 !important"><strong>SELECT</strong></span> LOC_ID , LOC_DESC , REGION




　　<span style="color:#000088 !important"><strong>FROM</strong></span> LOCATION



　　<span style="color:#000088 !important"><strong>WHERE</strong></span> LOC_ID = <span style="color:#006666 !important">10</span> <span style="color:#000088 !important"><strong>OR</strong></span> REGION = “MELBOURNE”</code></span>

　　如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

8. 用EXISTS替代IN、用NOT EXISTS替代NOT IN

在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接. 在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

在子查询中, NOT IN子句将执行一个内部的排序和合并.

无论在哪种情况下, NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).

为了避免使用NOT IN, 我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

例子：

高效:

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>SELECT</strong></span> * <span style="color:#000088 !important"><strong>FROM</strong></span> EMP (基础表) <span style="color:#000088 !important"><strong>WHERE</strong></span> EMPNO > <span style="color:#006666 !important">0</span> <span style="color:#000088 !important"><strong>AND</strong></span> <span style="color:#000088 !important"><strong>EXISTS</strong></span> (<span style="color:#000088 !important"><strong>SELECT</strong></span> ‘X’ <span style="color:#000088 !important"><strong>FROM</strong></span> DEPT <span style="color:#000088 !important"><strong>WHERE</strong></span> DEPT.DEPTNO = EMP.DEPTNO <span style="color:#000088 !important"><strong>AND</strong></span> LOC = ‘MELB’)</code></span>

低效:

<span style="color:#000000"><code><span style="color:#000088 !important"><strong>SELECT</strong></span> * <span style="color:#000088 !important"><strong>FROM</strong></span> EMP (基础表) <span style="color:#000088 !important"><strong>WHERE</strong></span> EMPNO > <span style="color:#006666 !important">0</span> <span style="color:#000088 !important"><strong>AND</strong></span> DEPTNO <span style="color:#000088 !important"><strong>IN</strong></span>(<span style="color:#000088 !important"><strong>SELECT</strong></span> DEPTNO <span style="color:#000088 !important"><strong>FROM</strong></span> DEPT <span style="color:#000088 !important"><strong>WHERE</strong></span> LOC = ‘MELB’)</code></span>

Sql优化的一点建议

Sql 优化:

当Oracle数据库拿到SQL语句时，其会根据查询优化器分析该语句，并根据分析结果生成查询执行计划。
也就是说，数据库是执行的查询计划，而不是Sql语句。

查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。

其中基于规则的查询优化器在10g版本中消失。

对于规则查询，其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。

1、先执行From ->Where ->Group By->Order By
2、执行From 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。
3、对于Where字句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在Where子句的末尾，而对于多表之间的连接，则写在之前。
因为这样进行连接时，可以去掉大多不重复的项。
4、SELECT子句中避免使用(*)ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间

https://blog.csdn.net/yangshangwei/article/details/52949803

相关标签：小小工匠 oracle 索引数据库

上一篇： Oracle ORA-12541：TNS：无监听程序

下一篇： Oracle（一）

各种Oracle索引类型介绍

各种Oracle索引类型介绍

Oracle中的索引详解

Oracle:索引列压缩的分析对比

ORACLE 索引概述

5.8.1 唯一索引和非唯一索引

5.8.2 复合索引

5.8.3 索引和键

5.8.4 索引和空值

5.8.5 函数索引

5.8.5.1 使用函数索引

5.8.5.2 函数索引的优化

5.8.5.3 函数索引的依赖性

5.8.6 索引是如何存储的

5.8.6.1 索引块的格式

5.8.6.2 索引的内部结构

5.8.6.3 索引的属性

5.8.6.4 平衡树结构的优势

5.8.7 索引唯一扫描

5.8.8 索引区间扫描

5.8.9 键压缩

5.8.9.1 索引键的前缀和后缀

5.8.9.2 性能上及存储上的考虑

5.8.9.3 使用键压缩

5.8.10 逆序键索引

5.8.11 位图索引

5.8.11.1 数据仓库应用中位图索引的优势

5.8.11.2 基数

5.8.11.3 位图索引和空值

5.8.11.4 分区表上的位图索引

5.8.12 位图连接索引

Oracle-index索引解读

概述

为什么需要索引

什么是索引

索引的原理

示例分析一

示例分析二

什么时候用索引

索引的语法

创建索引

修改索引

重命名索引

合并索引

重建索引

删除索引

查看索引

索引分类

B树索引

说明

特点

创建

适合使用场景

位图索引

说明

特点

创建

适合使用场景

注意事项

函数索引

说明

创建

分区索引

创建索引的一些规则

索引失效的情况

1.使用不等于操作符（not 、<>、!=）

2. 使用IS NULL 或IS NOT NULL

3.使用函数

4.索引列上进行计算

5.比较不匹配的数据类型

6.通配符在搜索词首出现时，oracle不能使用索引

7.用UNION替换OR(适用于索引列)

8. 用EXISTS替代IN、用NOT EXISTS替代NOT IN

Sql优化的一点建议

oracle分区表介绍（含本地分区索引和全局分区索引介绍）

各种Oracle索引类型介绍

Oracle raw数据类型介绍

介绍Oracle Virtual Index虚拟索引

Oracle学习笔记：分析各种数据类型

js的各种数据类型判断的介绍