索引的pctfree什么时候有效？

tom曰：
pctfree only counts when BUILDING an index.  It reserves X% of the block for subsequent
inserts into that block (inserts into the block can arise from inserts or UPDATES to the
base table).

All the pctfree does on an index is defer the splitting of an index block due to
subsequent inserts/updates AFTER the index is build.  10% is just a good "starting
point".

可见，与表的pctfree值不同，对于索引来说，其pctfree仅仅是在create或rebuild时生效，对与后续的插入、修改之类的操作来说是无效的，pctfree的设置也仅仅是为了延缓由于insert等操作而导致的的索引块分裂。

测试验证：

SQL> create table t(id int) tablespace users;

Table created.

SQL>  create index idx_t on t(id) tablespace idx_2k;

Index created.

SQL>  select table_name,index_name,pct_free from user_indexes where table_name='T';

TABLE_NAME                     INDEX_NAME                       PCT_FREE
------------------------------ ------------------------------ ----------
T                              IDX_T                                  10

SQL> begin
  2  for i in 1 .. 100000 loop
  3  insert into t values(i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> analyze index idx_t validate structure;

Index analyzed.

SQL> select name,blocks,lf_blks,br_blks,used_space,pct_used from index_stats;

NAME                                                   BLOCKS    LF_BLKS    BR_BLKS USED_SPACE   PCT_USED
-------------------------------------------------- ---------- ---------- ---------- ---------- ----------
IDX_T                                                    1024        864          7    1599166        100

SQL> alter index idx_t rebuild pctfree 10 tablespace idx_2k;

Index altered.

SQL>  analyze index idx_t validate structure;

Index analyzed.

SQL> select name,blocks,lf_blks,br_blks,used_space,pct_used from index_stats;

NAME                                                   BLOCKS    LF_BLKS    BR_BLKS USED_SPACE   PCT_USED
-------------------------------------------------- ---------- ---------- ---------- ---------- ----------
IDX_T                                                    1024        965          8    1600361         89

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

转载于:http://blog.itpub.net/10972173/viewspace-626164/