一、创建索引的原则
检索数据不超过表中数据的10%或15%时,创建索引
相对较小的表不宜创建索引
对包含在表连接操作中的列创建索引
对在where子句中频繁使用的列创建索引
对包含在order by和group by或者涉及排序的union和distinct等其他操作中的列创建索引
有长字符组成的列不宜创建索引
被频繁更改的列不宜创建索引
对表中选择性高的列创建索引
在OLTP中,表中索引宜少不宜多;而OLAP中,表中的索引可以多一点
where子句中经常涉及多列,可以考虑创建组合索引
二、估计索引的大小
declare
l_used_bytes number;
l_allocate_bytes number;
begin
dbms_space.create_index_cost(
ddl =>'create index index_name on schema.table_name(column_name)',
used_bytes =>l_used_bytes,
alloc_bytes =>l_allocate_bytes);
dbms_output.put_line('used_bytes='||l_used_bytes||','||'allocate_bytes='||l_allocate_bytes);
end;
三、创建唯一索引
create unique index index_name on schema.table_name(column_name);
四、创建B-树索引 --适于在选择性高、更改较频繁的列上创建
create index index_name on schema.table_name(column_name);
五、创建位图索引 --适于在选择性低、更改不频繁的列上创建
create bitmap index index_name on schema.table_name(column_name);
六、创建反键索引 --适用于列值前面差异小,后面差异大的列。在索引段中索引列的值反向存储。
create index index_name on schema.table_name(column_name) reverse;
七、创建函数索引 --不能使用分组函数
create index index_name on schema.table_name(function_name(column_name));
八、创建组合索引 --应该把经常使用的列放在最前面
create index index_name on schema.table_name(column_name1,column_name2 [,column_name3 [, ...]]);
九、分区索引
1、全局索引 --索引激列和表的分区键不同;不能使用列表分区对索引进行分区;使用范围分区时,必须包含maxsize分区;全局索引的分区和表的分区没有关联。
create index index_name on schema.table_name(columa_name)
global
partition by 分区方法(column_name)
(
partition par_name ..........
)
2、本地索引 --索引列和表的分区键不同,索引分区和表的分区一一对应。
create index index_name on schema.table(column_name)
local;
十、合并和重建索引 --合并仅合并碎片,重建索引使用原索引数据创建新索引,要求表空间能同时容纳两个索引。重建不仅达到合并碎片的目的,也使索引结构更加紧凑。
合并:alter index index_name coalesce;
重建:alter index index_name rebuild;
联机重建:alter index index_name rebuild online;
十一、监控索引
1、监控
alter index index_name monitoring usage;
2、取消监控 --开始监控之后,经过一段时间,然后取消监控,最后查看索引是否使用
alter index index_name nomonitoring usage;
3、查看使用情况
select * from v$object_usage where index_name='index_name';