精华内容
下载资源
问答
  • oracle的索引有多种类型。我分类介绍下几种常用的索引 B 树索引(B-Tree索引) B树索引是我们在oracle数据库中最常用的索引,在详细介绍访问方法之前,我们看一下B-TREE索引的结构(图片来源网络) oracle的B...

    B 树索引(B-Tree索引

    B树索引是我们在oracle数据库中最常用的索引,在详细介绍访问方法之前,我们看一下B-TREE索引的结构(图片来源网络)



             oracle的B树索引就好像一颗长到的树,他包含两种类型,一种是索引分支块(根节点块,分支节点块)一种是索引叶子块(叶子节点块)。分节点用来搜索,叶子节点用来存储数据。根节点存储索引的低层分支节点的数据。 由于所有的叶子节点均会自动的存储成相同的深度,所以称为“平 衡树索引”, 故此,从任何叶子处检索数据消耗的时间都是相同的

           对于分支节点块(包括根节点块)来说,其所包含的索引条目都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)。每个索引条目(也可以叫做每条记录)都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值;第二个字段为四个字节,表示所链接的索引块的地址,该地址指向下面一个索引块。 比如从上图一可以看到,对于根节点块来说,包含三条记录,分别为(0 B1)、(500 B2)、(1000 B3),它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。在一个分支节点块中 所能容纳的记录 行数由数据块大小以及索引键值的长度决定

           对于叶子节点块来说,其所包含的索引条目与分支节点一样,都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)。每个索引条目(也可以叫做每条记录)也具有两个字段。第一个字段表示索引的键值,对于单列索引来说是一个值;而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID,该ROWID是记录行在表里的物理地址。

           当用户创建索引时,Oracle 取得所有被索引列的数据并进行排序,之后将排序后索引值和与此值相对应的 rowid 按照从下到上的顺序加载到索引中。例如,以下语句:
    CREATE INDEX employees_last_name ON employees(last_name); 
           Oracle 先将 employees 表按 last_name 列排序,再将排序后的 列及相应的 rowid 按从下到上的顺序加载到索引中。使用此索引时,Oracle 可以快速地搜索已排序的 last_name 值,并使用相应的 rowid 去定位包含用户所查找的 last_name 值的数据行。
    在一个平衡树索引中,最底层的索引块(叶块)存储了被索引的数据值,以及对应的 rowid。叶块之间以双向链表的形式相互连接。位于叶块之上分支块中包含了
    指向下层索引块的指针。

           接下来介绍一个索引查询的流程,从上往下,第一层为根节点,第二层为分支节点,第三层为叶子节点(包含了列值和rowid)。比如我们的条件为where=29,(补充说明如果被索引的列存储的是字符数据,那么索引值为这些字符数据在当前数据库字符集中的二进制值)就从跟节点开始查询,29在0-500中,指向分支节点最左边第一个分支节点块(也就是B1),就B1中去找,发现29在0-200中,指向叶子节点的L1,于是在L1中找到29的值和响应的rowid。如果只查找索引列的值,就不用根据rowid去表中查找了,如果还要查找值29这行的其他列的值就得根据rowid去表里查查询(这个过程叫做回表查询)。叶子节点还有个双向链表(如图)。在通过索引进行范围扫描时会起作用,比如要查找值29-700,如果当查找到值29的时候,不就会再从跟节点开始查找其他的值,而是根据本叶子节点链表的指向去查找其他的值。


    参考文章http://blog.csdn.net/zhifeiyu2008/article/details/8309889

    展开全文
  • oracle的索引使用注意

    2011-08-31 09:13:29
    oracle的索引陷阱 一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。 oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了...
     
    

    一,

    oracle的索引陷阱
    一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。
    oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引,oracle还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差。
    下面是一些常见的索引限制问题。
     
    1、使用不等于操作符(<>, !=)
    下面这种情况,即使在列dept_id有一个索引,查询语句仍然执行一次全表扫描
    select * from dept where staff_num <> 1000;
    但是开发中的确需要这样的查询,难道没有解决问题的办法了吗?
    有!
    通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。
    select * from dept shere staff_num < 1000 or dept_id > 1000;
     
    2、使用 is null 或 is not null
    使用 is null 或is nuo null也会限制索引的使用,因为数据库并没有定义null值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。
    解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null)
     
    3、使用函数
    如果没有使用基于函数的索引,那么where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询就不会使用索引:
    select * from staff where trunc(birthdate) = '01-MAY-82';
    但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。
    select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);
     
    4、比较不匹配的数据类型
    比较不匹配的数据类型也是难于发现的性能问题之一。
    下面的例子中,dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。
    select * from dept where dept_id = 900198;
    这是因为oracle会自动把where子句转换成to_number(dept_id)=900198,就是3所说的情况,这样就限制了索引的使用。
    把SQL语句改为如下形式就可以使用索引
    select * from dept where dept_id = '900198';

     

     

    二,

     各种索引使用场合及建议

     

    (1)B*Tree索引。

    常规索引,多用于oltp系统,快速定位行,应建立于高cardinality列(即列的唯一值除以行数为一个很大的值,存在很少的相同值)。

     Create index indexname on tablename(columnname[columnname...])

    (2)反向索引。

    B*Tree的衍生产物,应用于特殊场合,在ops环境加序列增加的列上建立,不适合做区域扫描。

     Create index indexname on tablename(columnname[columnname...]) reverse

    (3)降序索引。

    B*Tree的衍生产物,应用于有降序排列的搜索语句中,索引中储存了降序排列的索引码,提供了快速的降序搜索。

     Create index indexname on tablename(columnname DESC[columnname...])

    (4)位图索引。

    位图方式管理的索引,适用于OLAP(在线分析)和DSS(决策处理)系统,应建立于低cardinality列,
    适合集中读取,不适合插入和修改,提供比B*Tree索引更节省的空间。

     Create BITMAP index indexname on tablename(columnname[columnname...])

    在实际应用中,如果某个字段的值需要频繁更新,那么就不适合在它上面创建位图索引。
    在位图索引中,如果你更新或插入其中一条数值为N的记录,
    那么相应表中数值为N的记录(可能成百上千条)全部被Oracle锁定,
    这就意味着其它用户不能同时更新这些数值为N的记录,其它用户必须要等第一个用户提交后,
    才能获得锁,更新或插入数据,bitmap index它主要用于决策支持系统或静态数据。

    (5)函数索引。

    B*Tree的衍生产物,应用于查询语句条件列上包含函数的情况,
    索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率。

    索引创建策略
    1.导入数据后再创建索引
    2.不需要为很小的表创建索引
    3.对于取值范围很小的字段(比如性别字段)应当建立位图索引
    4.限制表中的索引的数目
    5.为索引设置合适的PCTFREE值
    6.存储索引的表空间最好单独设定

    唯一索引和不唯一索引都只是针对B树索引而言.
    Oracle最多允许包含32个字段的复合索引
    由此估计出一个查询如果使用某个索引会需要读入的数据块块数。
    需要读入的数据块越多,则 cost 越大,Oracle 也就越有可能不选择使用 index

     

    三,

    能用唯一索引,一定用唯一索引
    能加非空,就加非空约束
    一定要统计表的信息,索引的信息,柱状图的信息。
    联合索引的顺序不同,影响索引的选择,尽量将值少的放在前面
    只有做到以上四点,数据库才会正确的选择执行计划。

    展开全文
  • Oracle的索引在数据库中的存储

    千次阅读 2012-01-05 14:30:42
    Oracle的索引是以平衡树的方式组织存储的:保存的是索引列的值,以及该行的rowid的一部分(文件号,块号,行号) 下面我们通过例子来了解一下: 1,create table test(id int,name varchar2(20)) insert into test ...
    Oracle的索引是以平衡树的方式组织存储的:保存的是索引列的值,以及该行的rowid的一部分(文件号,块号,行号)
    

    下面我们通过例子来了解一下:

    1,create table test(id int,name varchar2(20))
    insert into test values(1,'A');
    insert into test values(2,'B');

    begin
    for i in 3..2000 loop
    insert into test values(i,'t'||i);
    end loop;
    end;

    2,create  index idx_test on test(id)

    3,得到这个index的object_id:

    SQL> select object_id  from dba_objects where object_name='IDX_TEST';
     OBJECT_ID
    ----------
         72300

    4,将索引dump到trace文件中

    alter session set events 'immediate trace name treedump level 72300'

    看到结果:有两层,4个叶子节点

    branch: 0x1800014 25165844 (0: nrow: 4, level: 1)
       leaf: 0x1800017 25165847 (-1: nrow: 540 rrow: 540)
       leaf: 0x1800018 25165848 (0: nrow: 533 rrow: 533)
       leaf: 0x1800015 25165845 (1: nrow: 533 rrow: 533)
       leaf: 0x1800016 25165846 (2: nrow: 394 rrow: 394)
    ----- end tree dump
    0x1800017 :16进制的地址,25165847 10进制表示的地址,这里的地址是索引数据本身的地址。

    通过下面的转换可以看到上面这两个数据是可以相互转换的。

    select to_number('1800017','xxxxxxxxxxx') from dual = 25165847 

    select to_char('25165847','xxxxxxxxxxx') from dual = 1800017

    Oracle 中提供了dbms_utility来求的这个地址对应的文件号和块号(传入的参数是十进制的那个值).

    select dbms_utility.data_block_address_file(25165847)fno,
    dbms_utility.data_block_address_block(25165847) bkno from dual

    我得到的是6号文件23块号,

    通过查看extents的分配看dump的信息是和extents匹配的

    SQL> select file_id,block_id,blocks from dba_extents where segment_name='IDX_TEST';

       FILE_ID   BLOCK_ID     BLOCKS
    ---------- ---------- ----------
             6        17          8

    得到值6,17,8,这里的6是文件号,17是区间开始的block号,块23是在17+8这个区间的.

    5,将索引数据dump出来,dump 6号文件的23块,alter system dump datafile 6 block 23

    可以看到索引文件的内容:

    row#0[1116] flag: ------, lock: 0, len=12
    col 0; len 2; (2):  c1 02
    col 1; len 6; (6):  01 80 00 0c 00 00
    row#1[1128] flag: ------, lock: 0, len=12
    col 0; len 2; (2):  c1 03
    col 1; len 6; (6):  01 80 00 0c 00 01
    row#2[1140] flag: ----S-, lock: 2, len=12
    col 0; len 2; (2):  c1 04
    col 1; len 6; (6):  01 80 00 0c 00 02
    row#3[1152] flag: ----S-, lock: 2, len=12...........

    以前三行为例,

    row#0行号.

    col 0第一列(本例是id), len 2表示长度是2, (2)表示占了两个字节,c1 02是id的值(这里值是1的16进制表示)的存储表示.

    select dump(1,16) from dual 可以看到 = Typ=2 Len=2: c1,2 (0省略了)

    col 1是rowid,01 80 00 0c 00 00是rowid的一部分值,也是16进制的.,先要转换成2进制的,再通过各个位数代表的意义计算文件号,块号和和行号。

    01 80 00 0c 00 00先要转成2进制:

    00000001 10000000 00000000 00001100 00000000 00000000

    然后串起来之后前10位 00000001 10 表示文件号,=6

    然后是接下来的22位 000000 00000000 00001100表示块号=12

    最后面的的16位表示行号=0

    验证索引保存的rowid和从test表查数据库得到rowid的信息是否相同.

    SQL> select rowid,id,dbms_rowid.rowid_relative_fno('AAARprAAGAAAAAMAAA')fno,
      2  dbms_rowid.rowid_block_number('AAARprAAGAAAAAMAAA')bkno,
      3  dbms_rowid.rowid_row_number('AAARprAAGAAAAAMAAA')rno
      4  from test where id=1 ;

    ROWID                       ID        FNO       BKNO        RNO
    ------------------ ---------- ------ ---------- ----------  ---------- ---------- ----------
    AAANP8AAEAAAAGdAAA            1          6             12                 0

    上面的sql得到是6号文件12块0行.证明索引里保存的为rowid的一部分信息。

    由于索引里保存的是对应的记录的rowid,在table move之后rowid发生了变化,索引需要重建。

    alter table test move;alter index idx_test rebuild online;(如果不是online,会阻塞更新操作)

    在表上建立主键,unique限制,如果对应的列目前还没有索引,Oracle会自动建立一个和对应的constraint同名的索引.

    在建立组合索引的时候,最好将重复值多的列放在前面,这样以顺序靠后的列查询的时候也可以用到索引.

    展开全文
  • oracle的索引陷阱 文/Ray一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。 oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加...
     
    

    oracle的索引陷阱

     文/Ray

    一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。

    oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引,oracle还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差。

    下面是一些常见的索引限制问题。

    1、使用不等于操作符(<>, !=)

    下面这种情况,即使在列dept_id有一个索引,查询语句仍然执行一次全表扫描

    select * from dept where staff_num <> 1000;

    但是开发中的确需要这样的查询,难道没有解决问题的办法了吗?

    有!

    通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。

    select * from dept shere staff_num < 1000 or dept_id > 1000;

    2、使用 is null 或 is not null

    使用 is null 或is nuo null也会限制索引的使用,因为数据库并没有定义null值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。

    解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null)

     3、使用函数

    如果没有使用基于函数的索引,那么where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询就不会使用索引:

    select * from staff where trunc(birthdate) = '01-MAY-82';

    但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。

    select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

    4、比较不匹配的数据类型

    比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中,dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。

    select * from dept where dept_id = 900198;

    这是因为oracle会自动把where子句转换成to_number(dept_id)=900198,就是3所说的情况,这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引

    select * from dept where dept_id = '900198';

    5、使用like子句

    使用like子句查询时,数据需要把所有的记录都遍历来进行判断,索引不能发挥作用,这种情况也要尽量避免。

    展开全文
  • oracle的索引使用

    2014-03-30 18:16:20
     oracle中最常用的索引就两种:B树索引和位图索引,这里就来简单说下这两种索引的使用。 B-树索引在Oracle中是一个通用的索引,在创建索引时它就是默认的索引类型。最多可以包括32列。  位图索引Oracle为每个...
  • ORACLE的索引和约束详解数据库

    千次阅读 2013-10-24 10:24:59
    ORACLE的索引和约束详解数据库 Oracle的约束 * 如果某个约束只作用于单独的字段,即可以在字段级定义约束,也可以在表级定义约束,但如果某个约束作用于多个字段, 必须在表级定义约束 * 在定义约束时可以...
  • oracle的索引陷阱 一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。 oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引...
  • oracle的索引陷阱 文/Ray一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。 oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加...
  • (2)普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 (3)唯一索引索引列中值必须是唯一,但是允许为空值。 (4) 主键索引:是一种特殊...
  • Oracle的索引分裂和索引维护(精)

    千次阅读 2018-11-14 20:02:54
    索引不是建好了就行了?难道还需要维护?...对于开发人员来说,好像只对表操作就可以了,不用管oracle是如何做,但实际上,若想清清楚楚、明明白白了解你写接口,只有业务逻辑可不行,必须深入去知道索引...
  • Oracle的索引主要包含两类:BTree和位图索引。默认情况下大多使用Btree索引,该索引就是通常所见 唯一索引、聚簇索引等等,Btree用在OLTP,加快查询速度。位图索引是Oracle的比较引人注目的地方,其主要用在OLAP...
  • Oracle的索引类型总结

    2014-02-07 20:58:54
     Oracle数据库中最常见的索引类型是b-tree索引,也就是B-树索引,以其同名的计算科学结构命名。每当你发布基本的没有经过进 一步修改的CREATE INDEX语句时,就是在创建b-tree索引。这里不打算对b-tree索引进行更多...
  • oracle的索引优缺点分析

    千次阅读 2012-02-01 10:31:02
    以下文章主要是对Oracle数据库索引的优点与缺点介绍,如果你对Oracle数据库索引的优点与缺点想有所了解话,你可以通过以下文章对其实际应用与功能有所了解 ,以下是文章具体介绍……  以下...
  • 接触oracle索引有一段时间了,今天才听到前导索引这个名词,实在有点out了。 不过它倒是帮我解决了不小问题,很好!   具体关于前导索引的使用,在这里 ...
  • oracle的索引类型及作用

    千次阅读 2013-08-19 17:19:09
    oracle主要的索引类型有一下几种 B树索引 位图索引 索引组织表 逆序索引 哈希索引 分区索引 B树索引是关系型数据库中基本的索引结构,也是oracle数据库中最常见的索引形式 其定位值按照二叉树的方式定位...
  • 【Oracle】7.由B-Tree算法谈Oracle的索引

    千次阅读 2016-05-02 14:05:28
    B树算法很实用,但自己初次翻译,感受颇深,写不明之处,欢迎交流讨论。 Oracle索引的使用使得我们操作简便快捷,相信大家深有体会。 算法,深入思考,可以让人变得聪明哦!共勉!
  • – 利用索引扫描:快,推荐使用。也是一张表,只有两列。key-value – 共享语句:必须建立在全表扫描基础之上,已经查过数据会保留在缓存中,直接去缓存中查找。 索引 index 作用:...
  • oracle的索引原理

    千次阅读 2006-06-02 11:46:00
    [1] 基本的索引概念查询DBA_INDEXES视图可得到表中所有索引的列表,注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。...在 Oracle9i引入跳跃式扫描的索引访问方法之前,查询只能在有限条件下
  • Oracle的索引是区分大小写的 给大小写混合的字段建好索引后, 如果在执行的时候用upper或者lower将字段的值改变, 将导致不走原有的索引。 Java的Coder需要注意。 可以再给该字段建两个索引大小写,或者...
  • 从大方面来说,一般从业务上和技术上(oracle索引)来优化。 本文重点总结下oracle技术层面优化: 索引类型选择,索引选择,表之间联结类型对于实现最优性能有着很高重要性。 从算法维度索引广义可以...
  • JAVA中关于Oracle的索引

    2009-09-01 18:50:00
    在使用Oracle的过程,我们就不能不考虑性能和SQL优化,而正确的使用索引是优化SQL中的很关键的因素。如果发现Oracle在有索引的情况下而没有使用索引,这并不是Oracle的优化器出错。在有些情况下Oracle确实会选择全表...
  • Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Function-based函数索引 Domain 域索引 物理上: Partitioned 分区索引 NonPartitioned 非分区索引 B-tree: ...
  • http://www.linuxidc.com/Linux/2014-11/109301.htm
  • oracle的索引字段可以设置为空?

    千次阅读 2017-03-08 15:39:13
    答案是肯定; 可以建,就是有点不合理; 索引是告诉你有什么,并不会告诉你没有什么; 如果少数几条null无所谓,如果很多null/很多重复值建索引的意义就打折扣了
  • 如何监控oracle的索引是否使用

    千次阅读 2011-03-09 10:46:00
    因此有时候需要监控已有的索引是否在使用,oracle提供了监控索引是否使用的工具,很简单,简要介绍一下。首先,我们如果是监控一个表上的所有索引,可以这样先生成监控的命令:SQL> select 'alter index '||index_...
  • 其中建立索引,一个数据表内建立的索引内包含4个字段,另外一个表内建立的索引包含3个字段,提问哪个查询效率高?我略微思考了一下,回答是索引建有4个字段的那个数据表,但是答案是错误的,对方表示应该是索...
  • Oracle的索引 索引和对应的表应该位于不同的表空间中,oracle能够并行读取位于不同硬盘上的数据,可以避免产生I/O冲突 B树索引:在B树的叶节点中存储索引字段的值与ROWID。 唯一...
  • 一般观点认为oracle数据库使用的索引不会超过设计时创建索引总数的25%,或者不以它们被期望的使用方式使用.在实际应用中,调优速度较慢的查询时,经常发现执行的sql调用了垃圾索引,而不是我们设计时建立的索引.所以我们...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 21,099
精华内容 8,439
关键字:

oracle的索引