分类:
逻辑上:1、单列索引 2、多行索引 3、唯一索引(意味着不会有两行记录相同的索引键值) 4、非唯一索引 5、函数索引 6、域索引
7、函数索引
物理上:1、分区索引(是指索引可以分散地存在于多个不同的表空间中,其优点是可以提高数据查询的效率。) 2、非分区索引 (全表索引)
数据结构:
B-tree:
1、适合与大量的增、删、改(OLTP)
2、不能用包含OR操作符的查询
3、适合高基数的列(唯一值多)
4、B*树索引的存储结构类似书的索引结构,有分支和叶两种类型的存储数据块,分支块相当于书的大目录,叶块相当于索引到的具体的书页。Oracle用B*树机制存储索引条目,以保证用最短路径访问键值。默认情况下大多使用B*树索引,该索引就是通常所见的唯一索引、逆序索引。
Bitmap:
1、适合与决策支持系统
2、做UPDATE代价非常高
3、非常适合OR操作符的查询
4、位图索引存储主要用于节省空间,减少oracle对数据块的访问。它采用位图偏移方式来与表的行ID号对应,采用位图索引一般是重复值太多的表字段。
使用原则:
1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进并且检索行数小于总表行数的15%。则应该考虑。
2、对于两表连接的字段,应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。
3、主键和唯一关键字所在的列自动具有索引,但应该在与之关联的表中的外部关键字所在的列上创建索引。
4、索引主要进行提高数据的查询速度。 当进行DML时,会更新索引。因此索引越多,则DML越慢,其需要维护索引。 因此在创建索引及DML需要权衡。
创建索引:
1、单列索引
Create Index <自定义索引名Index_Name> On <Table_Name>(Column_Name)
2、多列索引(复合索引)
Create Index <自定义索引名Index_Name> On <Table_Name>(Column_Name1,Column_Name2)
索引失效情况:
1、索引列上不要使用is not null/is null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。
2、索引列上不要使用函数,SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC' 注意:对于 MIN, MAX 函数,Oracle 仍然使用索引。
3、索引列上不要进行计算SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效
4、索引列上不要使用( != 、 <> )如:SELECT Col FROM tbl WHERE col ! = 10
应该 改成:SELECT Col FROM tbl WHERE col > 10 OR col < 10 。
5、索引列上不要使用 LIKE ‘%T’ 进行模糊查询会使索引失效
6、索引列上不要比较不匹配数据类型, 例如:
dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。
select * from dept where dept_id = 900198;
这是因为 oracle 会自动把 where 子句转换成 to_number(dept_id)=900198,相当于使用函数,这样就限制了索引的使用。正确写法如下:
select * from dept where dept_id = '900198';
7、复合索引不要单独使用非第一索引列
如果在where 子句中有OR 操作符或单独引用Column_Name2列(索引列的后面列) 则将不会走索引,将会进行全表扫描。
重建索引:
重建索引的前提:
1、表上频繁发生update,delete操作;
2、表上发生了alter table ..move操作(move操作导致了rowid变化)
重建索引的标准:
1、索引重建是否有必要,一般看索引是否倾斜的严重,是否浪费了空间, 那应该如何才可以判断索引是否倾斜的严重,是否浪费了空间, 对索引进行结构分析(如下):
SQL>Analyze index index_name validate structure;
2、在执行步骤1的session中查询index_stats表,不要到别的session去查询。
SQL>select height,DEL_LF_ROWS/LF_ROWS from index_stats;
说明:当 查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 , 该索引考虑重建 。
重建索引的方式:
alter index indexname rebuild; 或alter index indexname rebuild online;
说明:此方式比较快,可以在24*7环境中实现,建议使用此方式。
注意:rebuild 会阻塞 dml 操作 ,rebuild online 不会阻塞 dml 操作
重建分区索引方法:
1、Alter index indexname rebuild partition paritionname tablespace tablespacename;
Alter index indexname rebuild subpartition partitioname tablespace tablespacename;
说明:Partition name 可以从user_ind_partitions查找
Tablepace 参数允许alter index操作更改索引的存储空间
索引状态描述:
在数据字典中查看索引状态,发现有三种:
1、valid:当前索引有效
2、N/A :分区索引有效
3、unusable:索引失效
其他:
1、truncate 分区操作和truncate 普通表的区别?
2、Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响;
3、Truncate 分区操作不会释放全局索引中的空间,而truncate 普通表会释放索引所占空间;
4、rename 表名操作对索引没有影响,因为rename操作只是更改了数据字典,表中数据行的rowid并没有发生变化