【对象管理】管理索引

       索引的创建的目的在于更快捷地查找定位数据行和数据块，提高数据库语句的查找速度，提高性能。oracle 为我们提供的索引基本上都是以B+树的这种数据结构为基础的，具体索引种类的介绍，可以查看我的另外一篇博文：

索引的种类：http://blog.csdn.net/flighting_sky/article/details/8868431

一、管理索引

1、插入数据之后创建索引

     如果需要大量从外部插入数据到表格的话，一般会使用SQL*Loader和Import功能，这样的话，为了提高性能，通常有两种方法：

一是，删除索引，插入数据，重新建立索引；

SQL>drop index IDX_TEST;--删除索引；------批量插入数据之后，重新建立索引SQL>create index IDX_TEST on test(id);

二是，禁用索引，插入数据，重建索引；

SQL>alter index IDX_TEST unusable;---让索引不可用，这是插入的时候不会更新索引；-----批量插入数据之后，重建索引；SQL>alter index IDX_TEST rebuild;

       创建索引的时候，主要的性能问题在于排序，如果在大量插入数据的时候，更新大量的索引，就需要很大的排序空间，为此，插入数据的效率将会很慢。排序的空间主要由两方面提供，一是来自PGA的sort area，其大小由实例由实例参数sort_area_size所决定，可以如下查看和修改该参数：

SQL> show parameter sort_area_size;----查看实例参数sort_area_size;NAME                                 TYPE        VALUE------------------------------------ ----------- ----------sort_area_size                       integer     65536SQL> alter system set sort_area_size=65536 scope=spfile;---修改实例参数sort_area_size;

        另一方面的空间消耗来自于临时表空间，当内存中的空间不够用的时候，数据库就会把部分数据写入到磁盘中的临时表空间中暂时存储。这样一来将会耗费大量的I/O资源。下面语句可以查看哪个会话哪条语句使用多少临时空间。

SQL>  select sess.sid,sort.blocks*8/1024 MB,sql.sql_text text  2   from v$session sess,v$sort_usage sort,v$sql sql  3   where sort.SESSION_ADDR = sess.SADDR  4    and sql.ADDRESS = sort.SQLADDR  5   order by sort.blocks ;

2、物理设计：选择合适的表和列创建索引

          适合创建索引的表：

第一，如果一个表被频繁地访问，并且经常是返回15%的数据，则该表适合创建索引。

第二，小表不需要索引。不过，如果小表的增长速度很大的话，当增长成一定程度，就需要建立索引来提高检索性能。

第三，使用有索引的表列来连接。

      选择合适的列来创建索引：

第一，值的基数。如果基数比较大则适合创建normal index，如果基数比较小则适合创建bitmap index。

第二，列的唯一度。如果基数跟总行数的比值比较高，适合建立索引【考虑值中的NULL值数量比例】。

第三，经常进行范围查询的列适合创建索引。

       下面情况中中的列不适合创建索引：

第一，列值中有大量的NULL值，同时很少查找非NULL值；

第二，LONG与LONG RAW列不适合创建索引；

       需要注意的是，对于virtual column也适合创建unique或non-unique索引。

        索引列的顺序：

第一，把使用频繁度最高的列放在第一位。如（col1,col2,col3）这个复合索引，如果访问col1或col1,col2都可以使用到索引加快检索速度，而访问col2或col3或col2,col3则不会使用到索引。

3、限制表索引的数目

      我们知道当insert或删除数据行，或者update列的时候，都需要对索引进行更新维护。如果一个表的索引众多，则会使得索引的维护开销巨大。为此，需要根据业务的需求，表的实际使用情况，合理地设计索引、删除索引、重建索引以限制表索引的数量，提高索引的使用效率。判断一个索引是否需要删除或重建可以通过索引的使用频率、索引列的使用频率、索引的聚簇因子三个参数来决定。

      下面情况下，需要删除索引：

第一，索引并不能提高检索效率。比如表的数据行很少，或者数据行多，但是index entry很少，都会使得索引其不到应有的作用，反而占用过多的空间。直接的体现就是，索引不被执行计划所使用，也就是索引的使用率很低；

第二，应用程序不使用索引；

4、估计索引的大小，设置存储参数

          创建索引之前应该根据表的数据行数量、表数据的增长速度，评估索引的空间需求、以及空间的增长速度，设置存储参数中的初始值、分配值、最大值。

       根据现有表创建索引，可以使用dbms_space. create_index_cost()来计算创建出来的索引所占据的实际空间以及所需要分配的空间大小。

SQL> set serverout on;SQL> declare  2  used_bytes number;---输出创建完索引后实际的索引大小；  3  alloc_bytes number;---输出创建该索引所需要分配的大小；  4  begin  5  dbms_space.create_index_cost(  6  'create index tt on tt(a)',used_bytes,alloc_bytes);  7  dbms_output.put_line('used_bytes:'||used_bytes);  8  dbms_output.put_line('alloc_bytes:'||alloc_bytes);  9  end; 10  /used_bytes:1066alloc_bytes:65536

5、选择索引的表空间

      给索引设置表空间的时候主要是考虑是否需要把索引和表放在同一个表空间的问题。

     在不同物理设备的表空间，则可以并发性很好，可以提高检索效率；

     在同一个表空间则比较好维护，可以避免出现不同表空间的时候，一个表空间offline，导致索引或表不能使用的情况。

SQL> alter index tt rebuild tablespace users;

6、并发创建索引

      所谓并发性，就是多个进程同时做同一件事。并发创建索引的时候，可以大大提高创建的效率。同时并发操作的时候，每一个进程都会独立使用同一套存储参数，也就是说并发度为12、initial为5k的索引创建，实际的initial为60K。

