Oracle性能分析7:创建索引
在创建索引时,我们往往希望可以预估索引大小,以评估对现有project环境的影响,我们也希望创建索引的过程可以最小化的影响我们正在执行的project环境,并能查看索引的状况。
预估索引大小
预估索引大小,最好的办法是在測试环境中创建它,測试环境最好包括完整的project环境数据,否则仅仅有通过部分数据来推算完整的索引大小。
假设不能搭建測试环境,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)锁,也就是在创建索引期间你可以查询一个表,可是无法以不论什么方式改动这个表。这导致创建索引的操作在project环境中实施时会存在问题,因此,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操作所做的改动同步到新的索引。这样就有效的攻克了project环境中实施的问题。
查看索引信息
能够通过上面的方式查看创建成功后的索引:
select * from user_indexes where index_name = upper('idx_historyalarm')
能够通过以下的方式显示所占用的空间的实际数额:
select bytes from user_segments where segment_name = upper('idx_historyalarm')
以下是输出结果,空间分配字节数的预计量略小于实际使用量:
BYTES -------------------------- 293601280
随着记录插入到表中,该索引将添加,对索引大小监控能够确保有足够的磁盘空间,以适应未来的数据添加需求。