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/