ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引的pctfree什么时候有效?

索引的pctfree什么时候有效?

原创 Linux操作系统 作者:lsq_008 时间:2010-01-27 16:03:39 0 删除 编辑

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博客 ” ,链接:https://blog.itpub.net/10972173/viewspace-626164/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    335
  • 访问量
    1369143