oracle database 11g性能优化攻略,oracle database 11g 性能优化攻略 读书笔记 索引篇

创建索引之前必须考虑的：

1.索引的类型

2.需要包含的列

3.应该使用单独的列还是组合列

4.特殊的属性，例如并行、关闭日志、压缩、不可见索引等；

5.唯一性

6.命名规范

7.表空间布局

8.初始化空间需求及增长

9.对select语句性能的影响(提高)

10对insert、update以及delete语句性能的影响

11如果基础表是分区表，那么使用全局索引还是本地索引。

索引类型：

1.B树索引：默认索引类型，也称为平衡树索引，适用于高基数(不同值数量多的)列

2.B数聚簇索引：适用于聚簇表

3.散列聚簇索引：适用于散列聚簇

4.基于函数的索引：适用于应用了SQL函数的列

5.虚拟列索引：适用于应用了SQL函数的列，可以替代基于函数的索引

6.反转键索引：对于平衡后续有较多数据插入的索引中的I/O非常有用

7.键值压缩索引：适用于引导列重复值较多的组合索引，压缩叶子块条目

8.位图索引：适用于数据仓库环境中低基数的列。这种索引不适用于数据行需要大量更新的在线事务处理(OLTP)数据库

9.位图连接索引：适用于数据仓库环境下连接事实表和维度表的查询

10.全局分区索引：跨分区表中的所有分区的全局索引

11.本地分区索引：基于分区表中单独分区的本地索引

12.域索引：用于具体的应用或程序模块

估算一个索引所需要的空间

set serverout on

exec dbms_stats.gather_table_stats(user,'CUST');

variable used_bytes number

variable alloc_bytes number

exec dbms_space.create_index_cost('create index cust_idx2 on cust(first_name)', :used_bytes, :alloc_bytes);

print :used_bytes

print :alloc_bytes

需要创建索引的列

1.为每张表定义一个主键约束，将会在主键中指定的列上自动创建索引(攻略2-3)https://www.cndba.cn/a_799330808/article/4085

2.在需要保持唯一的非空列(与主键列不同)上创建唯一键约束。这将会在唯一键约束所声明的列上自动创建一个索引(攻略2-4)

3.在外键列上显示创建索引(攻略2-5)

4.在经常作为谓词出现在执行频率很高的SQL查询WHERE子句中的列上创建索引。

在决定为哪些列创建索引之后，建议按照易于维护的标准来创建。具体来说，在创建索引时要遵循下面这些知道方针

1.除非有非常充分的理由使用其他索引类型，否则就使用默认的B数索引。

2.为索引创建一个独立的表空间。这样一来，当你执行备份和恢复等任务时，能够更方便地分别管理索引和表。https://www.cndba.cn/a_799330808/article/4085

3.让索引从表空间中继承存储属性。这样你在创建表空间时能够指定存储属性，而不必为单独的索引管理存储属性。https://www.cndba.cn/a_799330808/article/4085

4.如果对于多个索引具有不同的存储需求，那么可以为每种类型的索引创建一个独立的表空间。例如，针对不同索引大小，

创建具有适当存储特性的INDEX_LARGE、INDEX_MEDIUM和INDEX_SMALL表空间。

索引的创建和维护原则

明智而审慎地添加索引。通过测试来量化索引所带来的性能收益

使用正确的索引类型

使用一致的命名标准

监控索引，并删除不使用的索引

除非有非常充分的理由，否则不要重建索引。(重建索引查看攻略2-15)

在删除一个索引之前，考虑将其标记为未使用或不可见。(可以更好的确定删除一个索引是否会引起问题)

如果创建组合索引可以实现只需从索引结构中取数据，则考虑创建

考虑在order by、group by、union、distinct子句中使用的列上创建索引(在使用这些SQL结构时将具有更高的查询性能)

没有数据段的索引

可以通过nosegment子句命令ORACLE创建一个永远不会使用且不会为其分配任何盘区的索引

create index cust_idx1 on cust(first_name) nosegment;

即使这个索引永远也不会使用，仍然可以通过oracle的初始化参数‘_use_nosegment_indexes’来确定她是否被优化器使用。

例如

alter session set “_use_nosegment_indexes”=true;

set autot trace exp

select first_name from cust where first_name=’JIM’;

当确定可以使用该索引时，把nosegment 的索引删除，然后使用不含nosegment的语句重建它

查看外键约束的相关索引

select a.constraint_name cons_name,a.table_name tab_name,b.column_name cons_column,nvl(c.column_name,'***No Index***') ind_column

from user_constraints a join user_cons_columns b on a.constraint_name=b.constraint_name left outer join user_ind_columns c on b.column_name=c.column_name and b.table_name=c.table_name

where constraint_type='R';

创建组合索引需要考虑的因素

如果几个列经常在where子句中搭配使用，考虑创建组合索引。

