Oracle性能分析7：创建索引

32e2

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

预估索引大小，最好的办法是在测试环境中创建它，测试环境最好包含完整的工程环境数据，否则只有通过部分数据来推算完整的索引大小。
如果不能搭建测试环境，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  

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