一個unusable 的索引REBUILD後分配的block是否改變

kewin發表於2011-08-29
索引RebuildBloC
特殊條件下，一個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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章