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。