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