index-PCT USED

SQL> create table indsplit as select object_id from dba_objects;

Table created

Executed in 0.75 seconds

SQL> create index ind_split on indsplit (object_id) pctfree 1;

Index created

Executed in 0.093 seconds

SQL> analyze index ind_split validate structure;

Index analyzed

Executed in 0.016 seconds

SQL> select * from index_stats;

    HEIGHT     BLOCKS NAME                           PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         2         32 IND_SPLIT                                                          11433         22      173114       8000         21          1         234       8032           0               0         11433                 1      184032     173348         95            1                    3          0            0              0                0

Executed in 0.047 seconds
-- 22个叶节点，pct_use 95
SQL> SELECT object_id FROM user_objects WHERE object_name = 'IND_SPLIT';

OBJECT_ID
----------
     13675

Executed in 0.031 seconds

SQL > alter session set events 'immediate trace name treedump level 13675'
  2  /

----- begin tree dump
branch: 0x100028c 16777868 (0: nrow: 22, level: 1)
   leaf: 0x100028d 16777869 (-1: nrow: 534 rrow: 534)
   leaf: 0x100028e 16777870 (0: nrow: 528 rrow: 528)
   leaf: 0x100028f 16777871 (1: nrow: 528 rrow: 528)
   leaf: 0x1000290 16777872 (2: nrow: 528 rrow: 528)
   leaf: 0x1000291 16777873 (3: nrow: 528 rrow: 528)
   leaf: 0x1000292 16777874 (4: nrow: 528 rrow: 528)
   leaf: 0x1000293 16777875 (5: nrow: 528 rrow: 528)
   leaf: 0x1000294 16777876 (6: nrow: 528 rrow: 528)
   leaf: 0x1000295 16777877 (7: nrow: 528 rrow: 528)
   leaf: 0x1000296 16777878 (8: nrow: 528 rrow: 528)
   leaf: 0x1000297 16777879 (9: nrow: 528 rrow: 528)
   leaf: 0x1000298 16777880 (10: nrow: 528 rrow: 528)
   leaf: 0x100029a 16777882 (11: nrow: 528 rrow: 528)
   leaf: 0x100029b 16777883 (12: nrow: 528 rrow: 528)
   leaf: 0x100029c 16777884 (13: nrow: 528 rrow: 528)
   leaf: 0x100029d 16777885 (14: nrow: 528 rrow: 528)
   leaf: 0x100029e 16777886 (15: nrow: 528 rrow: 528)
   leaf: 0x100029f 16777887 (16: nrow: 528 rrow: 528)
   leaf: 0x10002a0 16777888 (17: nrow: 501 rrow: 501)
   leaf: 0x10002a1 16777889 (18: nrow: 495 rrow: 495)
   leaf: 0x10002a2 16777890 (19: nrow: 495 rrow: 495)
   leaf: 0x10002a3 16777891 (20: nrow: 432 rrow: 432)
----- end tree dump

--22个叶节点，每个大约500个key.

SQL> insert into indsplit values (500);

1 row inserted

Executed in 0.016 seconds

SQL> insert into indsplit values (500);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (500);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (500);

1 row inserted

Executed in 0.015 seconds

SQL> insert into indsplit values (500);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (500);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (500);

1 row inserted

Executed in 0.016 seconds

SQL> insert into indsplit values (500);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (1000);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (1000);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (1000);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (1000);

1 row inserted

Executed in 0.016 seconds

SQL> insert into indsplit values (1000);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (1000);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (1000);

1 row inserted

Executed in 0 seconds

SQL> insert into indsplit values (1000);

1 row inserted

Executed in 0 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

--在前两个节点上，插入一些值

SQL > alter session set events 'immediate trace name treedump level 13675'
  2  /

*** 2009-06-11 15:19:33.202
----- begin tree dump
branch: 0x100028c 16777868 (0: nrow: 24, level: 1)
   leaf: 0x100028d 16777869 (-1: nrow: 278 rrow: 278)
   leaf: 0x10002a7 16777895 (0: nrow: 264 rrow: 264)
   leaf: 0x100028e 16777870 (1: nrow: 272 rrow: 272)
   leaf: 0x10002a8 16777896 (2: nrow: 264 rrow: 264)
   leaf: 0x100028f 16777871 (3: nrow: 528 rrow: 528)
   leaf: 0x1000290 16777872 (4: nrow: 528 rrow: 528)
   leaf: 0x1000291 16777873 (5: nrow: 528 rrow: 528)
   leaf: 0x1000292 16777874 (6: nrow: 528 rrow: 528)
   leaf: 0x1000293 16777875 (7: nrow: 528 rrow: 528)
   leaf: 0x1000294 16777876 (8: nrow: 528 rrow: 528)
   leaf: 0x1000295 16777877 (9: nrow: 528 rrow: 528)
   leaf: 0x1000296 16777878 (10: nrow: 528 rrow: 528)
   leaf: 0x1000297 16777879 (11: nrow: 528 rrow: 528)
   leaf: 0x1000298 16777880 (12: nrow: 528 rrow: 528)
   leaf: 0x100029a 16777882 (13: nrow: 528 rrow: 528)
   leaf: 0x100029b 16777883 (14: nrow: 528 rrow: 528)
   leaf: 0x100029c 16777884 (15: nrow: 528 rrow: 528)
   leaf: 0x100029d 16777885 (16: nrow: 528 rrow: 528)
   leaf: 0x100029e 16777886 (17: nrow: 528 rrow: 528)
   leaf: 0x100029f 16777887 (18: nrow: 528 rrow: 528)
   leaf: 0x10002a0 16777888 (19: nrow: 501 rrow: 501)
   leaf: 0x10002a1 16777889 (20: nrow: 495 rrow: 495)
   leaf: 0x10002a2 16777890 (21: nrow: 495 rrow: 495)
   leaf: 0x10002a3 16777891 (22: nrow: 432 rrow: 432)
