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条记录,这时一个块被清空,总的数据条数才会减少。


 

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页