Oracle中的索引讲解

一、B-树索引

索引一般是B-Tree索引，在索引结构中存储着键值和键值的RowID，并且是一一对应的。

create [unique] index studentin student(id [desc]);

选项择索引字段的原则:

（ORACLE在UNIQUE和主键字段上自动建立索引 。）

  • 在WHERE子句中最频繁使用的字段 。
  • 联接语句中的连接字段。
  • 选择高选择性的字段(即如果很少的字段拥有相同值,即有很多独特值,可以快速查找到所需数据的字段) .
  • 在联机事务处理(OLTP)环境下,所由并发性非常高,索引经常被修改,可以建B-TREE索引，不应该建位图索引 。
  • 不要在经常被修改的字段上建索引，可建函数索引。
  • 不要在有用到函数的字段上建索引。
  • B-Tree索引不包含null的数据。 
    可以建立一个“伪”复合索引解决。eg:

    create index my_ix on my_table(my_col,0);

    也可以用函数索引将不想索引，即使不是null也可以剔除。eg:

    create index a on table(decode(status,0,0))--只关心少数status为0的行。

二、复合索引

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。例如，以下语句创建一个具有两列的复合索引：(复合索引列数量不超过32个)

CREATE INDEX name  ON employee (emp_lname, emp_fname)

复合索引的第一列称为前导列(leading column)。

复合索引字段排序的原则:

  • WHERE子句中使用到的字段需要是复合索引的前导字段，若仅对后面的任意列执行搜索时，则应该创建另一个仅包含第二列的索引。
  • 如果某个字段在WHERE子句中最频繁使用,则在建立复合索引时,考虑把这个字段排在第一位(在CREATE INDEX语句中)
  • 如果所有的字段在WHERE子句中使用频率相同,则将低选择性列排在最前面,将选择性较强的列排在最后面
  • 如果所有的字段在WHERE子句中使用频率相同,如果数据在物理上是按某一个字段排序的,则考虑将这个字段放在复合索引的第一位 。
  • 在主键索引(复合主键)中列的顺序被强制为与列在表定义中出现的顺序相同，这与 PRIMARY KEY 约束中指定的列顺序无关.
  • 索引列的排序方式必须与 ORDER BY 子句完全相同或完全相反。否则不能得到性能优化。
CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC)

在这种情况下，以下查询可以得到优化：

SELECT col1, col2, col3 from table1 ORDER BY col1 ASC, col2 DESC, col3 ASC

和

SELECT col1, col2, col3 from example ORDER BY col1 DESC, col2 ASC, col3 DESC
  • 复合索引的前导字段is (not) null 可以使用索引。

三、位图索引

创建语法是在普通索引创建的语法中index前加关键字bitmap即可,例如:

create bitmap index t_ix_执行人 on t(执行人);

位图索引主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等)。

索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码。

位图索引存储数据的方式相对于B-Tree索引,占用的空间非常小,创建时不需要排序，定位存储，创建和使用非常快。

位图索引的特点：

  • Bitmap索引允许键值为空，对位图索引列进行is（not） null查询时,则可以使用索引。
  • Bitmap索引对表记录的高效访问。当使用count(XX),可以直接访问索引就快速得出统计数据。当根据位图索引的列进行and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算。
  • Bitmap索引对批量DML操作只需进行一次索引。
  • 位图索引由于用位图反映数据,不同会话更新相同键值的同一位图段,insert、update、delete相互操作都会发锁定（未提交时）。
  • 由于并发DML操作锁定的是整个位图段的大量数据行，所以位图索引主要是用于联机分析处理(OLAP)应用。

四、函数索引

比如执行如下一条SQL语句：

select * from emp where upper(ename) = 'KING'，

即使在ename上建立了索引，还是会全表扫描emp表，将里面的ename字段改成大写跟常量KING进行比较。 
如果我们建立一个基于函数的索引，比如：

create index emp_upper_idx on emp(upper(ename));

这个时候，我们只需要按区间扫描小部分数据，然后获取rowid取访问表中的数据，这个速度是比较快的。

函数索引的特点：

  • 基于函数的索引，类似于普通的索引，只是普通的索引是建立在列上，而它是建立在函数上。当然这回对插入数据有一定影响，因为需要通过函数计算一下，然后生成索引。但是插入数据一般都是少量插入，而查询数据一般数据量比较大。为了优化查询速度，稍微降低点插入速度是可以承担的。
  • 函数索引还有一个功能，只对部分行建立索引。 
    假设有一个很大的表，有一列叫做FLAG，只可能取Y和N。假设大部分数据是Y，小部分数据是N，我们需要将N修改成Y。

如果建立一个普通索引，这个索引会非常大，而且将N修改成Y的时候，维护这个索引开销会很大。

如果建立一个位图索引，但这是一个事务系统（OLTP），可能有很多人同时插入记录，或者进行修改。那么位图索引也不适合。所以，如果我们只是在值为N的行上建立索引，就比较好办了。 
只在值为N的行建立索引：

