Oracle性能分析8：创建索引，oracle性能索引

在创建索引时，我们往往希望能够预估索引大小，以评估对现有工程环境的影响，我们也希望创建索引的过程能够最小化的影响我们正在运行的工程环境，并能查看索引的状况。

预估索引大小

预估索引大小，最好的办法是在测试环境中创建它，测试环境最好包含完整的工程环境数据，否则只有通过部分数据来推算完整的索引大小。
如果不能搭建测试环境，Oracle提供了存储过程DBMS_SPACE.CREATE_INDEX_COST来估算索引的大小，下面是一个例子：

declare
  used_bytes  number(10);
  alloc_bytes number(10);
begin
  dbms_stats.gather_table_stats(user, 'HISTORYALARM');
  dbms_space.create_index_cost('create index idx_historyalarm on historyalarm(position1)',
                               used_bytes      => used_bytes,
                               alloc_bytes     => alloc_bytes);
  dbms_output.put_line('used_bytes : ' || used_bytes);
  dbms_output.put_line('alloc_bytes : ' || alloc_bytes);
end;

在计算索引大小的时候，你需要先收集表的统计信息，因为Oracle是根据表的数据信息来推算的，下面是输出的结果：

used_bytes : 151994511
alloc_bytes : 251658240

创建索引

在估算了索引大小后，如果没有问题，就可以开始实际的创建索引了：

create index idx_historyalarm on historyalarm(position1) tablespace uep4x_fm_index

创建索引的索引会对表加排他DDL锁（Exclusive DDL lock），这会防止其他会话得到他们自己的DDL锁或TM（DML）锁，也就是在创建索引期间你能够查询一个表，但是无法以任何方式修改这个表。这导致创建索引的操作在工程环境中实施时会存在问题，因此，Oracle企业版提供了在线创建索引的方式：

create index idx_historyalarm on historyalarm(position1) tablespace uep4x_fm_index online

online将改变具体创建索引的过程，Oracle不会再加一个排他DDL锁来防止数据修改，改为试图得到表上的一个低级（mode 2）TM锁，这将有效地防止其他DDL（data definition language，包括CREATE、ALTER、DROP等）操作发生，但允许DML（data manipulation language，包括SELECT、UPDATE、INSERT、DELETE）操作正常进行。在Oracle的内部，Oracle会将创建索引期间做的DML操作放到一个临时表中，等创建索引操作完成后，再将DML操作所做的修改同步到新的索引。这样就有效的解决了工程环境中实施的问题。

查看索引信息

可以通过上面的方式查看创建成功后的索引：

select * from user_indexes where index_name = upper('idx_historyalarm')

可以通过下面的方式显示所占用的空间的实际数额：

select bytes from user_segments where segment_name = upper('idx_historyalarm')

下面是输出结果，空间分配字节数的估计量略小于实际使用量：

BYTES
--------------------------
293601280

随着记录插入到表中，该索引将增加，对索引大小监控可以确保有足够的磁盘空间，以适应未来的数据增加需求。

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；
以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

看看这个你就明白了
 

索引就好象一本字典的目录。凭借字典的目录，我们可以非常迅速的找到我们所需要的条目。数据库也是如此。凭借Oracle数据库的索引，相关语句可以迅速的定位记录的位置，而不必去定位整个表。
　 　虽然说，在表中是否创建索引，不会影响到Oracle数据库的使用，也不会影响数据库语句的使用。这就好像即使字典没有目录的话，用户仍然可以使用它一 样。可是，若字典没有目录，那么可想而知，用户要查某个条目的话，其不得不翻遍整本字典。数据库也是如此。若没有建立相关索引的话，则数据库在查询记录的 时候，不得不去查询整个表。当表中的记录比较多的时候，其查询效率就会很低。所以，合适的索引，是提高数据库运行效率的一个很好的工具。
　 　不过，并不是说表上的索引越多越好。过之而不及。故在数据库设计过程中，还是需要为表选择一些合适的索引。宁缺勿滥，这是建立索引时的一个遵循标准。在 理论上，虽然一个表可以设置无限的索引。但是，数据库管理员需要知道，表中的索引越多，维护索引所需要的开销也就越大。每当数据表中记录有增加、删除、更 新变化的时候，数据库系统都需要对所有索引进行更新。故数据库表中的索引绝对不是多多益善。具体来说，在索引建立上，笔者对大家有如下建议。
　　建议一：在基数小的字段上要善于使用位图索引。
create bitmap index index_name on table_name (column_name);
　　基数是位图索引中的一个基本的定义，它是指数据库表中某个字段内容中不重复的数值。如在员工信息表中的性别字段，一般就只有男跟女两个值，所以，其基数为2;婚姻状况字段的话，则其只有已婚、未婚、离婚三种状态，其基数就为3;民族一览内也是只有有限的几个值。
　　对于要查询基数小的字段，如现在用户想查找所有婚姻状况为已婚的女性时，利用位图索引可以提高查询的效率。这主要是因为标准索引是通过在索引中保存排序过的索引列以及对应的ROWID来实现的。若我们在基数小的列上建立标准索引的话，则其会返回大量的记录。
　 　而当我们在创建位图索引的时候，在Oracle会对整个表进行扫描，并且会为索引列的每个取值建立一个位图。若内容相同，则在位图上会以一个相同的数字 表示。此时，若这个字段的基数比较小的话，则若需要实现对整个字段的查询的话，效率就会非常的高。因为此时，数据库只要位图中数字相同的内容找出来即可。
　 　除了在数据表某列基数比较小的情况下，采用位图索引外，我们往往在一些特殊的情况下，也会建议采用位图索引。最常见的情况是，在Where限制条件中， 若我们多次采用AND或者OR条件时，也建议采用位图索引。因为当一个查询饮用了一些部署了位图索引的列的时候，这些位图可以很方便的与AND或者Or 运算符操作结合以快速的找出用户所需要的记录。
　　但是，这里要注意，不是在条件语句中包含运算符的时候，采用位图索引都能够提供比较高的 效率。一般来说，只有AND 或者OR运算符的时候，位图索引才会比较具有优势。若此时用户采用大于号或者不等于号作为条件语句中的限制条件的时候，则往往采用标准索引具有更大的优 势。
　　所以，笔者在数据库设置中，一般只有在三种情况下才采用位图索引。一是列的基数比较小，而有可能需要根据这些字段的内容查找相关的 记录;二是在条件语句中，用到了AND或者OR运算符的时候。除了这两种情况外，最好能够采用其他适合的索引。第三种情况是，需要用到NULL作为查询的 限制条件。因为标准查询一般情况下，会忽略所有的NULL值列。也就是说，若需要查询所有没有身份证号码的员工的信息......余下全文>>