管理索引

管理索引

1)索引是用于加速数据存取的数据库对象。通过使用索引，可以降低i/o次数，提高数据访问性能。

按索引数据的存储方式：B*树索引、反向索引、位图索引

按索引列的个数：单列索引、复合索引

按索引列值惟一性：惟一索引、非惟一索引

 

a)单列索引和复合索引

单列索引是基于单个列所建立的索引;复合索引是基于二列或多列所建立的索引。B*树索引列的个数不能超过32列，位图索引列的个数不能超过30列。可以在同一张表上建立多个索引，但要求列的组合不同。

create index emp_idx1 on emp (ename,job);

 

b)惟一索引和非惟一索引

 惟一索引是索引列值不能重复的索引;非惟一索引是索引列值可以重复的索引。无论是惟一索引还是非惟一索引列都允许为null。当定义主键约束或惟一约束时，oracle会自动在相应的约束列上建立惟一索引。

 

2)索引管理指导方针

a)装载数据后建立索引

b)索引正确的表和列

 c)索引应该建立在where子句经常引用的表列上。如果在大表上频繁使用某列或几列作为条件执行检索操作，并且检索行数低于总行数15%，那么应该考虑在这些列上建立索引

 d)为了提高多表连接的性能，应该在连接列上建立索引

 e)不要在小表上建立索引

 f)如果经常需要基于某列或某几列执行排序操作，那么通过在这些列上建立索引，可以加快数据排序的速度。

 g)限制表索引的个数

 

3)删除不再需要的索引

a)删除小表上建立的索引。如果表很小，使用索引不会加快查询速度

b）删除查询语句不会引用的索引。如果在某列或某几列上建立索引，但这些不会在where子句中引用，那么应该删除相应的索引。

 

4)建立索引

Ø       建立B*树索引

如果在wehre子句中经常引用某列或几列，应该基于这些列建立B*树索引。

SQL>create index ind_ename on emp(ename) pctfree 30 tablespace user02

 

Ø         使用B*树索引

建立B*树索引后，如果在where子句中引用索引列，oracle会根据统计信息确定位是否使用B*树索引定位表行数据。

SQL>set autotrace on explain

SQL>select * from emp where ename=’scott’;

 

1)       建立位图索引

B*树索引建立在重复值很少的列上，而位图索引建立在重复值很多、不同值相对固定的列上，使用位图索引可以节省大量磁盘空间，其占用空间仅仅是在相同列上建立B*树索引所用空间的1/20~1/10.建立位图索引时，oracle会基于每个不同值建立一个位图。

 

建立位图索引：create bitmap index idx_gender on customers(cust_gender);

 

初始化参数create_bitmap_area_size用于指定建立位图索引时分配的位图区尺寸，默认值8M，该参数越大，建立位图索引速度越快。

修改该参数的示例如下：

alter system set create_bitmap_area_size=8388608 scope=spfile;

 

2)       建立反向索引

create index customers_pk on customers(cust_id) reverse;

 

3)       修改索引

a)       修改索引段存储参数

存储参数initial、minextents是不能修改的，而修改其他存储参数只对新分配的区起作用

alter index upper_ename storage(next 200k maxentents 50);

b)       分配和释放索引空间

当使用sql*loader或insert给表装载数据时，也会为索引填加数据。如果索引段空间不足，将导致动态扩展索引段。

alter index deparment_dname allocate extent(size 1m);

 

释放多余空间

alter index department_dname deallocate unused;

 

c)       重建索引

执行delete操作时，会删除表的数据，但在索引上仅仅进行逻辑删除，其所占用的空间不能供其他插入操作使用。如果在索引列上频繁执行update或delete操作，应该定期重建索引，以提高其空间利用率。

alter index department_dname rebuild;

 

d)       联机重建索引

使用rebuild选项重建索引时，如果其他用户正在表上执行dml操作，那么重建索引将失败。为了最小化dml操作的影响，重建索引时，可以使用rebuild online选项。

alter index department_dname rebuild online;

 

4)       删除索引

drop index department_dname;

 

5)       显示表的所有索引

SQL>select index_name,index_type,uniqueness from dba_indexes

where owner=’scott’ and table_name=’emp’;