特殊条件下，一个unusable 的索引REBUILD后分配的block是否改变

Kevin Zou

2011-8-29

一个unusable 的索引，如果要启用，必须要对其REBUILD。我们知道REBUILD一个索引时，分配给索引的BLOCK信息会发生改变。

如果是对一个新创建的TABLE和INDEX，UNUSABLE后，没有INSERT/UPDATE/DELETE的操作，REBUILD时BLOCK信息会发生改变吗？

搭建测试环境：

SQL> create table test as select * from all_objects where 0=1;

Table created.

SQL> insert into test select * from all_objects;

13540 rows created.

SQL> create index i_test on test(object_id);

Index created.

查看新创建的索引I_TEST 占用的BLOCK信息：

SQL> select segment_name, file_id, blocks, block_id

2 from dba_extents

3 where segment_name='I_TEST' and wner='SYS';

SEGMENT_NAME FILE_ID BLOCKS BLOCK_ID

-------------------- ---------- ---------- ----------

I_TEST 1 8 37481

I_TEST 1 8 37489

I_TEST 1 8 37497

I_TEST 1 8 37505

通过SQL,查看索引被引用。

SQL> set autotrace traceonly stat exp
SQL> select count(*) from test;


Execution Plan
----------------------------------------------------------
Plan hash value: 1690092453

------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    10   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_TEST | 13540 |    10   (0)| 00:00:01 |
------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        138  recursive calls
          0  db block gets
         52  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

使索引处于UNUSABLE状态；

SQL> alter index i_test unusable;

Index altered.

因为索引I_TEST不能使用，同样的SQL只能走FTS。

SQL> select count(*) from test;


Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    49   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 13540 |    49   (3)| 00:00:01 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
        138  recursive calls
          0  db block gets
        194  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select object_id from dba_objects where object_name='I_TEST';

 OBJECT_ID
----------
     65949
 查询到索引I_TEST的对象ID 为 65949

 SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 65949';

Session altered.
查询到索引I_TEST的对象ID 为 65949，使用tree dump可以看到索引树的结构
branch: 0x40926a 4231786 (0: nrow: 29, level: 1)
   leaf: 0x40926b 4231787 (-1: nrow: 485 rrow: 485)
   leaf: 0x40926c 4231788 (0: nrow: 479 rrow: 479)
   leaf: 0x40926d 4231789 (1: nrow: 478 rrow: 478)
   leaf: 0x40926e 4231790 (2: nrow: 479 rrow: 479)
   leaf: 0x40926f 4231791 (3: nrow: 479 rrow: 479)
   leaf: 0x409270 4231792 (4: nrow: 479 rrow: 479)
   leaf: 0x409271 4231793 (5: nrow: 479 rrow: 479)
   leaf: 0x409272 4231794 (6: nrow: 478 rrow: 478)
   leaf: 0x409273 4231795 (7: nrow: 479 rrow: 479)
   leaf: 0x409274 4231796 (8: nrow: 479 rrow: 479)
   leaf: 0x409275 4231797 (9: nrow: 479 rrow: 479)
   leaf: 0x409276 4231798 (10: nrow: 479 rrow: 479)
   leaf: 0x409277 4231799 (11: nrow: 479 rrow: 479)
   leaf: 0x409278 4231800 (12: nrow: 479 rrow: 479)
   leaf: 0x409279 4231801 (13: nrow: 479 rrow: 479)
   leaf: 0x40927a 4231802 (14: nrow: 479 rrow: 479)
   leaf: 0x40927b 4231803 (15: nrow: 479 rrow: 479)
   leaf: 0x40927c 4231804 (16: nrow: 479 rrow: 479)
   leaf: 0x40927d 4231805 (17: nrow: 471 rrow: 471)
   leaf: 0x40927e 4231806 (18: nrow: 449 rrow: 449)
   leaf: 0x40927f 4231807 (19: nrow: 449 rrow: 449)
   leaf: 0x409280 4231808 (20: nrow: 449 rrow: 449)
   leaf: 0x409281 4231809 (21: nrow: 449 rrow: 449)
   leaf: 0x409282 4231810 (22: nrow: 449 rrow: 449)
   leaf: 0x409283 4231811 (23: nrow: 448 rrow: 448)
   leaf: 0x409284 4231812 (24: nrow: 448 rrow: 448)
   leaf: 0x409285 4231813 (25: nrow: 449 rrow: 449)
   leaf: 0x409286 4231814 (26: nrow: 449 rrow: 449)
   leaf: 0x409287 4231815 (27: nrow: 404 rrow: 404)

