records_per_block参数的使用

1、BLOCK是数据库中的最小存储和处理单位，包含块本身的头信息数据或PL/SQL代码。RECORDS_PER_BLOCK参数用于设定每个BLOCK中记录数的最大值，其先找到当前表所有BLOCK中容纳的最大行数，并会把这个数字记录到数据字典，以后任何导致BLOCK行数超过这个数字的插入都会被拒绝（插入另一个块中）。

2、不能对空表设定此参数。
3、每个BLOCK中可以包含的记录数的最低下限是2。
4、不能在已经有 bitmap 的表中使用records_per_block参数，也就是说，如果要使用records_per_block参数，必须先alter table xxx minimize records_per_block，然后才能在表上建立索引。
官方解释：

This facility improves the storage performance of bitmap indexes and has a direct
effect on query performance. The way in which bitmap indexes operate is that the
maximum possible number of records that can fit in a block is computed from the
table definition, and a bit allocated for each of these records. This calculated value
may be much larger than any actual value resulting in many unnecessary zero bits at
the end of each block having to be compressed. By detecting the actual value with the
ALTER TABLE command, bitmap storage is improved.
If the row size decreases after the minimization step, poor table storage may result, as
blocks will be restricted to the calculated maximum. The feature is aimed at static
environments, such as data warehouses.
It is not possible to minimize RECORDS_PER_BLOCK if the table has an existing
bitmap index.
The MINIMIZE RECORDS_PER_BLOCK syntax populates TAB$ with a value in the
SPARE1 column. With this syntax, the maximum number of records currently stored
in any data block is recorded. There is currently no view available to query this
column.
A table that is not minimized will have a value in TAB$.SPARE1. The value varies
depending on block size, for example, it is 178 for a 2 KB block, and 736 for an 8 KB
block.
测试过程：
1、SQL> create table test(id int,name varchar2(10));

Table created.

SQL> alter table test minimize records_per_block;
alter table test minimize records_per_block
*
ERROR at line 1:
ORA-28603: statement not permitted on empty tables

----表明不能对空表使用此参数
所以接下来，我们往表里插入点具体数：
2、
SQL> BEGIN
2  FOR I IN 1..10 LOOP
3  INSERT INTO test VALUES(1,'test'||I);
4  END LOOP;
5  END;
6  /

PL/SQL procedure successfully completed.

SQL> select * from test;

    ID NAME
     1 test1
     1 test2
     1 test3
     1 test4
     1 test5
     1 test6
     1 test7
     1 test8
     1 test9
     1 test10

10 rows selected.

SQL> commit;

Commit complete.

SQL> CREATE BITMAP INDEX IDX_TEST_NAME ON TEST(NAME);

Index created.

SQL> alter table test minimize records_per_block;
alter table test minimize records_per_block
*
ERROR at line 1:
ORA-28602: statement not permitted on tables containing bitmap indexes

---表明在已经有 bitmap 的表中不能使用records_per_block参数。
SQL> drop index IDX_TEST_NAME;

Index dropped.

SQL> create index IDX_TEST_NAME ON TEST(NAME);

Index created.

SQL> alter table test minimize records_per_block;

Table altered.

----如果存在b 树索引，就没有事；普通表，没索引的，也可以使用此参数。
3、SQL> select dbms_rowid.rowid_block_number(rowid),count(*) from test group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

---

                            1439         10

我们可以看到，10行数据，全都集中在一个Block，
这时候，我们都知道，如果没有minimize records_per_block，那么后面继续插入的数据，还会在1439号block，
但是由于我们使用了minimize records_per_block，可以观察一下，继续插入后的情况：

SQL> BEGIN
2  FOR I IN 1..10 LOOP
3  INSERT INTO test VALUES(1,'test'||I);
4  END LOOP;
5  END;
6  /

PL/SQL procedure successfully completed.

SQL> BEGIN
2  FOR I IN 1..10 LOOP
3  INSERT INTO test VALUES(1,'test'||I);
4  END LOOP;
5  END;
6  /

PL/SQL procedure successfully completed.

SQL> BEGIN
2  FOR I IN 1..11 LOOP
3  INSERT INTO test VALUES(1,'test'||I);
4  END LOOP;
5  END;
6  /

PL/SQL procedure successfully completed.

SQL> select dbms_rowid.rowid_block_number(rowid),count(*) from test group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

---

                            1436         10
                            1439         10
                            1435         10
                            1437         10
                            1438          1

这里我们可以看见，所有的已使用的block中，最多只有10条记录，
也就是使用minimize records_per_block之前的块中最大记录数，最后一次的插入，我故意插入了11条，就是为了能够看的更清楚。即使是11条，由于之前最大记录数是10条，所以一个块中最多只能存10条，另外一条数据数据存储在另一个块中，分开了。
所以records_per_block能够限定表中每个块的最大大小
4、
SQL> drop table test purge;

Table dropped.

SQL> create table test(id int,name varchar2(10));

Table created.

SQL> insert into test values(1,'aaa');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table test minimize records_per_block;

Table altered.

SQL> select dbms_rowid.rowid_block_number(rowid),count(*) from test group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

---

                            1439          1

SQL> BEGIN
2  FOR I IN 1..10 LOOP
3  INSERT INTO test VALUES(1,'test'||I);
4  END LOOP;
5  END;
6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_block_number(rowid),count(*) from test group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

---

                            1436          2
                            1439          2
                            1435          2
                            1437          2
                            1438          2
                            1443          1

6 rows selected.

---使用records_per_block参数之前，表里只有一条数据，存储在1443块中，然后使用records_per_block参数，插入10条数据，并不是每个块中存一条数据，而是2条，所以，records_per_block最小值为2。