精华内容
下载资源
问答
  • 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 索引 使用方法

    2012-06-05 16:35:28
    Oracle 索引 使用方法,索引 使用原理, 索引 使用顺序过程
  • 分析下Oracle组合索引顺序的差异

    千次阅读 2019-07-11 17:41:07
    create index ix_ora_22 on ora(systime,sqlname,db) create index ix_ora_23 on ora(sqlname,db,systime) ora表systime的选择性最高 ...可以看到组合索引顺序Oracle中没有差异,对于表关联的情况有待测试

    create index ix_ora_22 on ora(systime,sqlname,db)

    create index ix_ora_23 on ora(sqlname,db,systime)

    ora表systime的选择性最高

     

    select * from dba_segments where segment_name like 'IX_ORA_2%'

    除了header_block不一样,其它占用磁盘空间一致

    下面单表访问看看执行计划

     

     

    可以看到组合索引的顺序在Oracle中没有差异,对于表关联的情况有待测试

    展开全文
  • 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索引原理

    千次阅读 2017-10-23 17:17:18
    oracle中的索引与mysql中的索引不一样,oracle索引是存储了索引列的值以及rowid值。而mysql索引分为聚集索引,非聚集索引。其中聚集索引只能有一个。 oracle索引的主要分为根,茎,叶子三部分。索引列值都是存放...

    索引原理

    1. oracel中索引类型很多,默认采用B树索引。
    2. oracle中的索引与mysql中的索引不一样,oracle中索引是存储了索引列的值以及rowid值。而mysql索引分为聚集索引,非聚集索引。其中聚集索引只能有一个。
    3. oracle中索引的主要分为根,茎,叶子三部分。索引列值都是存放在叶子节点上,茎只是存放了叶子节点的相关信息。
    4. oracle中的索引反应的是逻辑结构,不是物理结构。索引创建的时候,是先创建叶子节点,然后再创建茎,最后创建根。从索引的创建过程分析可以看出来。
    5. oracle中数据单位是以块为单位的,数据存储满一个数据块block,再去存储下一个数据块block。

    索引特点

    1. 索引高度一般比较低
    2. 索引是按照有序排列的。
    3. 索引存储列值及rowid值

    如何建索引

    1. oracle会在主键上创建索引
    2. oracle会在非空唯一索引列上创建索引。
    3. 经常出现的谓词,即出现在执行频率很高的SQL查询的where条件中的列上。
    4. 索引最好有单独的表空间,有利于备份和维护

    索引缺点

    1. 索引需要占据存储空间,索引能提供查询速度,但是维护和更新却浪费空间。因为索引是有序排列的。

    影响性能缘由

    1. oracle中sql语句,在查询时影响性能主要有逻辑读,排序,花费时间time代价cost(cpu)全表扫描等多个因素。

    索引应用

    1. order by
      1.1 order by 列名。会对order by 后面的列进行排序,
    2. distinct
      2.1 distinct 会进行排序

    索引压缩

    1. 在索引列中(一列或者多列),如果有多行数值相同,为了提供查询数据速度,可以将索引进行压缩存储。索引最终也是要存储在数据块上,压缩索引是为了减少扫描数据块的个数。
    2. 可以采用compress N子句方式创建压缩索引。
    3. create index index_name on tableName(col1,col2) compress N。
      3.1 如果N为1,则会对第一列进行压缩存储,N为2,则会对第一列和第二列压缩存储。

    组合索引设计

    1. 适当场合尽量避免回表读
    2. 组合索引中的列数越少越高效
    3. 组合索引中有2列时,情况又不一样:
      3.1 组合索引中,等值查询时,无论哪列在前在后都无所谓。
      3.2 组合索引的2列中,如果一列是范围查询,一列是等值查询,则一定要把等值查询放在前面,范围查询列放在后面才能使索引高效。这里是创建索引的时候,等值查询列在组合索引的最前面,范围列在最后面,不是sql语句中的书写顺序。
    4. 如果单列的查询与组合索引的前置列查询一样,则单列可以不用创建索引,直接利用组合索引来检索数据。最好将该列放在组合索引的第一列
    5. 经常一起出现在where字句中的列可以创建索引

    函数索引

    1. 在SQL查询中,如果where条件中的列上创建了索引,但是在该索引列上使用了函数,则oracle函数会使当前列上的索引失效。
    2. 对于上述情况,需要创建函数索引。
    3. 创建函数索引需要在创建语句中指定使用的函数和列。create index index_name on table_name(函数(列名))
    4. 如果创建了函数索引,则不能直接修改。需要先删除索引,再修改列,然后重建。

    不可见索引

    1. 有时候想增加一个索引而又不影响线上应用的性能,即通知SQL优化器默认不使用该索引。需要在创建索引的时候,设置为不可见。
    2. create index index_Name on table_name(col1,col2) invisible。
    3. 创建完索引后,需要确保optimizer_use_invisible_indexes的值为true。可以使用alter system set optimizer_use_invisible_indexes= true
    4. 使用hit提示,可以显示通知优化器使用该索引。

    查看表中索引方式

    1. 可以在表user_indexes中查看索引方式 。
    展开全文
  • Oracle 索引

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

    2015-01-04 16:46:32
    1、oracle索引空值问题  当在有空值得列上建立单列索引时,如果搜索条件为 is null 在解释计划中可以看到,对于此列oracle并没有使用索引查询;  但是当建立的是多列索引是,就会按照索引来进行查询。 2、B-树...
  • oracle索引

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

    2014-12-01 20:04:50
    一, oracle索引陷阱 一个表中有几百万条数据,对某个...oracle索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引oracle还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而
  • Oracle索引简介

    千次阅读 2013-08-30 11:10:37
    Oracle索引简介  索引(INDEX) 是为了加快数据的查找而创建的数据库对象,特别是对大表,索引可以有效地提高查找速度,也可以保证数据的惟一性。索引是对数据库表中一列或多列的值进行排序的一种结构使用索引可快速...
  • ORACLE索引

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

    万次阅读 2013-12-11 14:50:29
    Oracle 索引详解 一.索引介绍  1.1 索引的创建语法:  CREATE UNIUQE | BITMAP INDEX .  ON .  ( | ASC | DESC,  | ASC | DESC,...)  TABLESPACE   STORAGE   LOGGING | ...
  • 快速熟悉Oracle索引

    千次阅读 2013-06-28 16:57:44
    Oracle数据库自动使用和维护索引。 1.2 索引分类 唯一性索引非唯一性索引 1.3创建索引的两种方式 自动创建---在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引.手动创建----用户可以在其他...
  • Oracle 索引 详解

    千次阅读 2013-12-19 10:23:11
    Oracle 索引 详解 分类: Oracle Basic Knowledge2010-03-04 20:23 49197人阅读 评论(19) 收藏 举报 oracle集群存储优化数据库null 一.索引介绍  1.1 索引的创建语法:  CREATE ...
  • Oracle索引结构

    千次阅读 2020-05-04 13:02:28
    BTREE索引 索引是建立在是表的具体列上的,其存在的目的是让表的查询变得更快,效率更高。表的记录丢失关乎生死,索引丢失只需要重建即可。 由图可知,索引是由Root(根块)、Branch(茎块)和Leaf(叶子块)三部分...
  • Oracle 索引质量分析

    千次阅读 2014-04-14 16:33:36
    索引质量的高低对数据库整体性能有着直接的影响。良好高质量的索引使得数据库性能得以数量级别的提升,而低效冗余的索引则使得数据库性能缓慢如牛,即便是使用高档的硬件配置。因此对于索引在设计之初需要经过反复的...
  • Oracle索引abc

    2011-12-31 22:21:42
    在这篇文章里,给大家简单介绍一下本人对Oracle索引的理解,如有不妥的地方,请不吝指教。 本文只讲最最平常最最简单的索引,就是以create index ix on tx(a,b,c);形式创建的索引,而不讲位图索引、反向键索引、...
  • oracle索引介绍

    千次阅读 2015-10-14 09:58:09
    索引介绍  1.1 索引的创建语法:  CREATE UNIUQE | BITMAP INDEX .  ON .  ( | ASC | DESC,  | ASC | DESC,...)  TABLESPACE   STORAGE   LOGGING | NOLOGGING  
  • Oracle索引 详解

    万次阅读 多人点赞 2013-10-27 18:18:34
    索引介绍  1.1 索引的创建语法:  CREATE UNIUQE | BITMAP INDEX .  ON .  ( | ASC | DESC,  | ASC | DESC,...)  TABLESPACE   STORAGE   LOGGING | NOLOGGING  COMPUTE
  • oracle 索引原理

    2015-08-01 20:30:14
     对于分支节点块(包括根节点块)来说,其所包含的索引条目都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)。每个索引条目(也可以叫做每条记录)都具有两个字段。第一个字段表示当前该...
  • oracle 索引详解

    千次阅读 2012-07-23 16:33:04
    索引介绍  1.1 索引的创建语法:  CREATE UNIUQE | BITMAP INDEX .  ON .  ( | ASC | DESC,  | ASC | DESC,...)  TABLESPACE   STORAGE   LOGGING | NOLOGGING  COMPUTE
  • 按照以下顺序查找,基本上能发现原因所在。 查找原因的步骤 首先,我们要确定数据库运行在何种优化模式下,相应的参数是:optimizer_mode。可在svrmgrl中运行“showparameteroptimizer_mode”来查看。ORACLEV7以来...
  • Oracle索引基础

    2011-04-28 19:21:00
    在加上索引的列进行查找时,Oracle首先在索引中查找,而索引是按顺序存储的,相应的索引项向Oracle提供了该项记录在磁盘中的确切位置。索引改进性能的程度取决与数据的选择性以及在表的块之间分布数据的方式。如果...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 74,539
精华内容 29,815
关键字:

oracle索引的顺序