索引的概念
索引是一个可选的结构、与簇表和表相关联,可以提高读取数据的速度,可以在表的一列或多列上创建索引,索引直接指向包含所查询值的行的位置,减少磁盘I/O,与索引的表时相互独立的物理结构,Oracle自动使用并维护索引,插入、删除、更新表后,自动更新。如果堆表没有索引,那么数据库必须执行全表扫描来找到一个值
索引的创建语法
CREATE UNIUQE | BITMAP INDEX .
ON .
(|expression>ASC|DESC,
|expression>ASC|DESC,...)
TABLESPACE
STORAGE
LOGGING|NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS|COMPRESS
NOSORT|REVERSE
PARTITION|GLOBAL PARTITION
相关说明
1)UNIQUE|BITMAP:指定UNIQUE为唯一索引,BITMAP为位图索引,省略为B-Tree索引。
2)>|expression>ASC|DESC:可以对多列进行联合索引,当为expression时即“基于函数的索引”
3)TABLESPACE:指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4)STORAGE:设置表空间的存储参数
5)LOGGING | NOLOGGING:是否对索引产生重做日志 (对大表尽量使用NOLOGGING来减少占用空间并提高效率 )
6)COMPUTE STATISTICS:创建新索引时收集统计信息
7)NOCOMPRESS|COMPRESS:是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8)NOSORT|REVERSE:NOSORT表示与表中相同的顺序创建索引,REVERSE表示相反顺序存储索引值
9)PARTITION|NOPARTITION:可以在分区表和未分区表上对创建索引进行分区
索引的特点:
1)通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性
2)可以大大加快数据的检索速度,
3)可以加速表和表之间的连接,特别是在实现数据的参考完整性
4)使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间
5)通过使用索引,在查询过程中,使用优化隐藏器,提高系统的性能。
索引不足
1)创建和维护索引要耗费时间,这种时间随着数据量的增加而增加
2)索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,需要的空间更大
3)当对表中的数据进行增加、删除、修改的时候,索引要动态维护,降低了数据的维护速度。
建立索引列的特点
1)经常需要搜索的列
2)作为主键的列,强制该列的唯一性和组织表中数据的排列结构
3)经常用在连接的列上,加速连接
4)经常根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的
5)经常需啊哟排序的列上创建索引,因为索引已经排序,查询可以利用索引的排序,加快排序查询时间
6)经常使用在WHERE子句中的列上创建索引,加快条件判断速度。
不应该创建索引列的特点
1)在查询中很少使用或者参考的列不应该创建索引
2)只有很少数据值的列
3)对于定义为blob数据类型的列不应该增加索引
4)修改性能远远大于检索性能时
限制索引
1)使用不等于操作符(<>,!=)
2)使用IS NULL 或 IS NOT NULL 如果索引列在某些行中存在NULL值,就不会使用这个索引(不包括位图索引)
3)使用函数,如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在的索引的列使用函数时,会使优化器忽略掉这些索引。
4)比较不匹配的数据类型
例:abc_number是一个varchar2类型,在abc_number字段上有索引。
下面语句将执行全表扫描:
select a,b,c,d,e from A where abc_number=123456;
Oracle会自动把where子句变成to_number(abc_number)=123456
限制了索引的使用,改为以下查询可以使用索引:
select a,b,c,d,e from A where abc_number='123456';
不匹配的数据类型之间比较会让Oracle自动限制索引的使用
查询索引:DBA_INDEXES, USER_INDEXES, USER_IND_COLUMNS
Oracle rowid
Oracle通过每个行的rowid,Oracle索引提供了访问单行数据的能力,ROWID直接指向单独行的线路图
SQL> select rowid from dual;
ROWID
------------------
AAAAEC AAB AAAAgi AAA
rowid=对象编号(6)+文件编号(3)+块编号(6)+ 行编号(3)=18位
ROWID的格式如下:
对象编号 文件编号 块编号 行编号
AAAAEC AAB AAAAgi AAA
索引分类
B树索引(默认类型)
位图索引
HASH索引
索引组织表索引
反转键(reverse key)索引
基于函数的索引
分区索引
位图连接索引
B树索引(默认类型)
B树索引在 Oracle中是一个通用索引。 在创建时它就默认的类型。B树索 引可以是一个列的(简单)索引,也可以是组合/复合(多个列)索引。 B树索引最多可以包括 32列
B树索引包括树枝块和树叶块
树枝块:包含链中下一个块的ID号
树叶块:包含了索引值、ROWID、以及指向前一个和后一个树叶块的指针。
下图为B树索引的结构图,


