索引详解

索引的概念
索引是一个可选的结构、与簇表和表相关联,可以提高读取数据的速度,可以在表的一列或多列上创建索引,索引直接指向包含所查询值的行的位置,减少磁盘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/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值