索引的目的是为了select速度快,对增删改的话要做多余的工作(更新索引),
所以经常增删改的表尽量不要索引。
1、平衡查询和DML需要
2、data和index放在不同的表空间
3、使用一致的extent大小:5个blocks或minimum extent的倍数
4、考虑使用nologging对于大的索引
5、在索引里initrans要大于表里的initrans
索引分类
逻辑上:
Single column or concatenated基于单列或复合(多个列)索引
Unique or nonunique唯一(键)或非唯一索引
Function-based(基于函数索引)
Domain
物理上:
分区或不分区
B-tree(平衡树)索引:正常索引或倒序索引
Bitmap索引
B-tree结构:
root节点
branch节点
leaf节点
leaf构成:
index entry(对应一条记录):
index entry header(控制信息)
key column length
key column value
rowid
Bitmap索引(对于取值只有固定几种的列,对where OR运算速度快,主应用数据仓库系统)
leaf节点构成:
key startrowid endrowid bitmap
男 xxxx xxxx 10010101010 (1为该记录值为男)
女 xxxx xxxx 01101010101 (1为该记录值为女)
创建B-tree索引
create index 表名_列名_idx
on 表名(列名)
pctfree 30
initrans 3
storage(......)
tablespace xxwz_index;
创建bitmap索引
create bitmap index 表名_列名_idx
on 表名(列名)
...
;
修改索引
alter index xxx
storage ();
增加分配索引空间
alter index xxx
allocate extent(size 200k datafile 'xxx.dbf');
释放unused空间
alter index xxx
deallocate unused;
重建索引(原因:当删除一个记录时同时索引并没有物理删除而是标识为不可见,很多deleted entries)
步骤:锁住表-》建个新的-》删除老的-》重命名新的-》解锁表
create index xxx rebuild
tablespace xxwz1_index(可选);(注:默认offline)
在线重建索引
步骤:锁表-》建新的空索引和IOT存储正执行的DML(表的索引:叶子节点是记录)-》释放锁-》用老索引填充新索引-》合并新索引和IOT-》锁表-》再次合并IOT且删除老索引-》重命名新索引-》解锁
alter index xxx rebuild online;
融合索引(解决索引空间碎片化)
步骤:扫描索引的叶子节点-》如果相邻的2个节点能合并成一个节点则合并
alter index xxx coalesce
分析索引结构
analyze index xxx validate structure;
index_stats存储上次分析结构信息
select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;
del_lf_rows超过10%就要考虑重建索引;
删除索引(批量装载时先删除后重建)
drop index xxx;
鉴定出未使用的索引
使用monitoring监控
alter index xxx monitoring usage;
使用v$object_usage查看
set autot on exp;查看下面查询语句是使用什么扫描的
select * from xxx;
select * from v$object_usage;
关闭监控
alter index xxx nomonitoring usage;
获取索引信息
dba_indexs 索引和表的关系
dba_ind_columns 索引和列