索引碎片整理--重建索引、合并索引、shrink索引 - 数据库编程

44 150021 86 .852663347 1 SQL> SQL> analyze index test_idx1 validate structure; Index analyzed. SQL> select height, 2 lf_rows, 3 lf_blks, 4 del_lf_rows, 5 btree_space, 6 used_space, 7 pct_used, 8 ((used_space - del_lf_rows_len) / btree_space) pct_unused, 9 ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows 10 from index_stats 11 where name = 'TEST_IDX1'; HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- -------------- 2 10000 21 9999 175944 150021 86 .001329969 .0001 SQL> SQL> alter index test_idx1 deallocate unused; Index altered. SQL> analyze index test_idx1 validate structure; Index analyzed. SQL> select height, 2 lf_rows, 3 lf_blks, 4 del_lf_rows, 5 btree_space, 6 used_space, 7 pct_used, 8 ((used_space - del_lf_rows_len) / btree_space) pct_unused, 9 ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows 10 from index_stats 11 where name = 'TEST_IDX1'; HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- -------------- 2 10000 21 9999 175944 150021 86 .001329969 .0001 SQL>

收集统计信息，之后可以看到在dba_indexes中依然显示存在的索引叶块，优化器从而使用该索引。

SQL> exec dbms_stats.gather_table_stats('SYS','TEST',cascade=>true);

PL/SQL procedure successfully completed.

SQL>  select index_name,leaf_blocks,num_rows,degree from dba_indexes where index_name='TEST_IDX1';

INDEX_NAME                     LEAF_BLOCKS   NUM_ROWS DEGREE
------------------------------ ----------- ---------- ----------------------------------------
TEST_IDX1                                1          1 1

SQL> set autotrace trace exp
sSQL>   
SQL> select * from test where id<20;

Execution Plan
----------------------------------------------------------
Plan hash value: 2624864549

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     1 |     7 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<20)

SQL>

但是注意：使用analyze index index_name validate structure ;进行索引分析的时候会锁定相应的对象直到该命令执行完成，如果不加锁可以使用online参数，但使用online参数数据信息又不会记录到index_stats视图，且在重建索引的过程中会产生很多的redo日志，可以考虑使用nologging参数，另外当在分析完成后在执行插入操作，那么相应的del_lf_rows将会改变从而影响对索引的分析信息提取：
eg：

SQL> select * from test;         

        ID TEX
---------- ---
     10000 Amy

SQL> insert into test values(10001,'Rhys');
insert into test values(10001,'Rhys')
                              *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."TEXT" (actual: 4, maximum: 3)


SQL> desc test             
 Name                                                                                                              Null     Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ID                                                                                                                         NUMBER
 TEXT                                                                                                                       CHAR(3)

SQL> alter table test modify text char(15);

Table altered.

SQL> insert into test values(10001,'Rhys');

1 row created.

SQL> commit;

Commit complete.

SQL> select height,
  2         lf_rows,
  3         lf_blks,
  4         del_lf_rows,
  5         btree_space,
  6         used_space,
       pct_used,
  7    8         ((used_space - del_lf_rows_len) / btree_space) pct_unused,
  9         ((lf_rows - del_lf_rows) / lf_rows) pct_undel_rows
 10    from index_stats
 11   where name = 'TEST_IDX1';

    HEIGHT    LF_ROWS    LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE   PCT_USED PCT_UNUSED PCT_UNDEL_ROWS
---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- --------------
         2      10000         21        9999      175944     150021         86 .001329969          .0001

SQL> analyze index test_idx1 validate structure;

Index analyzed.

SQL> select height,
  2         lf_rows,
  3         lf_blks,
       del_lf_rows,
  4    5         btree_spac

索引碎片整理--重建索引、合并索引、shrink索引(二)