B-tree特点:
适合与大陆的增、删、改(OLTP)
不能用包含OR操作符的查询
适合高基数的列(唯一值多)
典型的树状结构
每个节点都是数据块
位图索引
位图索引适用于离散度较低的列,它的叶块中存放key, start rowid-end rowid,并应用一个函数把位图中相应key值置1,位图索引在逻辑or时效率最高。

例:
SQL>create bitmap index job_bitmap on emp1(job);
值/行 1 2 3 4 5 6 7 8 9 10 11 12 13 14
---------------------------------------------------------------
ANALYST 0 0 0 0 0 0 0 1 0 0 0 0 1 0
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER 0 0 0 1 0 1 1 0 0 0 0 0 0 0
PRESIDENT 0 0 0 0 0 0 0 0 1 0 0 0 0 0
SALESMAN 0 1 1 0 1 0 0 0 0 0 0 0 0 0
SQL>select count(*) from emp1 where job = 'CLERK' or job = 'MANAGER';
值/行 1 2 3 4 5 6 7 8 9 10 11 12 13 14
-----------------------------------------------------------------------------
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER 0 0 0 1 0 1 1 0 0 0 0 0 0 0
-----------------------------------------------------------------------------
or的结果 1 0 0 1 0 1 1 0 0 0 1 1 0 1
常用的B树索引类型:
唯一或非唯一索引(Unique or non_unique): 唯一索引指键值不重复。
SQL> create unique index empno_idx on emp1(empno);
或
SQL> create index empno_idx on emp1(empno);
组合索引(Composite):绑定了两个或更多列的索引。
SQL> create index job_deptno_idx on emp1(job,deptno);
反向键索引(Reverse):将字节倒置后组织键值。当使用序列产生主键索引时,可以防止叶节点出现热块现象(考点)。
SQL> create index mgr_idx on emp1(mgr) reverse;
函数索引(Function base):以索引列值的函数值为键值去组织索引
SQL> create index fun_idx on emp1(lower(ename));
压缩(Compress):重复键值只存储一次,就是说重复的键值在叶块中就存一次,后跟所有与之匹配的rowid字符串。
SQL> create index comp_idx on emp1(sal) compress;
升序或降序(Ascending or descending):叶节点中的键值排列默认是升序的。
SQL> create index deptno_job_idx on emp1(deptno desc, job asc);
可以更改索引属性:
alter index xxx ....
索引相关的数据字典
USER_INDEXES //索引主要信息
USER_IND_CULUMNS //索引列的信息
索引是一个可选的结构、与簇表和表相关联,可以提高读取数据的速度,可以在表的一列或多列上创建索引,索引直接指向包含所查询值的行的位置,减少磁盘I/O,与索引的表时相互独立的物理结构,Oracle自动使用并维护索引,插入、删除、更新表后,自动更新。如果堆表没有索引,那么数据库必须执行全表扫描来找到一个值
索引的创建语法
CREATE UNIUQE | BITMAP INDEX .
ON .
(|expression>ASC|DESC,
|expression>ASC|DESC,...)
TABLESPACE
STORAGE
LOGGING|NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS|COMPRESS
NOSORT|REVERSE
PARTITION|GLOBAL PARTITION
相关说明
1)UNIQUE|BITMAP:指定UNIQUE为唯一索引,BITMAP为位图索引,省略为B-Tree索引。
2)>|expression>ASC|DESC:可以对多列进行联合索引,当为expression时即“基于函数的索引”
3)TABLESPACE:指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4)STORAGE:设置表空间的存储参数
5)LOGGING | NOLOGGING:是否对索引产生重做日志 (对大表尽量使用NOLOGGING来减少占用空间并提高效率 )
6)COMPUTE STATISTICS:创建新索引时收集统计信息
7)NOCOMPRESS|COMPRESS:是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8)NOSORT|REVERSE:NOSORT表示与表中相同的顺序创建索引,REVERSE表示相反顺序存储索引值
9)PARTITION|NOPARTITION:可以在分区表和未分区表上对创建索引进行分区
索引的特点:
1)通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性
2)可以大大加快数据的检索速度,
3)可以加速表和表之间的连接,特别是在实现数据的参考完整性
4)使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间
5)通过使用索引,在查询过程中,使用优化隐藏器,提高系统的性能。
索引不足
1)创建和维护索引要耗费时间,这种时间随着数据量的增加而增加
2)索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,需要的空间更大
3)当对表中的数据进行增加、删除、修改的时候,索引要动态维护,降低了数据的维护速度。
建立索引列的特点
1)经常需要搜索的列
2)作为主键的列,强制该列的唯一性和组织表中数据的排列结构
3)经常用在连接的列上,加速连接
4)经常根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的
5)经常需啊哟排序的列上创建索引,因为索引已经排序,查询可以利用索引的排序,加快排序查询时间
6)经常使用在WHERE子句中的列上创建索引,加快条件判断速度。
不应该创建索引列的特点
1)在查询中很少使用或者参考的列不应该创建索引
2)只有很少数据值的列
3)对于定义为blob数据类型的列不应该增加索引
4)修改性能远远大于检索性能时
限制索引
1)使用不等于操作符(<>,!=)
2)使用IS NULL 或 IS NOT NULL 如果索引列在某些行中存在NULL值,就不会使用这个索引(不包括位图索引)
3)使用函数,如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在的索引的列使用函数时,会使优化器忽略掉这些索引。
4)比较不匹配的数据类型
例:abc_number是一个varchar2类型,在abc_number字段上有索引。
下面语句将执行全表扫描:
select a,b,c,d,e from A where abc_number=123456;
Oracle会自动把where子句变成to_number(abc_number)=123456
限制了索引的使用,改为以下查询可以使用索引:
select a,b,c,d,e from A where abc_number='123456';
不匹配的数据类型之间比较会让Oracle自动限制索引的使用
查询索引:DBA_INDEXES, USER_INDEXES, USER_IND_COLUMNS
Oracle rowid
Oracle通过每个行的rowid,Oracle索引提供了访问单行数据的能力,ROWID直接指向单独行的线路图
SQL> select rowid from dual;
ROWID
------------------
AAAAEC AAB AAAAgi AAA
rowid=对象编号(6)+文件编号(3)+块编号(6)+ 行编号(3)=18位
ROWID的格式如下:
对象编号 文件编号 块编号 行编号
AAAAEC AAB AAAAgi AAA
索引分类
B树索引(默认类型)
位图索引
HASH索引
索引组织表索引
反转键(reverse key)索引
基于函数的索引
分区索引
位图连接索引
B树索引(默认类型)
B树索引在 Oracle中是一个通用索引。 在创建时它就默认的类型。B树索 引可以是一个列的(简单)索引,也可以是组合/复合(多个列)索引。 B树索引最多可以包括 32列
B树索引包括树枝块和树叶块
树枝块:包含链中下一个块的ID号
树叶块:包含了索引值、ROWID、以及指向前一个和后一个树叶块的指针。
下图为B树索引的结构图,


