一个unusable 的索引REBUILD后分配的block是否改变

特殊条件下，一个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-

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/40239/viewspace-706118/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40239/viewspace-706118/