精华内容
下载资源
问答
  • Oracle索引

    千次阅读 2015-07-31 07:46:08
    Oracle 索引


    索引是数据库管理系统提供的一种用来快速访问表中数据的机制,在数据库管理系统中,索引的意义非常重大,使用索引可以显著提高数据的查询效率,减少磁盘的io操作,提升整个数据库的性能。
    使用索引具有如下优点:
    1.索引可以大大加快检索数据的速度
    2.使用唯一性索引可以保证数据表中每一行数据的唯一性
    3.通过索引可以加快表与表之间的连接
    4 在使用分组和排序子句进行数据检索时,使用索引可以显著地减少查询中分组查询和排序的时候

    Oracle操作符两种形式:
    1.顺序访问方式
    2.索引访问方式

    索引原理:
    在Oracle数据表中,每一张都有一个ROWID伪列,这个ROWID是用来唯一标志一条所在物理位置的一个id号,每一行对应的ROWID值是固定而且唯一的,一量数据存入数据库就确定,不会在对数据
    库表操作的过程中发生改变,只有在表发生移动或表空间变化等操作产生物理位置变化,才会发生改变。
    注意:
    在索引段中保存排序的索引列的值及代表着物理地址的ROWID值

    创建索引:
    索引的创建方式分为如下两种。
    1.自动创建:在定义主键约束或唯一约束时,Oracle会自动在相应的约束列上建立唯一索引,Oracle不推荐人为地创建唯一性索引
    2.手动创建:用户可以在其他列上创建唯一索引。
    在Oracle中,索引根据其组织形式又可以分为多种类型,分别如下所示
    1.单列索引:索引基于单个列创建
    2.复合索引:索引基于多个列所创建
    3.B树索引:这是Oracle默认使用的索引,B树索引可以是单列索引或复合索引,唯一索引或非唯一性索引,索引按B树结构组织并存放索引数据
    4.位图索引:为索引列的每个取值创建一个位图,对表中的每行使用1位(bit,取值为0或为1)来给示该行是否包含该位图索引的取值
    5.函数索引:索引的取值不直接来自列,而是来自包含有列的函数或表达式,这就是函数索引

    create table emp_index as select * from emp;
    create index idex_emp_name on emp_index(ename,empno);--B树索引
    create index idex_emp_job on emp_index(job);--B树索引
    create bitmap index idx_emp_job_bitmap on emp_index(job);--位图索引
    create index idex_emp_name on emp(upper(ename));--函数索引
    注意:当创建复合索引时,索引的顺序决定了索引的性能,通常要将最常查询的列放在前面,不常查询的列放在后面,两个具有不同名称的复合索引列,使用了相同字段但是顺序不同是合法

    下面是创建索常见的10条原则
    1.小表不需要创建索引,比如emp表只有数十条记录,可以不建立索引
    2.对于大表而言,如果经常查询的记录数目少于表中总记录的15%,可以创建索引,这个比例并不绝对,它与全表扫描速度成反比
    3.对于大部分列值不重复的列可以建立索引
    4.对于基数大的列,适合建立B树索引,而对于基数小的列适合建立位图索引
    5.对于列中有许多空值,但经常查询所有的非空值的记录列,应该建立索引
    6.long与long ram列不能创建索引
    7.经常进行连接查询的列应该建立索引
    8.在使用credate index语句创建查询时,将最常查询的列放在其他列前面
    9.维护索引需要开销,特别对表进行插入和删除操作时,因此要限制表中索引的数量,对于主要用于读的表,索引多就有好处,但是,如果一个表经常被更改,则索引应该少点
    10.在表中插入数据后创建索引,如果在装载数据之前创建了索引,那么当插入每行时,Oracle都必须更改索引

    修改索引:
    --重命名索引
    alter index IDEX_EMP_NAME rename to idx_ename_empno;
    select * from user_indexes where index_name=upper('IDX_ENAME_EMPNO')
    合并和重建索引
    合并索引:合并索引并不改变索引的物理组织架构只是简单地将B树叶子节点中的存储碎片合并在一起
    重建索引L重新创建一个新的索引删除原来的索引

    alter index idx_ename_empno coalesce;
    合并索引使用alter index coalesce;语法,合并只是简单地将B树中叶子节点的碎片合在一起,其实并没有改变索引的物理组织架构
    合并前放在两个叶子节点中的碎片被合并到了1个叶子节点,而另一个叶子节点就被释放了

    重建索引实际上就是对原有的索引的删除,再重新建一个新的索引因为这个原因,所以在使用alter index时,可以使用各种存储参数,比如使用STORAGE指定存储参数,使用table space指定表空间或利用nologging选项避免产生重做日志信息
    --重建索引
    alter index idx_ename_empno rebuild;
    --使用存储语句更改索引的所在的表空间
    alter index idx_ename_empno rebuild tablespace users;
    上面语句在重建索引时的时候,使用tablespace选项将索引移到了users空间中,合并索引和重建索引都能消除索引碎片,但二者使用上有明显的区别
    1.合并索引不能将索引移动到其他索引空间,但重建索引可以
    2.合并索引代价较低,无须额外存储空间,但重建索引恰恰相反
    3.合并索引只能在B树的同一子树中合并,不改变树的高度,但重建索引重建整个B树,可能降低树的高度

    分配和释放索引空间
    在插入或者加载数据时,如果表具有索引,会同时在索引中添加数据,如果索引段空间不足,为了能够向索引段添加数据将导致动态地扩展索引段,从降低了数据的装载速度,为了避免这个问题,可以在执行装载或大批量插入之前为索引段分配足够的空间

    --分配索引空间
    alter index idx_ename_empno allocate extent(size 200k)
    上述语法首先装饰idx_ename_empno索引段的索引扩容200kb,以便能容纳所插入的索引数据
    当索引段占用了过多的空间,而实际上用不这样多的空间时,可以deallcate unused来释放多余的空间,
    --释放多余的索引空间
    alter index idx_ename_empno deallocate unused;
    上述语句执行后,将释放未曾使用的索引空间

    删除索引
    drop index idx_ename_empno
    对于唯一索引,如果是在列定义约束时由Oracle自动建立的,可以通过使用Disable禁用约束或删除约束的方法来删除对应的索引

    注意;在删除表时,所有基于该表的索引也会自动删除
    当以下情况发生时,需要从数据库中移除索引
    1.索引不再需要时,应该删除以释放所占用的空间
    2.索引没有经常使用时,只是极少数查询会使用到该索引时。
    3.如果索引中包含损坏的数据块,或者是索引碎片过多时,应删除该索引,然后重建索引
    4.如果表数据被移动后导致索引无效,此时应删除该索引,然后重建
    5.当使用SQL*Loader给表中装载大量数据时,系统也会给表的索引增加数据,为了加快装载速度,可以在装载之前删除索引,在装载之后重新创建索引

     


    5.3.索引
    5.3.1.索引说明
    一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
    索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
    索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引
    在删除一个表时, 所有基于该表的索引会自动被删除
    通过指针加速 Oracle 服务器的查询速度
    通过快速定位数据的方法,减少磁盘 I/O

    5.3.2.创建索引
    自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
    手动创建: 用户可以在其它列上创建非唯一的索引,以加速查询
    create Index index on table (column...)
    在表employees的列last_name上创建索引
    create index emp_last_name_idx
    on employees(lasy_name)
    index created

    5.3.3.什么时候创建索引
    以下情况可以创建索引:
    列中数据值分布范围很广
    列经常在 WHERE 子句或连接条件中出现
    表经常被访问而且数据量很大 ,访问的数据大概占数据总量的2%到4%
    5.3.4.下列情况不要创建索引:
    表很小
    列不经常作为连接条件或出现在WHERE子句中
    查询的数据大于2%到4%
    表经常更新

    5.3.5.查询索引
    可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息
    删除索引:
    drop index index;

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效:
    1如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
     注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
      2.对于多列索引,不是使用的第一部分,则不会使用索引
      3.like查询是以%开头

     4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
     5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
    此外,查看索引的使用情况
    show status like ‘Handler_read%’;
    大家可以注意:
    handler_read_key:这个值越高越好,越高表示使用索引查询到的次数
    handler_read_rnd_next:这个值越高,说明查询低效
    查询索引
    select object_name, object_type
      from user_objects
     where object_type = upper('index');
    SQL> conn as1/as1
    Connected.

    创建表:
    SQL> create table dex (id int,sex char(1),name char(10));
    Table created.

    向表中插入1000条数据
    SQL> begin
      2  for i in 1..1000
      3  loop
      4  insert into dex values(i,'M','chongshi');
      5  end loop;
      6  commit;
      7  end;
      8  /

    PL/SQL procedure successfully completed.

    查看表记录
    SQL> select * from dex;
            ID SE NAME
    ---------- -- --------------------       ... . .....
           991 M  chongshi
           992 M  chongshi
           993 M  chongshi
           994 M  chongshi
           995 M  chongshi
           996 M  chongshi
           997 M  chongshi
           998 M  chongshi
           999 M  chongshi
          1000 M  chongshi

    1000 rows selected.

    创建索引:
    SQL> create index dex_idx1 on dex(id);
    Index created.
    注:对表的第一列(id)创建索引。

    查看创建的表与索引
    SQL> select object_name,object_type from user_objects;

    OBJECT_NAME                  OBJECT_TYPE
    --------------------------------------------------------------------------------
    DEX                           TABLE
    DEX_IDX1                      INDEX
    索引分离于表,作为一个单独的个体存在,除了可以根据单个字段创建索引,也可以根据多列创建索引。Oracle要求创建索引最多不可超过32列。

     

    SQL> create index dex_index2 on dex(sex,name);
    Index created.

    SQL>  select object_name,object_type from user_objects;

    OBJECT_NAME                           OBJECT_TYPE
    --------------------------------------------------------------------------------
    DEX                                       TABLE
    DEX_IDX1                                 INDEX
    DEX_INDEX2                               INDEX
    创建位图索引:

     

    查看表记录
    SQL> select * from dex;
    ...................
            ID SEX NAME
    ---------- -- --------------------
           991 M  chongshi
           992 M  chongshi
           993 G  chongshi
           994 G  chongshi
           995 G  chongshi
           996 M  chongshi
           997 G  chongshi
           998 G  chongshi
           999 G  chongshi
          1000 M  chongshi

    1000 rows selected.

    对于上面表来说sex(性别)只有两种值,最适合用来创建位图所引
    创建索引:
    SQL> create bitmap index my_bit_idx on dex(sex);

    Index created.

    查看创建的所引
    SQL>  select object_name,object_type from user_objects;

    OBJECT_NAME                           OBJECT_TYPE
    --------------------------------------------------------------------------------
    MY_BIT_IDX                               INDEX
    创建索引的一些规则                                                  

     

    1、权衡索引个数与DML之间关系,DML也就是插入、删除数据操作。

    这里需要权衡一个问题,建立索引的目的是为了提高查询效率的,但建立的索引过多,会影响插入、删除数据的速度,因为我们修改的表数据,索引也要跟着修改。这里需要权衡我们的操作是查询多还是修改多。

    2、把索引与对应的表放在不同的表空间。

         当读取一个表时表与索引是同时进行的。如果表与索引和在一个表空间里就会产生资源竞争,放在两个表这空就可并行执行。

    3、最好使用一样大小是块。

         Oracle默认五块,读一次I/O,如果你定义6个块或10个块都需要读取两次I/O。最好是5的整数倍更能提高效率。

    4、如果一个表很大,建立索引的时间很长,因为建立索引也会产生大量的redo信息,所以在创建索引时可以设置不产生或少产生redo信息。只要表数据存在,索引失败了大不了再建,所以可以不需要产生redo信息。

     

    5、建索引的时候应该根据具体的业务SQL来创建,特别是where条件,还有where条件的顺序,尽量将过滤大范围的放在后面,因为SQL执行是从后往前的。(小李飛菜刀)

     

    索引常见操作                                                           

     

    改变索引:

    SQL> alter index employees_last _name_idx storage(next 400K maxextents 100);索引创建后,感觉不合理,也可以对其参数进行修改。详情查看相关文档

     

    调整索引的空间:

     

    新增加空间
    SQL> alter index orders_region_id_idx allocate extent (size 200K datafile '/disk6/index01.dbf');

    释放空间
    SQL> alter index oraers_id_idx deallocate unused;
    索引在使用的过程中可能会出现空间不足或空间浪费的情况,这个时候需要新增或释放空间。上面两条命令完成新增与释放操作。关于空间的新增oracle可以自动帮助,如果了解数据库的情况下手动增加可以提高性能。

     

    重新创建索引:

    所引是由oracle自动完成,当我们对数据库频繁的操作时,索引也会跟着进行修改,当我们在数据库中删除一条记录时,对应的索引中并没有把相应的索引只是做一个删除标记,但它依然占据着空间。除非一个块中所有的标记全被删除的时,整个块的空间才会被释放。这样时间久了,索引的性能就会下降。这个时候可以重新建立一个干净的索引来提高效率。

    SQL> alter index orders_region_id_idx rebuild tablespace index02;通过上面的命令就可以重现建立一个索引,oracle重建立索引的过程:

    1、锁表,锁表之后其他人就不能对表做任何操作。

    2、创建新的(干净的)临时索引。

    3、把老的索引删除掉

    4、把新的索引重新命名为老索引的名字

    5、对表进行解锁。

     

    移动所引:

    其实,我们移动索引到其它表空间也同样使用上面的命令,在指定表空间时指定不同的表空间。新的索引创建在别位置,把老的干掉,就相当于移动了。

    SQL> alter index orders_region_id_idx rebuild tablespace index03;

    在线重新创建索引:

    上面介绍,在创建索引的时候,表是被锁定,不能被使用。对于一个大表,重新创建索引所需要的时间较长,为了满足用户对表操作的需求,就产生的这种在线重新创建索引。

    SQL> alter index orders_id_idx  rebuild  online;创建过程:

    1、锁住表

    2、创建立临时的和空的索引和IOT表用来存在on-going DML。普通表存放的键值,IOT所引表直接存放的表中数据;on-gong DML也就是用户所做的一些增删改的操作。

    3、对表进行解锁

    4、从老的索引创建一个新的索引。

    5、IOT表里存放的是on-going DML信息,IOT表的内容与新创建的索引合并。

    6、锁住表

    7、再次将IOT表的内容更新到新索引中,把老的索引干掉。

    8、把新的索引重新命名为老索引的名字

    9、对表进行解锁

    如上图,在很多索引中有剩余的空间,可以通过一个命令把剩余空间整合到一起。  

    SQL> alter index orders_id_idx  coalesce;

    删除索引:

    SQL> drop  index  hr.departments_name_idx;
    可以通过一条命令来完成分析索引,分析的结果会存放在在index_stats表中。

     

    查看存放分析数据的表:
    SQL> select count(*) from index_stats;

      COUNT(*)
    ----------
             0
    执行分析索引命令:
    SQL> analyze index my_bit_idx validate structure;

    Index analyzed.

    再次查看 index_stats 已经有了一条数据
    SQL> select count(*) from index_stats;

      COUNT(*)
    ----------
             1

    把数据查询出来:
    SQL> select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

        HEIGHT   NAME              LF_ROWS   LF_BLKS   DEL_LF_ROWS
    ---------- ---------------------------------------------------------------------- ---------- -----------
             2   MY_BIT_IDX            1000          3            100 
    分析数据分析:

    (HEIGHT)这个所引高度是2 ,(NAME)索引名为MY_BIT_IDX  ,(LF_ROWS)所引表有1000行数据,(LF_BLKS)占用3个块,(DEL_LF_ROWS)删除100条记录。

      这里也验证了前面所说的一个问题,删除的100条数据只是标记为删除,因为总的数据条数依然为1000条,占用3个块,那么每个块大于333条记录,只有删除的数据大于333条记录,这时一个块被清空,总的数据条数才会减少。


     

    展开全文
  • ORACLE 索引

    2013-10-20 10:49:03
    今天整理ORACLE索引ORACLE索引有很多类型,包括有: B树索引(默认类型) 位图索引 HASH索引 索引组织表索引 反转键(reverse key)索引 基于函数的索引 分区索引(本地和全局索引) 位图连接索引 今天主要针对oracle最...

    今天整理ORACLE索引,ORACLE索引有很多类型,包括有:

    B树索引(默认类型)

    位图索引
    HASH索引
    索引组织表索引
    反转键(reverse key)索引
    基于函数的索引
    分区索引(本地和全局索引)

    位图连接索引

    今天主要针对oracle最常见、应用最广泛B树索引(默认类型)、位图索引进行整理,直接进入正文:

    首先先了解创建索引的语法:

    CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>
        ON <schema>.<table_name>
             (<column_name> | <expression> ASC | DESC,
              <column_name> | <expression> ASC | DESC,...)
       TABLESPACE <tablespace_name>
       STORAGE <storage_settings>
       LOGGING | NOLOGGING
       COMPUTE STATISTICS
       NOCOMPRESS | COMPRESS<nn>
       NOSORT | REVERSE
       PARTITION | GLOBAL PARTITION<partition_setting>

    UNIUQE:创建唯一索引,创建索引的列的值必须不相同的,是唯一
    BITMAP:创建位图索引,不指定就是创建B-TREE(oracle默认)索引
    <schema>.<index_name>:指定索引名称
    <schema>.<table_name>:指定索引所应用的表名
    <column_name>:为哪个列创建索引
    TABLESPACE:指定表所在的表空间
    STORAGE:可进一步设置表空间参数
    LOGGING | NOLOGGING:索引是否重做日志
    COMPUTE STATISTICS:创建新索引时收集统计信息
    NOCOMPRESS | COMPRESS:是否使用索引键压缩(键压缩可以删除键中出现的重复值,减少存储空间使用同时效率会降低)
    NOSORT:表示与表中相同的顺序创建索引,REVERSE表示相反顺序存储索引值
    PARTITION | GLOBAL PARTITION:可以对分区表或未分区表上创建的索引进行分区

    例1:为test2表创建默认B-tree索引

    CREATE INDEX t_name_index on test2 (name)  --其他项默认

    --创建B-TREE索引成功!就这么简单,那么什么是B-tree所以呢?往下看

    什么是B-tree索引?
    B-tree索引时最常见的一种类型,也是oracle默认类型。B树索引指的是一种平衡树,使用平衡算法来管理索引的。
    B树索引从结构上看是一个倒立的树状结构,它有两种类型的数据块:分支块和叶块。分支块的作用是根据条件查询定位其他分支块和叶块的位置。
    叶块是用于存储索引的列值和对应数据行的物理地址,该物理地址用rowid来表示,rowid是一个指针,用它来指向数据行的物理地址,这是oracle数据库
    访问数据行的最快方法。索引中只保存索引的列值和rowid,才是它的规模比表要小的多,所以对索引查询的速度要远高于对表的检索速度。
    B-tree索引检索原理
    举例说明:查询ID为2199的记录,那么执行 SELECT * FROM TEST WHERE ID=2199;
    如果没有索引那么要查询呢2199的记录就需要对全表扫描,现在对ID列上建立B-tree索引,使用索引查询的原理步骤为:
    1、读取最上层的分支块数据,得知id为2199记录的索引在第二层分支块B3锁定位的范围内。
    2、读取第二层分支块B3中数据,得知ID为2199记录的索引列在第二层分支块B3下面的叶块B31中存储
    3、读取叶块中B31数据,获取id为2199的记录的rowid,然后得到rowid得到该记录。
    可见索引检索数据至读取了3个数据块,要远远低于全表读取数据的数量。大大降低了I/0操作数据,从而提高了查询效率。

    如果查询是不等查询,则从第三层开始不必重新沿着向最上层分支块查找,直接沿着双向链表进行查询即可,如下图代码所示:
    SELECT * FROM TEST WHERE ID BETWEEN '2199' AND '2999';
    此时查询该记录的步骤为:
    1、读取最上层的分支块数据,得知id为2199的记录的索引列值在第二层分支块B3所在的范围内。
    2、读取第二层B3中的数据,得知ID为2199的记录索引列值在第二层分支块下面叶块B31中存储。
    3、读取叶块B31中数据,获得ID为2199的rowid, 然后根据rowid得到该记录。
    4、沿着叶块双向链表结构向后依次获取2999的记录rowid,然后根据rowid得到相应记录。

    对于索引来说,B-tree索引结构思一个十分有效的数据结构,其层次通常很短,一般不会超过三层,在一个千万数据级的表中查询一条记录,一般也只需读取
    3个数据块。

    当一条新数据插入表中,新的信息同时也被插入到叶块中;当一个叶块被写满后,被插入新的行信息叶块就会被分割为两个叶块,新的叶块定位信息就会被放入
    上层分支块中;如果分支块也被写满,那么它也会自动分割为两个分支块,新的分支块地位信息同时放入到上层分支块中,以此类推直到全部信息写入各个级别
    的分支块。在此过程中平衡算法会自动调整各个分支块所能定位的下级分支块和叶块的数量以及叶块中保存索引列值数量,以使各分支尽量达到平衡。

    虽然B-tree索引应用最为广泛,但也不适用于所有情况,适用B树索引的场合有:
    1、表中存储的数据库很多
    2、列中存储的不同数据值很多
    3、查询的数据量不超过全表的5%,否则应使用全表扫描

    用于查看索引信息user_indexes:
    select index_name,blevel+1,table_name  from user_indexes;    --blevel表示该索引有几个分支块,+1表示包含叶块数量


    例子2:为test2表创建位图索引

    CREATE BITMAP INDEX t_name_index on test2 (name)  --test2表创建位图索引
    虽然B-tree索引应用很广,但在一个列有多很多重复值时,B-TREE索引的叶块就会存储很多重复的数据,反而会影响数据查询效率。对于一个列中重复值很多时,
    采用位图索引进行检索查询效率会更高。ORACLE创建位图索引时,会为索引创建一个位图,该位图由二进制数组成。0表示该列无值,1表示该列有值。

    直接上例子:SELECT s_id,s_name,s_political,s_language from student;

    sid                    s_name                    s_political                    s_language    
    0807020225   张三             共青团员           日语
    0807020226   李四                            共青团员           英语
    0807020227   王五                            群众                    英语
    0807020228   赵六                            共青团员                     英语
    0807020229   孙琦                            共青团员           日语
    0807020230   王八                            预备党员           英语
    0807070130   王八1                        预备党员           英语
    0807070201     王八2                 党员               英语

    如以s_political、s_language为条件查询,那么位图索引二进制表示为:
    共青团员:11011000
    群众:    00100000
    预备党员:00000110
    党员:     00000001

    日语:10001000
    英语:01110111

    --以上是位图索引所表现的搜索方式,0表示该列无值,1表示该列有值。
    假设要查询政治面貌是‘共青团员’语种是‘英语’的学生学号、姓名,可执行下面的语句:
    SELECT S_ID,S_NAME,FROM STUDENT WHERE s_political='共青团员' and s_language='英语'
    共青团员:11011000
    英语:    01110111
    共青团员和英语匹配为1的。那么记录为2,4
    对于表列中重复数据很多时使用位图索引可以查询提高效率。当对表总进行增删改时会重新组织索引项,每个位图索引项都包含了表中大量数据行的rowid,因此对于频繁使用增删改操作
    表不适用于使用位图索引。使用位图所有的场合有:
    1、表中有大量数据存在
    2、表中列有重复数值不少
    3、列用于布尔运行(or,and,not);


    查看索引信息

    select  * from user_indexes

    有时创建了索引,但并没有比没索引查询速度高,这是因为索引中也存在一些自身的限制:

    查看网络连接:http://blog.chinaunix.net/uid-24612962-id-3243307.html




    展开全文
  • oracle索引

    千次阅读 2009-12-25 14:03:00
    索引的类型: B-树索引 位图索引 HASH索引 索引编排表 反转键索引 基于函数的索引 分区索引 本地和全局索引 限制索引(索引失效)容易引起oracle索引失效的原因很多:(需要具体分析。你可以根据执行计划来判断...

     索引的类型:
       B-树索引
       位图索引
       HASH索引
       索引编排表
       反转键索引
       基于函数的索引
       分区索引
       本地和全局索引

     

     

    限制索引(索引失效)

    容易引起oracle索引失效的原因很多:(需要具体分析。你可以根据执行计划来判断)

    1、在索引列上使用函数。如SUBSTR,DECODE,INSTR等,对索引列进行运算.需要建立函数索引就可以解决了。

    2、新建的表还没来得及生成统计信息,分析一下就好了

    3、基于cost的成本分析,访问的表过小,使用全表扫描的消耗小于使用索引。

    4、使用<>、not in 、not exist,对于这三种情况大多数情况下认为结果集很大,一般大于5%-15%就不走索引而走FTS。

    5、单独的>、<。

    6、like "%_" 百分号在前。

    7、单独引用复合索引里非第一位置的索引列。

    8、字符型字段为数字时在where条件里不添加引号。

    9、当变量采用的是times变量,而表的字段采用的是date变量时.或相反情况。

    10、索引失效,可以考虑重建索引,rebuild online。

    11、B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null  都会走、联合索引 is not null 只要在建立的索引列(不分先后)都会走。

     

    12.可能是你的表经常被更新,碎片太多,索引占用空间太大,优化器觉得没有必要用索引了  
      就直接全表扫描了啊,你重新建立索引,就整理了碎片了啊,当然就又用索引了

     

    限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题:
    4.1 使用不等于操作符(<>、!=)
    下面的查询即使在cust_rating列有一个索引,查询语句仍然执行一次全表扫描。
           select cust_Id,cust_name
           from   customers
           where  cust_rating <> 'aa';
    把上面的语句改成如下的查询语句,这样,在采用基于规则的优化器而不是基于代价的优化器(更智能)时,将会使用索引。
           select cust_Id,cust_name
           from   customers
           where  cust_rating < 'aa' or cust_rating > 'aa';
    特别注意:通过把不等于操作符改成OR条件,就可以使用索引,以避免全表扫描。
    2 使用IS NULL 或IS NOT NULL
    使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开     发人员在建表时,把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值,就不会使用这个索引(除非索引是一个位图索     引,关于位图索引在稍后在详细讨论)。
    3 使用函数
    如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。
    下面的查询不会使用索引(只要它不是基于函数的索引)
            select empno,ename,deptno
            from   emp
            where  trunc(hiredate)='01-MAY-81';
            把上面的语句改成下面的语句,这样就可以通过索引进行查找。
            select empno,ename,deptno
            from   emp
            where  hiredate<(to_date('01-MAY-81')+0.9999);
    4 比较不匹配的数据类型
    比较不匹配的数据类型也是比较难于发现的性能问题之一。注意下面查询的例子,account_number是一个VARCHAR2类型,在account_number字段上有索引。下面的语句将执行全表扫描。
           select bank_name,address,city,state,zip
           from   banks
           where  account_number = 990354;
    Oracle可以自动把where子句变成to_number(account_number)=990354,这样就限制了索引的使用,改成下面的查询就可以使用索引:
           select bank_name,address,city,state,zip
           from   banks
           where  account_number ='990354';
    特别注意:不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一 次“全表扫描”。

    展开全文
  • Oracle 索引

    2015-07-07 11:27:28
    Oracle 官方给了一个大约的数字,维护一个索引所需要的代价大约是操作本身的 3 倍。另外,索引也有好多种类型,不同的索引适应的场景也不同。 索引组织表(Index-Organized Tables) 创建索引时,如何

    -- Start

    不可否认,提高性能最直接有效的方式就是创建索引,正因为如此,好多人把它当做救命的良药,随意创建索引,殊不知维护索引的代价是非常大的。Oracle 官方给了一个大约的数字,维护一个索引所需要的代价大约是操作本身的 3 倍。另外,索引也有好多种类型,不同的索引适应的场景也不同。


    索引组织表(Index-Organized Tables)

    创建索引时,如何选择索引列呢?其实就是查询用到的列,包括 SELECT,WHERE,JOIN, ORDER 等用到的列,但是列的顺序是有讲究的,应该把那些重复值最少的列放在最前面。如果你创建一个索引包含一个表的所有列,那么你应该将该表创建为索引组织表(IOT),如果一个表的列比较少,这么做是可以的。对于列多的表,千万别这么干。因为索引组织表的记录存储在索引的叶子节点上,当我们向表中插入数据时,Oracle 为了维护索引需要移动数据,这会大大降低插入速度。下面是一个简单的例子。

    CREATE TABLE test
    (
      id    NUMBER(10),
      name  VARCHAR2(30),
      CONSTRAINT pk_test PRIMARY KEY (id)
    ) ORGANIZATION INDEX;


    B 树索引(B-Tree Indexes)

    B 树索引是默认的索引类型,特别适合主键,或重复值比较少的列或列的组合,如何判断重复值得多少呢?看看下面的公式吧,下面的值越高越好,主键是 1.0

    SELECT COUNT(DISTINCT COLUMN) / COUNT(*) FROM TEST;

    下面是一个简单的例子。

    --普通索引
    CREATE INDEX test_idx_name ON test (name);
    
    --唯一索引
    CREATE UNIQUE INDEX test_idx_id ON test (id);


    位图索引(Bitmap Indexes)

    位图索引和 B 树索引正好相反,非常适合重复值比较多的列,最好是只有几项,如:国籍,性别,省份等等,而且这些值基本上不会频繁更新。注意,频繁更新的列不适合位图索引,如,订单表有个列表示是否被处理,只有两个值,YES, NO。下面是一个简单的例子。

    --位图索引
    CREATE BITMAP INDEX test_idx_country ON test (country);
    CREATE BITMAP INDEX test_idx_gender ON test (gender);
    CREATE BITMAP INDEX test_idx_province ON test (province);

    基于函数的索引(Function-based Indexes)

    在索引字段上使用函数会使索引失效,有时候,我们可以通过把它转化为范围扫描来避免这个问题,但是,有时候,我们必须要使用函数,如:忽略大小写查询,这个时候,我们可以在创建基于函数的索引,如下是一个简单的例子。
    CREATE INDEX test_idx ON test(UPPER(NAME));

    分区索引(Partitioned Indexes)

    分区索引的思想是将大的索引分成多个小索引,这样索引扫描时就可以减少IO。对于普通表,我们可以创建下面两种类型的分区索引。

    -- 范围全局分区索引
    CREATE INDEX test_idx ON test (amount)
    GLOBAL PARTITION BY RANGE (amount)
    (
    	PARTITION p1 VALUES LESS THAN (1000),
    	PARTITION p2 VALUES LESS THAN (2500),
    	PARTITION p3 VALUES LESS THAN (MAXVALUE)
    );
    
    -- 哈希(散列)全局分区索引
    CREATE INDEX cust_last_name_ix ON customers (cust_last_name)
    GLOBAL PARTITION BY HASH (cust_last_name)
    PARTITIONS 4;

    对于分区表,除了可以创建上面两种类型的分区索引外,我们还可以给某个表分区或所有分区创建分区索引。

    -- 创建分区表
    CREATE TABLE test
    (
            id number,  
            year number,  
            month number  
    )
    PARTITION BY RANGE (year)    
    (
            PARTITION p1 VALUES LESS THAN (2013),   
            PARTITION p2 VALUES LESS THAN (2014),    
            PARTITION p3 VALUES LESS THAN (2015),    
            PARTITION p4 VALUES LESS THAN (3000)
    );
    
    -- 给所有表分区创建分区索引
    CREATE INDEX test_idx ON test (year, month) LOCAL;
    
    -- 给 p1分区创建分区索引
    CREATE INDEX test_idx ON test (year, month) LOCAL (PARTITION p1);

    反向索引(Reverse Key Indexes)



    反向索引就是将正常的键值头尾调换后再进行存储,比如原值是'abc',将会以'cba'形式进行存储,为什么要这样做呢?原因是有些值是根据一定的规则生成的,如时间,序列等,当我们插入大量数据时,它们都需要同时插入到索引的某个区域,Oracle 称之为热点区域(hot spot),如果我们使用反向存储,可以有效的避免这个问题,但是有利就有弊,我们不能使用反向索引进行范围扫描,所以使用它要慎重。这里给我们一个非常重要的启示,在设计自动生成的值时,如果有可能,每两次生成的值范围要广。


    复合索引

    复合索引就是有多个列的索引,索引列的顺序很关键,如果索引包含 A,B,C 三列,而你的查询条件只包含 B,C 两列,Oracle 就无法使用索引。

    重建索引

    如果让你重建索引该怎么办呢?大多数人都会先删除索引,然后再创建新索引,其实通过下面的方式重建索引更快,因为 Oracle 可以利用现有的索引重建索引。

    ALTER INDEX ... REBUILD

    有关索引的视图

    如果让你查询一下某个表都定义了哪些索引该怎么办呢?呵呵,很简单,只需查询一下下面的视图即可。

    all_indexes
    all_ind_columns
    all_ind_expressions
    all_ind_partitions
    all_ind_subpartitions
    all_ind_statistics

    那如果让你查询一下某个索引是否被用到,该怎么办呢?首先,你需要让 Oracle 帮你监控一下索引,等过一段时间后,你可以查询下面的视图查看索引是否被用到。

    -- 让 Oracle 监控索引
    ALTER INDEX <indx name> MONITORING USAGE;
    
    -- 查询是否被用到
    select * from v$object_usage

    --更多参见:Oracle SQL 优化精萃

    -- 声明:转载请注明出处

    -- Last edited on 2015-08-24

    -- Created by ShangBo on 2015-07-07

    -- End

    展开全文
  • oracle 索引

    2008-07-09 16:54:00
    今天才发现oracle索引和主键是有区别的。索引字段的值可以选择可以重复,也可以选择不能重复non unique和unique选项。并且可以选择索引的方式,bitmap,还是其他形式。经常查询的一些字段制作索引。这些字段重复值...
  • ORACLE索引

    千次阅读 2012-10-08 16:09:19
    经常一起使用多个字段检索记录,组合索引比单索引更有效把最常用的列放在最前面,例:dx_groupid_serv_id(groupid,serv_id),在where条件中使用groupid或groupid,serv_id,查询将使用索引,若仅用到serv_id字段,则...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 27,712
精华内容 11,084
关键字:

oracle索引