Setting the Index ( B*Tree / Bitmaps/ Function based ) to unusable state will drop the underlying storage segments. Here is the code piece to demonstrate that.
rajesh@11GR2> create table t(
2 x ,
3 y ,
4 z
5 )
6 nologging
7 as
8 select level as x,
9 rpad('*',40,'*') as y,
10 sysdate as dt
11 from dual
12 connect by level <= 1000000;
Table created.
Elapsed: 00:00:15.31
rajesh@11GR2>
rajesh@11GR2> create unique index t_ind on t(x,z) nologging;
Index created.
Elapsed: 00:00:08.43
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks**************** 3328
l_total_bytes***************** 27262976
l_unused_blocks*************** 58
l_unused_bytes**************** 475136
l_last_used_extent_file_id**** 4
l_last_used_extent_block_id*** 17152
l_last_used_block************* 70
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 1
l_fs2_bytes******************* 8192
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 3209
l_full_bytes****************** 26288128
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.95
rajesh@11GR2>
rajesh@11GR2> alter index t_ind unusable;
Index altered.
Elapsed: 00:00:02.21
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks**************** 0
l_total_bytes***************** 0
l_unused_blocks*************** 0
l_unused_bytes**************** 0
l_last_used_extent_file_id**** 0
l_last_used_extent_block_id*** 0
l_last_used_block************* 0
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 0
l_full_bytes****************** 0
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> drop index t_ind;
Index dropped.
Elapsed: 00:00:00.15
rajesh@11GR2> create bitmap index t_ind on t(z) nologging;
Index created.
Elapsed: 00:00:02.15
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks**************** 32
l_total_bytes***************** 262144
l_unused_blocks*************** 4
l_unused_bytes**************** 32768
l_last_used_extent_file_id**** 4
l_last_used_extent_block_id*** 4784
l_last_used_block************* 4
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 1
l_fs2_bytes******************* 8192
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 23
l_full_bytes****************** 188416
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
rajesh@11GR2> alter index t_ind unusable;
Index altered.
Elapsed: 00:00:00.04
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks**************** 0
l_total_bytes***************** 0
l_unused_blocks*************** 0
l_unused_bytes**************** 0
l_last_used_extent_file_id**** 0
l_last_used_extent_block_id*** 0
l_last_used_block************* 0
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 0
l_full_bytes****************** 0
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> drop index t_ind;
Index dropped.
Elapsed: 00:00:00.10
rajesh@11GR2> create index t_ind on t(x,0) nologging;
Index created.
Elapsed: 00:00:04.82
rajesh@11GR2> SELECT index_name, index_type
2 FROM user_indexes
3 WHERE index_name ='T_IND'
4 /
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
T_IND FUNCTION-BASED NORMAL
Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks**************** 2560
l_total_bytes***************** 20971520
l_unused_blocks*************** 2
l_unused_bytes**************** 16384
l_last_used_extent_file_id**** 4
l_last_used_extent_block_id*** 16384
l_last_used_block************* 126
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 1
l_fs2_bytes******************* 8192
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 2509
l_full_bytes****************** 20553728
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> alter index t_ind unusable;
Index altered.
Elapsed: 00:00:00.01
rajesh@11GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks**************** 0
l_total_bytes***************** 0
l_unused_blocks*************** 0
l_unused_bytes**************** 0
l_last_used_extent_file_id**** 0
l_last_used_extent_block_id*** 0
l_last_used_block************* 0
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 0
l_fs2_bytes******************* 0
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 0
l_full_bytes****************** 0
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
rajesh@11GR2>
No comments:
Post a Comment