关于数据库 Block 存储细节问题的讨论

Yong Huang

You're right. Thanks for the correction. I definitely read that part of Tom's and others' writing but forgot. Your test is not exactly what I want but I can prove it. Basically, I'd like to see one index entry taking one block. So I use a database with smaller block size (you can use a tablespace with smaller block size). Something like this:

create table t (x char(2000));
create index ind_t on t (x);
insert into t values ('a');
insert into t values ('b');
insert into t values ('c');
analyze index ind_t validate structure;

SQL> select lf_blks,br_blks,btree_space from index_stats;

     LF_BLKS      BR_BLKS  BTREE_SPACE
------------ ------------ ------------
           3            1        15648

delete from t;
analyze index ind_t validate structure;

SQL> select lf_blks,br_blks,btree_space from index_stats;

     LF_BLKS      BR_BLKS  BTREE_SPACE
------------ ------------ ------------
           3            1        15648

insert into t values ('d');
insert into t values ('e');
insert into t values ('f');
analyze index ind_t validate structure;

SQL> select lf_blks,br_blks,btree_space from index_stats;

     LF_BLKS      BR_BLKS  BTREE_SPACE
------------ ------------ ------------
           3            1        15648

So it still uses 3 leaf blocks.

Yong Huang

freas

hi,Yong Huang

      Your test is very clear,and can directly prove it...

      and I found an interesting phenomenon in it,when i did a little changing.


...created a tablespace with blocksize 4k...

SQL> create table t (x char(2000));

表已创建。

SQL> create index ind_t on t (x);

索引已创建。

SQL> insert into t values ('a');

已创建 1 行。

SQL> insert into t values ('b');

已创建 1 行。

SQL> insert into t values ('c');

已创建 1 行。

SQL> analyze index ind_t validate structure;

索引已分析

SQL> select lf_blks,br_blks,btree_space from index_stats;

   LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- -----------
         3          1       15648

SQL> delete from t;

已删除3行。

SQL> select lf_blks,br_blks,btree_space from index_stats;

   LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- -----------
         3          1       15648

                                                                                                    ——>I did not do analyzing at this point
                                                                                                    ——>so the space in the leaf block can not be reused in the same transaction.

SQL> insert into t values ('d');

已创建 1 行。

SQL> insert into t values ('e');

已创建 1 行。

SQL> insert into t values ('f');

已创建 1 行。

SQL> analyze index ind_t validate structure;

索引已分析

SQL> select lf_blks,br_blks,btree_space from index_stats;

   LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- -----------
         6          1       27360                                               ——>the num of leaf block is 6,it directly proves the space in the leaf block is not reused.




                 freas
                 junior

yangzhao8587

新人报道，学习一下

Yong Huang

原帖由 freas 于 2011-7-12 19:11 发表
...

SQL> select lf_blks,br_blks,btree_space from index_stats;

   LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- -----------
         6          1       27360                                               ——>the num of leaf block is 6,it directly proves the space in the leaf block is not reused.

I noticed that (but I didn't post it here yesterday). If the transaction is not committed, the space will be preserved by the current transaction in case of rollback. That's not unique to index blocks. Table blocks should behave like that too.

Analyze is a DDL which implicitly commits, although it doesn't update xxx_objects.last_ddl_time as most other DDL's do.

Yong Huang