Shulou(Shulou.com)06/01 Report--

1. BLOCK is the smallest storage and processing unit in the database, including the header information data or PL/SQL code of the block itself. The RECORDS_PER_BLOCK parameter is used to set the maximum number of records in each BLOCK. It first finds the maximum number of rows contained in all BLOCK in the current table, and records this number in the data dictionary. Any subsequent insertion that causes the number of BLOCK rows to exceed this number will be rejected (inserted into another block).

2. You cannot set this parameter on an empty table.

3. The lowest limit of the number of records that can be included in each BLOCK is 2.

4. You cannot use the records_per_block parameter in a table that already has bitmap, that is, if you want to use the records_per_block parameter, you must alter table xxx minimize records_per_block before indexing the table.

Official explanation:

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.

Testing process:

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

-indicates that this parameter cannot be used on an empty table

So next, let's insert a specific number into the table:

2 、

SQL > BEGIN

2 FOR I IN 1..10 LOOP

3 INSERT INTO test VALUES (1)

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

-indicates that the records_per_block parameter cannot be used in a table that already has bitmap.

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.

-if there is a b-tree index, it's fine; you can also use this parameter for regular tables and those without an index.

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

We can see that 10 rows of data are all concentrated in one Block.

At this time, we all know that if there is no minimize records_per_block, then the data that will be inserted later will be block 1439.

But because we used minimize records_per_block, we can observe what happens after we continue to insert:

SQL > BEGIN

2 FOR I IN 1..10 LOOP

3 INSERT INTO test VALUES (1)

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)

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)

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

Here we can see that of all the block used, there are only 10 records at most.

That is, the maximum number of records in the block before using minimize records_per_block, the last insert, I deliberately inserted 11, in order to see more clearly. Even if there are 11 records, because the previous maximum number of records is 10, a maximum of 10 records can be stored in one block, and the other piece of data is stored in another block, separated.

So records_per_block can limit the maximum size of each block in the table.

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)

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)

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.

-before using the records_per_block parameter, there is only one piece of data in the table, which is stored in 1443 blocks, and then use the records_per_block parameter to insert 10 pieces of data. Instead of storing one piece of data in each block, there are 2 pieces of data. Therefore, the minimum value of records_per_block is 2.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.