Data block Structure and Row Piece Structure

目标：搞清Data Block 与 Row Piece的物理结构，理解Oracle 如何存储行数据

参考文档

P118<>

P260<>

目录

1 Data Block Format

2 Data Block Free Space Management

3 释放的空间可以被重用的条件

4 Oracle coalesces the free space of a data block only when(Coalesces空间的条件)

5 Row Chaining and Migrating

6 PCTFREE, PCTUSED, and Row Chaining

7 How table data is stored

8 Row Piece Format

9 Rowids of Row Pieces

10 Column Order

11 测试

11.1 正常情况下， 只占了一个row piece

11.2 当columns 个数大于等于256时，出现的分成两个row pieces的情形.

11.3 测试column 小于 256时单行数据跨块的情形

12 Notes

1 Data Block Format

The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data.

Block Header: The header contains general block information. Such as the block address and the type of segment (for example, data or index).

Table Directory: contains information about the table who have rows in this block.

Row Directory: contains information about the actual rows in the block (including address of each row piece in the data area). Once the space was allocated in the row directory, this space is not reclaim(回收)even the row is deleted. Oracle reuses(重用) the space only when new rows are inserted in the block.

Overhead: The data block header, table directory and row directory are referred to collectively as overhead. Some block overhead is fixed size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.

Row data: This portion of the data block contains table or index data. Rows can span blocks

Free space: Free space is allocated for insertion of new rows and for updates to rows that require additional space. Whether issued insertions actually occur in a given data block is a function of current free space in that data block and the value of the space management parameter PCTFREE.

In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. The space required for transaction entries is operating system dependent; however, transaction entries in most operating systems require approximately 23 bytes. (Transaction entries 的固定开销大约23 bytes in Free Space portion)

2 Data Block Free Space Management

Free space can be managed automatically or manually

Auto: The in-segment free/used space is tracked using bitmaps

Manual: The in-segment free/used space is tracked using free lists.

Two types statement can increase the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. (两种释放datablock空间的语句:Delete and update to smaller value. 但释放的空间并不能被立即被重用)

3 释放的空间可以被重用的条件

1) If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available. (在同个事务，且insert紧跟delete操作)

2) If the INSERT statement is in a separate transaction from the statement that frees space (perhaps being run by another user), then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed. (不在同个事务，只有在释放空间的事务提交后，且释放的空间正是申请空间的事务所需的大小)

4 Oracle coalesces the free space of a data block only when(Coalesces空间的条件)

(1) An INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece.

(2) The free space is fragmented so the row piece cannot be inserted in a contiguous section of the block.

Oracle does this compression only in such situations, because otherwise the performance of a database system decreases due to the continuous compression of the free space in data blocks.

5 Row Chaining and Migrating

In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block then it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.

However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block’s free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change. When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.

6 PCTFREE, PCTUSED, and Row Chaining

For manually managed tablespaces, two space management parameters, PCTFREE and PCTUSED, enable you to control the use of free space for inserts and updates to the rows in all the data blocks of a particular segment. Specify these parameters when you create or alter a table or cluster (which has its own data segment). You can also specify the storage parameter

7 How table data is stored

When you create a table, Oracle automatically allocates a data segment in a tablespace to hold the table’s future data. You can control the allocation and use of space for a table’s data segment in the following two ways:

Setting the storage parameters for the data segment.设置segment 参数

Setting the storage parameters for the data block.   设置block参数

（具体的参数细节要参考相应的文档?未整理）

Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece. However, if all of a row’s data cannot be inserted into a single data block or an update to an existing row causes the row to outgrow its data block, Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in more than one row piece, it is chained across multiple blocks.

When a table has more than 255 columns, rows that have data after the 255 column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. (如果一个表小于256列，Oracle将表中的单行数据存储在单个数据块中作为一个Row Piece，如果由于初始太大或者由于更新导致太大，导致不能在单个数据中存放时，Oracle会将多余的数据存放在另一个Block中，这样就生多个Row Pieces，从而形成Chain,但仍有一个Rowid. A data block usually contains only one row piece for each table row data. Chain spans multiple blocks. 如果一个表大于256列，则第255列之后的数据可能被chained within the same block.这就是 intra-block chaining，而不会span multiple blocks。）

8 Row Piece Format

The row header precedes the data and contains information about:

1)     Row pieces

2)     Chaining(for chained row pieces only)

3)     Columns in the row piece

4)     Cluster keys(for clustered data only)

A row fully contained in one block has at least 3 bytes of row header. After the row header information, each row contains column length and data. The column length requires 1 byte for columns that store 250 bytes or less, or 3 bytes for columns that store more than 250 bytes, and precedes the column data. Space required for column data depends on the datatype. If the datatype of a column is variable length, then the space required to hold a value can grow and shrink with updates to the data.

To conserve space, a null in a column only stores the column length (zero). Oracle does not store data for the null column. Also, for trailing null columns, Oracle does not even store the column length. (Each row also uses 2 bytes in the data block header’s row directory,见第一个图)

Clustered rows contain the same information as nonclustered rows. In addition, they contain information that references the cluster key to which they belong. (Cluster rows包含cluster key信息)

9 Rowids of Row Pieces

The rowed identifies each piece by its location or address. After they are assigned, a given row piece retains its rowed until the corresponding row is deleted or exported and imported using the Export and Import Utilities. Because rowids are constant for the lifetime of a row piece, it is useful to refer rowid in SQL statements such as SELECT, UPDATE, and DELETE.(rowid 是一个常量)

10 Column Order

The column order is the same as they were listed in the CREATE TABLE statement, but this is not guaranteed. Long type column is always stored in the last. To conserve space, a null in a column only stores the column length (zero). Oracle does not store data for the null column. Also, for trailing null columns, Oracle does not even store the column length. So try to place columns that frequently contain nulls last. (Column Order 顺序没保证)

11 测试

11.1 正常情况下， 只占了一个row piece

11.2 当columns 个数大于等于256时，出现的分成两个row pieces的情形.即intra-block chaining

可知在 block id in (14369-14376)上只有一个 Segment

查看DUMP文件

从上面的DUMP文件可以看出，一个row分成了两个row piece

tab 0, row 1, @0x1bc9

tl: 24 fb: --H-F--- lb: 0x1  cc: 5

nrid:  0x04000196.0

看上面的fb，标示为H和F，H代表Head piece of row ，F代表First data piece

nrid:  0x04000196.0        下一个row piece的地址

tab 0, row 0, @0x1be1

tl: 927 fb: -----L-- lb: 0x1  cc: 255

看上面的fb，标示为L，L代表Last data piece

11.3 测试column Number小于 256列时单行数据跨块的情形

如何测试？参考<中行迁移和行链接的清除及检测_20091210.doc>>

如何查看行数据有没有跨块， 在dump文件中查找nrid标记

查看dump 文件 nrid:  0x0180000e.0 表明有行迁移

12 Notes

1) Table can be define up to 1000 columns the same as views

2） 假定一个8K的data block，有多少个row piece呢，一个row piece多大，开销多大 ？row piece并没有固定的大小，只是指存储的一行数据。参考Data block的结构，有哪些视图可用来查询，什么时候才跨块，是不能在single datablock 存放下时，做测试

3）Default Values for Columns： If a default values is not explicitly defined for a column, then the default for the column is implicitly set to NULL.

4) 一个block中可以存放多个table的数据 ?

不能吧

Block=>extent=>Segment=table

5) 关于Block的详细解析以后再研究

参考<物理结构详解_20091210.doc>>