Oracle 索引

oracle数据库表的每一行都有rowid来记录，rowid能够标识某一行的具体位置(物理位置)。使用rowid来查找数据，是最快的查询数据的方式。通过创建索引，就可以快速找到指定的rowid。

当不创建索引时，查找制定条件的数据时，必须遍历整张表，取出每一条记录与条件比较，时间和I/O开销都很大。创建索引之后，就可以通过索引中的rowid，快速的找到记录了。

oracle需要为索引分配存储空间，创建索引时，会在默认表空间或指定表空间创建索引段。

建立索引要注意：                                                                                                                                        

建立在频繁在where子句使用的列，且检索行数低于总数的15%.

建立在频繁排序的列上.

限制索引的个数。索引可以加快查询速度，但会降低dml操作性能。

若insert操作较多，为索引设置较大的pctfree.

不要在小表上建立索引.

可在多表连接列上创建索引.

使用nologging可以节省日志空间、降低索引建立时间、提高索引并行建立性能.

 

创建索引：                                                                                                                                                     

在创建索引时，oracle会对字段排序，将字段值和rowid存放在索引段。

1.B树索引(balance)

若在where子句中经常用到，可以建立B树索引。最常用。

create index student_classid_idx on student(classid) pctfree 25 tablespace users;

2.位图索引

当一个列的基数小于1%，建立位图索引。比如1000条记录，不同值只有100条，则建立位图索引。

创建时，oracle对表进行全表扫描，为各个取值建立“图表”。

create bitmap index student_sex_idx on student(sex) tablespace users;

初始化参数,create_bitmap_area_size，指定建立索引时分配的位图大小，默认8M，值越大，建立索引的时间越短。该参数是静态参数，因此修改后需重启数据库。

alter system set create_bitmap_area_size=8388608 scope=spfile;

3.反向索引

反向索引可以阻止在单调递增许列上建立索引后使叶子结点偏向某一方。

表的主键，oracle会自动创建B树索引。但当主键值单调递增时 ，就该建立反向索引。

create index student_id_idx on student(id) reverse tablespace users;

将原先的B树索引重建为返乡索引

alter index student_id_idx rebuild reverse;

4.基于函数的索引

如select id,sex,name from student where city=upper('wenzhou');如此，会进行全表搜索，即使建立索引，当wenzhou不存在于表中时，也会扫描全表，并为遇到的每行做upper计算。

此时，可创建基于函数的索引。

create index student_city_idx on student(lower(city));--因为一般的sql中都用小写

修改索引：                                                                                                                                                 

建立索引后，随着表的dml操作，索引中会产生存储碎片，影响索引的效率。

合并索引，将B树叶子节点的存储碎片合并在一起，不改变索引的物理组织结构。

alter index_classid_idx coalesce deallocate unused

重建索引，可以改变存储参数、存储表空间

alter index_classid_idx rebuild tablespace user2;

删除索引：                                                                                                                                                  

删除显示创建的 索引：drop index  index_classid_idx。

删除定义约束时oracle创建的索引，需要删除或禁用约束本身，删除表时相关的索引会被删除。

 

显示索引信息                                                                                                        

所有索引：dba_indexes(数据库的所有索引) all_indexes(当前用户可以访问的索引) user_indexes(当前用户的索引信息)。  

select index_name,index_type from dba_indexes where owner='TEST'

 

索引列：dba_ind_columns，all_ind_columns，user_ind_columns  。

select column_name,column_length from  user_ind_columns where index_name='STUDENT_CLASSID_INX'

 

索引段的位置和大小：索引段名称和索引名一样

select tablespace_name,segment_type,bytes from user_segments where segment_name='STUDENT_CLASSID_IDX'

 

函数索引：  user_ind_expression，dba_ind_expression

 select column_expression from user_ind_expression where index_name='STUDENT_CLASSID_IDX'