oracle中索引的学习

索引是与表关联的可选结构,它只是一种快速访问数据的途径,它只影响执行速度.
索引根本目地:提高查询效率

1. 合理使用索引的原则:
a) 在条件表达示中经常用到的不同值效多的列上建立索引
b) 在不同值少的列上,不要创建索引或位图索引.如:’姓别’列上只有’男’和’女’,不要在这样的列上创建索引,这样不会提高查询效率.
c) 在经常建立连接,但没有指定外键的列上建立索引
d) 在频繁进行排序和分组的列上建立索引
e) 如果排序的列有多个可以建立分组索引
f) 如果条件表达示经常对某一列应用某个函数后指定查询,可以用函数索引

2. 下列情况下建立了索引,但不会此使用索引
a) 存在数据类型隐式转换时
如:select * from emp where empno=’123’这里存在字符转换成数字,不会使用索引
b) 在建立索引的某一列上进行算术运算,不会使用索引
如:select * from emp where sal*2<1000
如果改成select * from emp where sal<1000/2这样就可以使用到索引
c) 使用’<>’不等号时,不会使用索引
d) 使用substr字符串函数时,不会使用索引
e) ‘%’通配符放在第一位时, 不会使用索引
f) 使用‘||’字符串连接符时, 不会使用索引

3. 标准索引语法
创建索引语法:
Create index 索引名
On 表名(列名1,列名2……列名N)
[tablespace 为索引指定的表空间名];

重建索引语法:
Alter index 索引名 rebuild

删除索引语法:
Drop index 索引名

4. 唯一索引
索引可以是唯一的,也可以是非唯一的.唯一索引可以确保在定义索引的列中,表的任意两行的值不相同.非唯一索引没有在列值上规定此限制,orcale自动为表的主键列创建唯一索引.
创建唯一索引语法:
Create unique index 索引名
On 表名(列名)

5. 组合索引
组合索引是在表中的多个列上创建的索引.组合索引中列的顺序是任意的,不必是表中相邻的列.如果select语句中的where子句引用了组合索引中的所有列或大多数列,则组合索引可以提高数据检索速度.创建组合索引时,应注意定义中使用的列的顺序.通常最频繁访问的列应放置在列表的最前面.
创建组合索引语法:
Create index 索引名
On 表名(列名1,列名2……列名N)

6. 反向索引
反向索引是一种特殊的索引,在索引基于含有序数的列时非常有用,如果一个标准索引基于一个含有这种数据的列,往往因为数据过于密集而降底读取性能.反向索引通过简单的反向被索引的列中的数据来解决问题,首先反向每个列键值的字节,然后在反向后的新数据上进行索引,而新数据在值的范围上分布通常比原来的有序数更均匀.因此反向键索引通常建立在一些值连续增长的列上
创建反向键索引语法:
Create index 索引名
On 表名(列名) reverse

重建反向键索引语法:
Alter index 索引名 rebuild noreverse

7. 位图索引
使用位图索引适应于低基数列,也就是列的不同值数目比表行数少的列.如果某个列的值重复了超过一百次,则可以考虑在该列上创建位图索引.位图索引不应当用在频繁发生insert update delete操作上.
创建位图索引语法:
Create bitmap index 索引名
On 表名(低基数列的列名)

8. 索引组织表
索引组织表的数据存储在与其关联的索引中,索引中存储的是行的实际数据而不是ROWID,对表的数据进行插入,更新或删除,只会导致对索引的更新.
创建索引组织表的语法:
Create table 表名
(
ID number(4) primary key,
列名 数据类型
)organization index
普通表与索引表的比较
普通表 索引表
ROWID唯一的标识行 主键唯一的标识行
隐式的ROWID列 没有隐式的ROWID列
基于ROWID访问 基于主键访问
顺序扫描返回所有的行 完全索引扫描返回所有的行,并按主键顺序排列
支持分区 不支持分区

9. 获取索引信息
USER_INDEXS 查询此表可获取有关当前用户已创建的索引的详细信息
USER_IND_PARTITIONS 查询此表可获取有关用户已创建的分区索引的详细信息
USER_IND_COLUMNS 查询此表可获取有关列(用记的索引是基于这些列创建的)的详细信息
例如:
Select INDEX_NAME(索引名),TABLE_NAME(与索引相关的表名),COLUMN_NAME(建有索引的某一列) from USER_IND_COLUMNS

10. 基于函数的索引
有时可能要在某一列上常用到某一函数,可以通过建立函数索引来提高查询效率,但不能在聚合函数上建立索引.不能在LOB类型蝗列,REF类型的列或包含LOB或REF对象类型上创建函数索引.
创建函数索引语法:
Create index 索引名 on 表名 (函数名(参数));
创建表达示索引语法:
Create index 索引名 on 表名 (表达示);