索引的合并、重建与监视

查看当前用户的所有索引:

SQL> select index_name,index_type,table_name,uniqueness from user_indexes;

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES
------------------------------ --------------------------- TT_IDX                         NORMAL                      TT                             NONUNIQUE
PARTITION_IDX_LOCAL            NORMAL                      PARTITIONED_2                  NONUNIQUE
PARTITIONED_IDX                NORMAL                      PARTITIONED_1                  NONUNIQUE
UNION_T3_INDEX                 NORMAL                      T3                             NONUNIQUE
                   UNIQUE

已选择20行。

已用时间:  00: 00: 00.45

查看索引所在的表和列

SQL> select index_name,table_name,column_name from user_ind_columns order by 2,3;
TEST1_PK                       TEST_TAB1                      A_COL
TRUN_UNI_INDEX                 TRUN_TAB                       A
TT_IDX                         TT                             USER_ID
I5                             TT1                            DEPTNO

SQL> create table t_tab as select * from emp;

表已创建。

已用时间:  00: 00: 00.46
SQL> create index t_indx_empno on t_tab(empno);

索引已创建。

已用时间:  00: 00: 00.04
SQL> analyze index t_indx_empno validate structure;

索引已分析

已用时间:  00: 00: 00.03
SQL> select height,blocks,br_blks,lf_blks,lf_rows,lf_rows,del_lf_rows from index_stats;

    HEIGHT     BLOCKS    BR_BLKS    LF_BLKS    LF_ROWS    DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- ---------- ----
         1          8          0          1         14           0

已用时间:  00: 00: 00.06
SQL> select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats

    HEIGHT     BLOCKS    BR_BLKS    LF_BLKS    LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
         1          8          0          1         14           0

已用时间:  00: 00: 00.01
SQL> update t_tab set empno=7777 where empno=7900;

已更新 1 行。

已用时间:  00: 00: 00.00
SQL> commit;

提交完成。

已用时间:  00: 00: 00.01

SQL> analyze index  t_indx_empno validate structure;

索引已分析

已用时间:  00: 00: 00.00

SQL> select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats;

    HEIGHT     BLOCKS    BR_BLKS    LF_BLKS    LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
         1          8          0          1         15           1

已用时间:  00: 00: 00.00

SQL> update t_tab set empno=8888 where empno=7902;

已更新 1 行。

已用时间:  00: 00: 00.00
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00
SQL> analyze index  t_indx_empno validate structure;

索引已分析

已用时间:  00: 00: 00.01

SQL> select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats;

    HEIGHT     BLOCKS    BR_BLKS    LF_BLKS    LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
         1          8          0          1         15           1

已用时间:  00: 00: 00.01

SQL> insert into t_tab select * from t_tab;

SQL> commit;

提交完成。

已用时间:  00: 00: 00.00


SQL> select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats;

    HEIGHT     BLOCKS    BR_BLKS    LF_BLKS    LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
         1          8          0          1         15           1

已用时间:  00: 00: 00.01

SQL> analyze index  t_indx_empno validate structure;

索引已分析

已用时间:  00: 00: 00.00

SQL>  select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats;

    HEIGHT     BLOCKS    BR_BLKS    LF_BLKS    LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
         2          8          1          4       1792           0

已用时间:  00: 00: 00.03

SQL> select count(*) from t_tab;

  COUNT(*)
----------
      1792

已用时间:  00: 00: 00.01
SQL> delete t_tab where rownum<=600;

已删除600行。

已用时间:  00: 00: 00.01
合并索引：
SQL> alter index t_indx_empno coalesce;

索引已更改。

已用时间:  00: 00: 00.06
SQL> analyze index t_indx_empno validate structure;

索引已分析

已用时间:  00: 00: 00.00

SQL> select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats;

    HEIGHT     BLOCKS    BR_BLKS    LF_BLKS    LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
         2          8          1          3       1192           0

已用时间:  00: 00: 00.01

重建索引：
SQL> ALTER INDEX T_INDX_EMPNO REBUILD;

索引已更改。

已用时间:  00: 00: 00.18

SQL> ANALYZE INDEX T_INDX_EMPNO VALIDATE STRUCTURE;

索引已分析

已用时间:  00: 00: 00.00

SQL>  select height,blocks,br_blks,lf_blks,lf_rows,del_lf_rows from index_stats;

    HEIGHT     BLOCKS    BR_BLKS    LF_BLKS    LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
         2          8          1          3       1192           0

已用时间:  00: 00: 00.01

注意：1.在查看索引的内容时，一定要先分析索引(analyze index my_index validate structure)，不然的话数据不会重新收集。
2.合并只是合并枝干内的叶子，如果叶子属于不同的枝干则分别独立合并，合并不会改变索引的结构，不会改变索引的表空间和索引的类型。有事务的时候也可以合并索引。
3.重建只能够在没有事务的情况下进行，如果有未提交的事务，就会报错。
4.对本文中列的说明：
height：索引的高度
blocks：索引的总块数
br_blks：枝干块数
lf_blks：叶子块数
lf_rows：叶子内行数
del_lf_rows ：叶子中被删除的行数

索引的监视与取消监视


查看索引是否使用过，如果长期未使用过的索引，就可以删除掉
1.生成监控索引的脚本文件：

spool c:\index_monitor.log
select 'alter index '||index_name||' monitoring usage;' from user_indexes; 
spool off;
然后对该脚本文件修改一下，并执行，即可监控索引了。

查看索引是否使用过：
select table_name,index_name,used from v$object_usage;
如果used列对应的值为NO，则证明该索引未使用过。在监视过程中
我们不能够启动数据库，因为v$视图会被重新创建，丢失原来的监视。

2.生成取消监控索引的脚本文件：
spool c:\index_nomonitor.log
select 'alter index '||index_name||' nomonitoring usage;' from user_indexes;
spool off;



3.生成重建索引的脚本文件
spool /home/oracle/index_rebuild.sql
SELECT 'ALTER INDEX '||INDEX_NAME ||' REBUILD;'FROM USER_INDEXES; 
spool off;

4.生成合并索引的脚本文件
spool /home/oracle/index_rebuild.sql
SELECT 'ALTER INDEX '||INDEX_NAME ||' coalesce;'FROM USER_INDEXES; 
spool off;