oracle笔记整理9——性能调优之索引、表分区、索引分区

1. 索引

1) 需要建索引的列

a) where从句中频繁使用的关键字。
b) sql语句中频繁用于表连接的字段。
c) 可选择性高（重复值少）的字段。

2) 索引限制条件

a) where条件中索引列上使用了否定操作符（比如：<>,!=,is null,is (not) null）,将不会使用索引，直接全表扫描，此时可以把部分否定操作符换成or形式或者union all的形式来执行。
or与in等效，or关键字是否使用索引，视情况而定。

b) 索引列上使用了函数或运算操作，则不会使用索引，转而全表扫描，但可以建立相应的函数索引来实现。

c) like索引列时，通配符出现在词首时不会使用索引，其他位置将使用。

d) 索引列与给定变量的类型不一致时，将不会使用索引（oracle 11g中，可以进行varchar2至number的自动转换，此种情况索引正常使用，反之则没有，故尽量保持类型一致，不要寄希望于oracle的自动转换，否则容易搞混淆）

e) 频繁修改的列上最好不要建立索引，维护索引的开销太大。

3) 组合索引

由多个列构成的索引。
如：create index idx_tmp on temp(col1,col2,col3,……)
引导列（leading column）：col1列为引导列；
限制条件中包含先导列时，该限制条件才会使用该组合索引。因此组合索引中应该按照列的使用频繁程度从高到底排列。

4) 并行索引

a) 快速创建并行索引

create index idx on table_name(id) parallel 4

b) 取消并行索引
服务器资源紧张时，并行索引会引起更严重的资源争用，需要把并行度改回来。

alter index idx noparallel;

5) 监视索引使用情况

a) alter index index_name monitoring usage;

b) select * from V$OBJECT_USAGE;

c) 删除那些不被使用的索引，否则将阻碍性能 。

6) 索引的本质

oracle一般使用平衡二叉树(B—tree)来存储索引，在一个平衡树索引(B-tree index)中，最底层的索引块(叶块(leaf block))存储了被索引的数据值，以及对应的 rowid。叶块之间以双向链表的形式相互连接。位于叶块之上的索引块被称为分支块，分枝块中包含了指向下层索引块的指针。

2. 表分区

1) 原理

：分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。

2) 优势

：
a) 增强可用性:如果表的一个分区发生故障，其余的分区仍可以使用。
b) 均衡I/O:通过把表的不同分区分配到不同的磁盘来平衡I/O 改善性能,从10g开始oracle提供ASM存储管理模式自动均衡I/O.
c) 提高查询速度：对大表的增删改查分解到不同的分区来并行执行。

3) 分类

：range、hash、list、组合分区

4) range：范围分区

Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。当数据在范围内均匀分布时，性能最好。如按照时间划分。如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

5) hash：哈希分区

hash分区是在列的取值难以确定的情况下，会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配。

6) list：列表分区

List分区也需要指定列的值，其分区值必须明确指定（即该列值是可以枚举的），通常建议使用要创建一个default分区存储那些不在明确值范围内的记录

7) 组合分区：

如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。

8) 联机重定义

普通表转换分区表，可以通过联机重定义方法来实现。

3. 索引分区

1) 概念

对大数据量的索引进行分区同样能够优化应用系统的性能。oracle索引分区分为局部/本地索引分区和全局索引分区。

2) 局部索引分区

局部索引分区反应基础表的表结构，使用和分区表同样的分区字段来进行索引分区。独立性强 ，可以单独重建，如果只有一个分区需要维护，则只有一个本地索引受影响。

3) 全局索引分区

全局索引分区不反应基础表的表结构，使用的字段与分区表的分区字段不同，可以保证所有分区中的记录的唯一性。无论表是否分区，都可以进行全局索引分区。

4) 局部索引

建立在表分区的基础上，每一个表分区对应一个索引段，索引列与表分区列无关。局部索引之间相互独立。

5) 全局索引

普通表或者跨表分区的索引，可以实现所有分区中记录的唯一性。