create index flag_index on big_table(case flag when 'N' then 'N' end);

这样一个索引大小会大大降低，而且维护成本也会很低的。前提是我们只对值为N的行感兴趣。

五、维护索引

  • 字典表：all_indexes、user_indexes
  • 重建索引：alter index dep_idx rebuild [online]
  • 删除索引：drop index dep_idx;

到此这篇关于Oracle索引的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持我们。

赞 (0)

相关推荐

  • oracle索引介绍(图文详解)

    oracle索引介绍(图文详解)

    对于数据库来说,索引是一个必选项,但对于现在的各种大型数据库来说,索引可以大大提高数据库的性能,以至于它变成了数据库不可缺少的一部分. 索引分类:逻辑分类single column or concatenated  对一列或多列建所引unique or nonunique 唯一的和非唯一的所引,也就是对某一列或几列的键值(key)是否是唯一的.Function-based  基于某些函数索引,当执行某些函数时需要对其进行计算,可以将某些函数的计算结果事先保存并加以索引,提高效率. Doman 

  • Oracle 如何创建和使用全文索引

    不使用Oracle text功能,也有很多方法可以在Oracle数据库中搜索文本.可以使用标准的INSTR函数和LIKE操作符实现. SELECT *FROM mytext WHERE INSTR (thetext, 'Oracle') > 0; SELECT * FROM mytext WHERE thetext LIKE '%Oracle%'; 有很多时候,使用instr和like是很理想的, 特别是搜索仅跨越很小的表的时候.然而通过这些文本定位的方法将导致全表扫描,对资源来说消耗比较昂贵,

  • oracle 索引的相关介绍（创建、简介、技巧、怎样查看） .

    一.索引简介 1.索引相当于目录 2.索引是通过一组排序后的索引键来取代默认的全表扫描检索方式,从而提高检索效率. 3.索引的创建要适度,多了会影响增删改的效率,少了会影响查询的效率,索引最好创建在取值分散的列上,避免对同一张表创建过多的索引 4.索引的使用对用户来说是透明的,由系统来决定什么时候使用索引. 5.Oracle支持多种类型的索引,可以按列的多少.索引值是否唯一和索引数据的组织形式对索引进行分类,以满足各种表和查询条件的要求.(请见附件) a. 单列索引和复合索引 b．B树索引(cr

  • Oracle使用强制索引的方法与注意事项

    Oracle使用强制索引的方法与注意事项

    Oracle使用强制索引 在一些场景下,可能ORACLE不会自动走索引,这时候,如果对业务清晰,可以尝试使用强制索引,测试查询语句的性能. 以EMP表为例: 先在EMP表中建立唯一索引,如图. 普通搜索: SELECT * FROM EMP T 查看执行计划: 可以看到,是走的全表扫描. 使用强制索引,在SELECT 后面加上/*.......*/ 中间加上索引的属性,代码如下: SELECT /*+index(t pk_emp)*/* FROM EMP T --强制索引,/*.....*/第一

  • Oracle数据库索引的维护

    正在看的ORACLE教程是:Oracle数据库索引的维护. 本文只讨论Oracle中最常见的索引,即是B-tree索引.本文中涉及的数据库版本是Oracle8i. 一. 查看系统表中的用户索引 在Oracle中,SYSTEM表是安装数据库时自动建立的,它包含数据库的全部数据字典,存储过程.包.函数和触发器的定义以及系统回滚段. 一般来说,应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象.因为这样会带来数据库维护和管理的很多问题.一旦SYSTEM表损坏了,只能重新生成数据库.我们可以用下

  • oracle索引总结

    目录 一.简介 二.索引原理 三.索引使用(创建.修改.删除.查看) 1.创建索引语法 2.修改索引 3.重建索引 3.删除索引 4.查看索引 四.索引分类 1. B树索引 2. 位图索引 3.单列索引和复合索引(基于多个列创建) 4. 函数索引 五.索引建立原则总结 一.简介 说明: 索引是数据库对象之一,用于加快数据的检索,类似于书籍的索引.在数据库中索引可以减少数据库程序查询结果时需要读取的数据量,类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息. 索引是建立在表上的可选对象

  • 深入oracle分区索引的详解

    表可以按range.hash.list分区,表分区后,其上的索引和普通表上的索引有所不同,oracle对于分区表上的索引分为2类,即局部索引和全局索引,下面分别对这2种索引的特点和局限性做个总结.局部索引local index1.局部索引一定是分区索引,分区键等同于表的分区键,分区数等同于表的分区数,一句话,局部索引的分区机制和表的分区机制一样.2.如果局部索引的索引列以分区键开头,则称为前缀局部索引.3.如果局部索引的列不是以分区键开头,或者不包含分区键列,则称为非前缀索引.4.局部索引只能依

  • Oracle数据库中建立索引的基本方法讲解

    怎样建立最佳索引? 1.明确地创建索引 create index index_name on table_name(field_name) tablespace tablespace_name pctfree 5 initrans 2 maxtrans 255 storage ( minextents 1 maxextents 16382 pctincrease 0 ); 2.创建基于函数的索引 常用与UPPER.LOWER.TO_CHAR(date)等函数分类上,例: create index

  • Oracle中的索引讲解

    一.B-树索引 索引一般是B-Tree索引,在索引结构中存储着键值和键值的RowID,并且是一一对应的. create [unique] index studentin student(id [desc]); 选项择索引字段的原则: (ORACLE在UNIQUE和主键字段上自动建立索引 .) 在WHERE子句中最频繁使用的字段 . 联接语句中的连接字段. 选择高选择性的字段(即如果很少的字段拥有相同值,即有很多独特值,可以快速查找到所需数据的字段) . 在联机事务处理(OLTP)环境下,所由并发

  • 索引在Oracle中的应用深入分析

    索引是提高数据查询最有效的方法,也是最难全面掌握的技术,因为正确的索引可能使效率提高10000倍,而无效的索引可能是浪费了数据库空间,甚至大大降低查询性能. 一.索引的管理成本1. 存储索引的磁盘空间2. 执行数据修改操作(INSERT.UPDATE.DELETE)产生的索引维护3. 在数据处理时所需额外的回退空间. 二.实际数据修改测试:一个表有字段A.B.C,同时进行插入10000行记录测试在没有建索引时平均完成时间是2.9秒在对A字段建索引后平均完成时间是6.7秒在对A字段和B字段建索引后

  • Oracle中的SUM用法讲解

    Oracle中的SUM条件查询 1.按照区域编码分组查询区域编码.IPTV_NBR不为空的数量.ACC_NBR不为空的数量.所有用户数量 SELECT AREA_CODE, SUM ( CASE WHEN IPTV_NBR IS NULL or IPTV_NBR = '' THEN 0 ELSE 1 END ), SUM ( CASE WHEN ACC_NBR IS NULL or ACC_NBR = '' THEN 0 ELSE 1 END ), COUNT (*) FROM GAT_SQMS

  • Oracle中sql语句(+)符号代表连接的使用讲解

    oracle中sql语句(+)符号代表连接 (+)在=前边为右连接 (+)在=后边为左连接 SELECT a.*, b.* from a(+) = b就是一个右连接,等同于select a.*, b.* from a right join b SELECT a.*, b.* from a = b(+)就是一个左连接,等同于select a.*, b.* from a left join b 内连接 常用的连接运算符=.<.> 使用比较运算符根据每个表共有的列的值匹配两个表中的行 外连接 左连接

  • Oracle中ROW_NUMBER()OVER()函数用法实例讲解

    目录 1. 说明: 2. 原理: 3.语法: 4.示例一: 5. 示例二 总结 Oracle中ROW_NUMBER() OVER()函数用法 1. 说明: ROW_NUMBER() OVER() 函数的作用:分组排序 2. 原理: row_number() over() 函数,over() 里的分组以及排序的执行晚于 where.group by.order by 的执行. 3.语法: row_number() over( partition by 分组列 order by 排序列 desc )

  • 解读Oracle中代替like进行模糊查询的方法instr(更高效)

    解读Oracle中代替like进行模糊查询的方法instr(更高效)

    目录 一.简介 二.使用说明 对应参数描述 我们以一些示例讲解使用方法 三.instr()与like比较 instr函数也有三种情况 下面通过一个示例说明like 与 instr()的使用比较 四.效率对比 五.总结 一.简介 相信大家都使用过like进行模糊匹配查询,在oracle中,instr()方法可以用来代替like进行模糊查询,大数据量的时候效率更高. 本文将对instr()的基本使用方法进行详解以及通过示例讲解与like的效率对比. 二.使用说明 instr(sourceString

  • 简述Oracle中in和exists的不同

    一直以来,大家认为exists比in速度快,其实是不准确的.且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询. 如果两张表大小差不多,那么exists和in的效率差不多. 例如: 一张大表为A,一张小表B 一.第一种情况 select * from A where mm in (select mm from B) 效率高,这里用到的是大表A上的索引 select * from B exists (sel

  • Oracle建立二进制文件索引的方法

    正在看的ORACLE教程是:Oracle建立二进制文件索引的方法.Oracle text是Oracle的全文检索技术,是9i版本标准版和企业版的一部分.Oracle text使用标准的sql语言索引.查找.分析存储在oracle数据库.文件或者网络里的文本及文档. Oracle text能进行关于文档的语言分析,使用多种方法查找文档,包括关键字.上下文查询.逻辑操作.模式匹配.混合主题查询.HTML/XML段落查找等方法.Oracle text在包含文本和结构化的关系属性的混合查询方面具有优越性

  • oracle中110个常用函数介绍

    1. ASCII 返回与指定的字符对应的十进制数; SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2. CHR 给出整数,返回对应的字符; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C --

随机推荐