Oracle datablock Structure

First, extract the official document and get a dump content to verify it.

I. data block format

Data Block Format

The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 2-2illustrates the format of a data block.

Figure 2-2 Data Block Format

Description of "Figure 2-2 Data Block Format"

Header (Common and Variable)

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

Table Directory

This portion of the data block contains information about the table having rows in this block.

Row Directory

This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area ).

After the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. oracle reuses this space only when new rows are inserted in the block.

Overhead

The data block header, table directory, and row directory are referred to collectivelyOverhead. Some block overhead is fixed in 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.

 

========== By the way, I mentioned the line link and line migration.

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 when it is first inserted. in this case, Oracle stores the data for the row inChainOf 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 datatypeLONGOrLONG 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, OracleMigratesThe 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.

 

Ii. record format

Row Format and Size

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 if an update to an existing row causes the row to outgrow its data block, then 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 isChainedAcross multiple blocks.

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is calledIntra-block chaining. A chained row's pieces are chained together using the rowids of the pieces. with intra-block chaining, users receive all the data in the same block. if the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

Each row piece, chained or unchained, containsRow headerAnd data for all or some of the row's columns. Individual columns can also span row pieces and, consequently, data blocks. Figure 5-3 shows the format of a row piece:

Figure 5-3 The Format of a Row Piece

Description of "Figure 5-3 The Format of a Row Piece"

TheRow headerPrecedes the data and contains information about:

• Row pieces

• Chaining (for chained row pieces only)

• Columns in the row piece

• 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.

Note:

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.

 

========== The expression of NULL is mentioned here:

Nulls Indicate Absence of Value

ANullIs the absence of a value in a column of a row. nulls indicate missing, unknown, or inapplicable data. A null shoshould not be used to imply any other value, such as zero. A column allows nulls unlessNOT NULLOrPRIMARY KEYIntegrity constraint has been defined for the column, in which case no row can be inserted without a value for that column.

Nulls are stored in the database if they fall between columns with data values. In these cases they require 1 byte to store the length of the column (zero ).

Trailing nulls in a row require no storage because a new row header signals that the remaining columns in the previous row are null. for example, if the last three columns of a table are null, no information is stored for those columns. in tables with specified columns, the columns more likely to contain nulls shocould be defined last to conserve disk space.

Most comparisons between nulls and other values are by definition neither true nor false, but unknown. To identify nulls in SQL, useIS NULLPredicate. Use the SQL functionNVLTo convert nulls to non-null values.

Nulls are not indexed, cannot t when the cluster key column value is null or the index is a bitmap index.