SQL> alter index tt parallel 4;

7、创建索引的时候nologging

      创建索引的时候，不产生日志有下面几个好处：

第一，减少redo log file的空间；

第二，减少创建索引的时间；

第三，大大提高并发创建大索引的性能；

       不过，由于没有redo log，为此进行该操作之后，一般需要进行一次增量备份。

SQL> create index tt on tt(a) nologging;Index created.SQL> alter index tt nologging;Index altered.SQL> alter index tt logging;Index altered.

8、unusable index与invisible  index的使用

     两者的基本区别：

种类

[index]

优化器

DML

-维护

用途事后处理实例参数注意unusableNONO提高bulk load 性能

rebuild或drop,create;

截断表会让该类索引valid

skip_unusable_indexes,该参数

默认为true，此时不会被优化器

所使用，除非使用index()hint。

若为false，若使用到了该索引则

会报错。

第一，11.2g开始索引段会被删除

第二，创建索引时候出错会自动使

该索引unusable;

第三，必须重建才能使用

invisible NOYES

第一，删除前测试其影响

第二，在不影响整体应用程序

的前提下，为可能的操作或模

块使用临时索引结构

让它变为visibleoptimizer_use_invisible_indexes

第一，11.1g之后才提供该类索引

第二,不能分区索引部分invisible,

因为其DML操作的时候需要维护

9、处理碎片

     随着索引的增长，索引数据块中碎片也就增加了。为此，处理这些碎片有两种方法，一是rebuild，二是Coalesce.不同的方法，自然会产生不同的成本。

    coalesce的成本比较低，主要是因为其是压缩同一个分支下面的叶节点的空间，速度快，而且不会要求大量的磁盘空间，只是不能把索引移动到其他的表空间罢了。

    而rebuild的成本比较高，因为其相当于重新建立一个新的索引，需要大量磁盘空间，当然也可以把索引移动到其他的表空间中。

二、创建索引

1、创建normal index     

SQL>create index tt on tab1(a);

2、创建unique index

SQL>create unique index tt on table1(b);

3、创建带有约束的索引

----创建表的时候创建约束的同时创建索引SQL>create table tab1      1 (a int primary key);---或者使用USING语法SQL>CREATE TABLE a (  1 a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));

4、在线创建索引

SQL>CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

        在线创建索引，在创建索引的同时可以允许DML操作，但是不允许DDL操作和并发操作。

5、创建function-based index

      基于函数的索引，简单理解就是用来保存函数结果的索引。表的拥有者必须拥有基于函数索引对应函数的EXECUTE权限。而对于用户创建的函数需要有DETERMINISTIC，否则不能用于创建基于函数的索引。

      还有一点需要注意的是，create index的时候会存储该函数最近被使用的时间，在索引处于valide状态的时候，该时间都会被更新。如果函数被修改了，则该索引也就变成invalid。此时，可以使用下面的方法来使其变为valid状态。

SQL>anylyze index tt validate structure;

       创建基于函数索引的时候，函数不能是分组函数，否则会报错。

SQL> create index function_ind on sales(count(quantity_sold));create index function_ind on sales(count(quantity_sold))                                   *ERROR at line 1:ORA-00934: group function is not allowed here

       创建基于函数的normal索引

SQL> create index function_ind on sales(sin(quantity_sold));

SQL> select index_name,index_type from user_indexes where index_name ='FUNCTION_IND';INDEX_NAME                     INDEX_TYPE------------------------------ ---------------------------FUNCTION_IND                   FUNCTION-BASED NORMAL

6、创建压缩索引
     所谓压缩，其实就是把index key分成prefix entry 和suffix entry两部分。prefix entry是指index key中重复的value值，然后系统会生成一个标志值作为suffix entry。然后在数据块中存储的时候，prefix entry只会被存储一次，然后index key就是suffix entry+ROWID.这样大大节约了空间。

   为此，下面情况下应该建立压缩索引：

第一，non-unique索引。

第二，unique的符合索引。

SQL> create index compression_ind on sales(quantity_sold) compress 1;Index created.-----其中compress 1的这个1表示prefix columns列的个数，也就是索引列的个数

三、修改索引

SQL>ALTER INDEX emp_ename STORAGE (PCTINCREASE 50);---修改存储参数；SQL>ALTER TABLE emp ENABLE PRIMARY KEY USING INDEX;---修改约束的存储参数，修改索引；SQL>ALTER INDEX emp_name REBUILD;--重建索引；SQL>ALTER INDEX emp_email_uk UNUSABLE;---使得索引不可用；SQL>ALTER INDEX emp_name  INVISIBLE;--使得索引失效；SQL>ALTER INDEX emp_name  RENAME TO emp_new_name ;---改名；----如果在监控期间使用了索引，则就会体现在V$object_usage中【只是表示使用过】SQL>ALTER INDEX TT  MONITORING USAGE;---开始监控该索引的使用情况；SQL>ALTER INDEX TT  NOMONITORING USAGE;---结束监控该索引的使用情况SQL> select * from v$object_usage;INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING------------------------------ ------------------------------ --- --- ------------------- -------------------TT                             TT                             NO  YES 08/14/2013 21:42:38 08/14/2013 21:46:54

四、删除索引

       删除索引的时候，需要注意的是带有约束的索引（unique、primary key）,这些约束的索引不能直接被删除，否则会报错。此时，需要直接删除约束。

SQL> select index_name,table_name from user_indexes where table_name ='TT';INDEX_NAME                     TABLE_NAME------------------------------ ------------------------------SYS_C0011805                   TTSQL> drop index SYS_C0011805;drop index SYS_C0011805           *ERROR at line 1:ORA-02429: cannot drop index used for enforcement of unique/primary key