----- end tree dump

--前两个节点，55分裂了，多用了2个块，每个块都只是用了50%
SQL> analyze index ind_split validate structure;

Index analyzed

Executed in 0.016 seconds

SQL> select * from index_stats;

    HEIGHT     BLOCKS NAME                           PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         2         32 IND_SPLIT                                                          11449         24      173338       8000         23          1         256       8032           0               0         11433                 9      200032     173594         87 1.0013994577     3.00069972885507          0            0              0                0

Executed in 0.047 seconds

--索引自动平衡后变成24个节点，pct_used也降低了。

假如我们随机的向每个叶节点插入一个值，导致每个节点都分裂，那么就会是节点增多一倍，然后我们以后，都是有序插入，那么有可能许多节点都是50%的使用量，这个时候，如果rebuild，就会合并节点，减少节点数。提高pct_used.

SQL> alter index ind_split rebuild;

Index altered

Executed in 0.219 seconds

SQL> analyze index ind_split validate structure;

Index analyzed

Executed in 0.016 seconds

SQL> select * from index_stats;

    HEIGHT     BLOCKS NAME                           PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
         2         32 IND_SPLIT                                                          11449         22      173338       8000         21          1         234       8032           0               0         11433                 9      184032     173572         95 1.0013994577     3.00069972885507          0            0              0                0

Executed in 0.047 seconds


*** 2009-06-11 15:38:29.525
----- begin tree dump
branch: 0x10002ac 16777900 (0: nrow: 22, level: 1)
   leaf: 0x10002ad 16777901 (-1: nrow: 535 rrow: 535)
   leaf: 0x10002ae 16777902 (0: nrow: 528 rrow: 528)
   leaf: 0x10002af 16777903 (1: nrow: 528 rrow: 528)
   leaf: 0x10002b0 16777904 (2: nrow: 528 rrow: 528)
   leaf: 0x10002b1 16777905 (3: nrow: 528 rrow: 528)
   leaf: 0x10002b2 16777906 (4: nrow: 528 rrow: 528)
   leaf: 0x10002b3 16777907 (5: nrow: 528 rrow: 528)
   leaf: 0x10002b4 16777908 (6: nrow: 528 rrow: 528)
   leaf: 0x10002b5 16777909 (7: nrow: 528 rrow: 528)
   leaf: 0x10002b6 16777910 (8: nrow: 528 rrow: 528)
   leaf: 0x10002b7 16777911 (9: nrow: 528 rrow: 528)
   leaf: 0x10002b8 16777912 (10: nrow: 528 rrow: 528)
   leaf: 0x10002ba 16777914 (11: nrow: 528 rrow: 528)
   leaf: 0x10002bb 16777915 (12: nrow: 528 rrow: 528)
   leaf: 0x10002bc 16777916 (13: nrow: 528 rrow: 528)
   leaf: 0x10002bd 16777917 (14: nrow: 528 rrow: 528)
   leaf: 0x10002be 16777918 (15: nrow: 528 rrow: 528)
   leaf: 0x10002bf 16777919 (16: nrow: 528 rrow: 528)
   leaf: 0x10002c0 16777920 (17: nrow: 501 rrow: 501)
   leaf: 0x10002c1 16777921 (18: nrow: 495 rrow: 495)
   leaf: 0x10002c2 16777922 (19: nrow: 495 rrow: 495)
   leaf: 0x10002c3 16777923 (20: nrow: 447 rrow: 447)
----- end tree dump

--------------------------------------------------------------------------------------------------

analyze index xxxx validate structure;

select height,lf_rows,del_lf_rows,btree_space,used_space,pct_used,del_lf_rows/lf_rows radio from index_stats;

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

转载于:http://blog.itpub.net/13165828/viewspace-605928/