重新REBUILD 索引：

SQL> alter index i_test rebuild;

Index altered.

SQL> select segment_name, file_id, blocks, block_id

2 from dba_extents

3 where segment_name='I_TEST' and wner='SYS';

SEGMENT_NAME FILE_ID BLOCKS BLOCK_ID

-------------------- ---------- ---------- ----------

I_TEST 1 8 37641

I_TEST 1 8 37649

I_TEST 1 8 37657

I_TEST 1 8 37665

再次DUMP 索引的TREEDUMP：

branch: 0x40930a 4231946 (0: nrow: 29, level: 1)

leaf: 0x40930b 4231947 (-1: nrow: 485 rrow: 485)

leaf: 0x40930c 4231948 (0: nrow: 479 rrow: 479)

leaf: 0x40930d 4231949 (1: nrow: 478 rrow: 478)

leaf: 0x40930e 4231950 (2: nrow: 479 rrow: 479)

leaf: 0x40930f 4231951 (3: nrow: 479 rrow: 479)

leaf: 0x409310 4231952 (4: nrow: 479 rrow: 479)

leaf: 0x409311 4231953 (5: nrow: 479 rrow: 479)

leaf: 0x409312 4231954 (6: nrow: 478 rrow: 478)

leaf: 0x409313 4231955 (7: nrow: 479 rrow: 479)

leaf: 0x409314 4231956 (8: nrow: 479 rrow: 479)

leaf: 0x409315 4231957 (9: nrow: 479 rrow: 479)

leaf: 0x409316 4231958 (10: nrow: 479 rrow: 479)

leaf: 0x409317 4231959 (11: nrow: 479 rrow: 479)

leaf: 0x409318 4231960 (12: nrow: 479 rrow: 479)

leaf: 0x409319 4231961 (13: nrow: 479 rrow: 479)

leaf: 0x40931a 4231962 (14: nrow: 479 rrow: 479)

leaf: 0x40931b 4231963 (15: nrow: 479 rrow: 479)

leaf: 0x40931c 4231964 (16: nrow: 479 rrow: 479)

leaf: 0x40931d 4231965 (17: nrow: 471 rrow: 471)

leaf: 0x40931e 4231966 (18: nrow: 449 rrow: 449)

leaf: 0x40931f 4231967 (19: nrow: 449 rrow: 449)

leaf: 0x409320 4231968 (20: nrow: 449 rrow: 449)

leaf: 0x409321 4231969 (21: nrow: 449 rrow: 449)

leaf: 0x409322 4231970 (22: nrow: 449 rrow: 449)

leaf: 0x409323 4231971 (23: nrow: 448 rrow: 448)

leaf: 0x409324 4231972 (24: nrow: 448 rrow: 448)

leaf: 0x409325 4231973 (25: nrow: 449 rrow: 449)

leaf: 0x409326 4231974 (26: nrow: 449 rrow: 449)

leaf: 0x409327 4231975 (27: nrow: 404 rrow: 404)

看到BLOCK信息发送了变化，REBUILD前第一个BLOCK的位置是37481，而REBUILD后改为37641。

结论：在数据没有发生变化的条件，REBUILD一个UNUSABLE的索引，BLOCK还是会改变的。

-THE END-