oracle索引
oracle索引
作用: 用于加快数据的检索
类似于书籍的目录
索引的缺点
- 降低了dml的效率
- 数据量越大,索引键所占用的空间便越大
- 位图索引不适合OLTP系统,不适合dml频繁的环境
平衡树索引
原理:通过一组排序后的索引键来取代默认的全表扫描检索方式
从而提高检索效率
当创建或删除一个索引时,不会影响基本的表
索引一旦建立,在表上进行DML操作时(例如在执行插入修改或者删除相关操作时),oracle会自动管理索引,但是会降低dml操作效率
oracle创建主键时会自动在该列上创建索引
. 主键!=唯一索引
主键不能为null,但唯一索引可以
分类
- UNIQUE,唯一索引
- NON-UNIQUE(默认),非唯一索引
- REVERSE KEY（数据列中的数据是反向存储的),反向索引
默认索引使用
创建
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
创建普通索引
默认使用NON-UNIQUE
SQL> create index ind_grade on salgrade(grade);
Index created
修改
重命名索引
SQL> alter index ind_grade rename to ind_id;
Index altered
合并索引
表使用一段时间后在索引中会产生碎片,此时索引效率会降低,可以选择重建索引或者合并索引
合并索引和重建索引相比,合并索引无需额外存储空间,代价较低
SQL> alter index ind_id coalesce;
Index altered
重建索引
方式一
删除原来的索引,重新建立索引
方式二
SQL> alter index ind_id rebuild;
Index altered
查看
oracle中表的索引信息存在
user_indexes 和 user_ind_columns 两张表里面
SQL> select * from all_indexes where table_name='SALGRADE';
select * from user_ind_columns where table_name='SALGRADE';
删除
SQL> drop index ind_id;
Index dropped
唯一索引
SQL> create unique index ind_id on salgrade(grade);
Index created
复合索引
即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列
新建复合索引
create index ind_in on salgrade(grade,losal);
第二种查询时依然会走全表扫描,不会通过索引查询
select * from salgrade where grade=1
select * from salgrade where losal=700
位图索引
位图索引是为数据仓库(也就是查询环境)设计的
因此位图索引比较适合查询环境,不适合dml频繁的环境
列基数(列不重复值的个数)小的情况下适合使用位图索引
位图索引适合只有几个固定值的列，如性别、婚姻状况、行政区等等
此处先删除前面创建的索引
SQL> drop index ind_id;
Index dropped
SQL> create bitmap index ind_id on salgrade(grade);
Index created
索引失效原因
null值判断
- is null
- is not null
函数会使索引失效(函数索引除外)
索引列不能计算
like时通配符不能放在第一位
索引建立原则
- 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
- 如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引
- 小表不要建立索引
- 对于基数大的列适合建立B树索引，对于基数小的列适合建立位图索引
- 列中有很多空值，但经常查询该列上非空记录时应该建立索引
- 经常进行连接查询的列应该创建索引
- LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引
- 限制表中索引的数量（创建索引耗费时间？，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）