如果某个列还会(在其他查询中)单独出现在where子句中，那么将这一列放在组合索引的前导端(所定义的第一列)。

记住，如果放在后面定义的某一列单独出现在where子句中，oracle仍然可以使用这个后端索引。

但是如果经常使用组合索引中位于后面的列，那么就可以考虑在这一列上创建单列索引

压缩索引

当一个需要建立索引的列中有很多相同的行，可以使用compress N子句创建压缩索引

create index cust_cidx1 on cust(last_name,first_name) compress 2;

上面这行代码指示oracle在两个列(last_name和first_name)上创建一个压缩索引。对于这个例子，如果我们确定只有第一个列存在大量的重复值，

那么可以为N指定数值1，命令COMPRESS N子句仅对第一列(LAS_NAME)进行压缩：

create index cust_cidx1 on cust(last_name,first_name) compress 1;https://www.cndba.cn/a_799330808/article/4085

验证是否使用了索引压缩，以及相应的前缀长度：

select index_name,compression,prefix_length from user_indexes where index_name =’CUST_CIDX1’;

通过重建索引来修改前缀长度。下面这段代码将前缀长度修改为1：

alter index cust_cidx1 rebulid compress 1;

可以通过重建为已有的索引启用或禁用压缩。

alter index cust_cidx1 rebuild nocompress;

在虚拟列上创建索引

你现在正在使用一个基于函数的索引，单线要获得更好的性能。将基于函数的索引替换为一个虚拟列，然后再虚拟列上创建索引

https://www.cndba.cn/a_799330808/article/4085

首先在表中添加一个将SQL函数封装起来的虚拟列：

alter table cust add(up_name generated always as (UPPER(first_name)) virtual);

接下来在虚拟列上创建一个索引

create index cust_vidx1 on cust(up_name);https://www.cndba.cn/a_799330808/article/4085

虚拟列并不是没有代价的。如果又一张已经存在的表，要想创建虚拟列必须创建并维护所需的DDL，而基于函数的索引能够独立地从表中添加、修改或删除。https://www.cndba.cn/a_799330808/article/4085

关于虚拟列地注意事项

只能在普通的堆组织表上定义虚拟列。不能在索引组织表、外部表、临时表、对象表或聚簇表上创建虚拟列。https://www.cndba.cn/a_799330808/article/4085

虚拟列不能引用引用其他虚拟列。

虚拟列仅能引用定义它的表中的列。

虚拟列的输出必须你标量值(单独的一个值，而不是一组值)。

https://www.cndba.cn/a_799330808/article/4085

平衡索引的I/O

使用一个序列来填充表的主键列，但意识到这样会造成索引前导端的资源争夺，因为索引值都是近似的，从而导致对同一个数据块的多次插入，引起争夺。

你想要分散对索引的插入，从而使插入的值更均匀地分布在索引结构中。可以使用反转键索引来实现这一点。

创建一个反转键索引

create index inv_idx1 on inv(inv_id) reverse;

验证索引类型使反转键索引

select index_name,index_type from user_indexes;

反转索引地类型是：NORMAL/REV

除了所在索引条目创建时索引键的字节时反转的以外，反转键索引与B树索引类似。例如，如果索引值时100、101和102，反转键索引的值时001、101和201

通过rebuild reverse子句将一个已有的索引重建为反转索引：

alter index f_regs_idx1 rebuild reverse;https://www.cndba.cn/a_799330808/article/4085

类似地，如果想要将反转键索引修改为正常顺序地索引，使用rebuild noreverse；

alter index f_regs_idx1 rebuild noreverse;

新增一个索引而不影响已有的应用

通常第三方供应商不支持用户在应用上新增自己的索引。但是，可能会有这样一种情况，你确定能够提高一个查询的性能，而不会影响应用中的其他查询。可以将索引创建为不可见，

然后通过提示(hint)显示命令一个查询使用这个索引

create index inv_idx1 on inv(inv_id) invisible;

下一步，确保optimizer_use_invisible_indexes 初始化参数设置为true (默认值为false)。

alter system set optimizer_use_invisible_indexes=true；

现在，使用一个提示告诉优化器该索引的存在：

select /*+ index(inv inv_idx1) */ inv_id from inv from where inv_id=1;

要删除索引时，可以先把索引设为不可见 alter index inv_idx1 invisible;

一段时间后，确定不出问题后，再把索引删除

索引组织表 详情查看 攻略2-14

索引创建速度最大化

1.采用nologging子句

2.增加并行度

create index inv_idx1 on inv(inv_id,inv_id2) nologging tablespace inv_mgmt_index;

create index inv_idx1 on inv(inv_id,inv_id2) parallel 2 tablespace inv_mgmt_index;

回收索引的空间

alter index f_regs_idx1 shrink space;

版权声明：本文为博主原创文章，未经博主允许不得转载。