B-tree特点:
适合与大陆的增、删、改(OLTP)
不能用包含OR操作符的查询
适合高基数的列(唯一值多)
典型的树状结构
每个节点都是数据块
位图索引
位图索引适用于离散度较低的列,它的叶块中存放key, start rowid-end rowid,并应用一个函数把位图中相应key值置1,位图索引在逻辑or时效率最高。

例:
SQL>create bitmap index job_bitmap on emp1(job);
值/行 1 2 3 4 5 6 7 8 9 10 11 12 13 14
---------------------------------------------------------------
ANALYST 0 0 0 0 0 0 0 1 0 0 0 0 1 0
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER 0 0 0 1 0 1 1 0 0 0 0 0 0 0
PRESIDENT 0 0 0 0 0 0 0 0 1 0 0 0 0 0
SALESMAN 0 1 1 0 1 0 0 0 0 0 0 0 0 0
SQL>select count(*) from emp1 where job = 'CLERK' or job = 'MANAGER';
值/行 1 2 3 4 5 6 7 8 9 10 11 12 13 14
-----------------------------------------------------------------------------
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER 0 0 0 1 0 1 1 0 0 0 0 0 0 0
-----------------------------------------------------------------------------
or的结果 1 0 0 1 0 1 1 0 0 0 1 1 0 1
常用的B树索引类型:
唯一或非唯一索引(Unique or non_unique): 唯一索引指键值不重复。
SQL> create unique index empno_idx on emp1(empno);
或
SQL> create index empno_idx on emp1(empno);
组合索引(Composite):绑定了两个或更多列的索引。
SQL> create index job_deptno_idx on emp1(job,deptno);
反向键索引(Reverse):将字节倒置后组织键值。当使用序列产生主键索引时,可以防止叶节点出现热块现象(考点)。
SQL> create index mgr_idx on emp1(mgr) reverse;
函数索引(Function base):以索引列值的函数值为键值去组织索引
SQL> create index fun_idx on emp1(lower(ename));
压缩(Compress):重复键值只存储一次,就是说重复的键值在叶块中就存一次,后跟所有与之匹配的rowid字符串。
SQL> create index comp_idx on emp1(sal) compress;
升序或降序(Ascending or descending):叶节点中的键值排列默认是升序的。
SQL> create index deptno_job_idx on emp1(deptno desc, job asc);
可以更改索引属性:
alter index xxx ....
索引相关的数据字典
USER_INDEXES //索引主要信息
USER_IND_CULUMNS //索引列的信息
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28282660/viewspace-1385724/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28282660/viewspace-1385724/