89 - [Oracle 19C] Availability and Optimization of Free Space in a Data Block

 

• Managing Storage Space

Availability and Optimization of Free Space in a Data Block

 

Managing Storage Space
Availability and Optimization of Free Space in a Data Block

Two types of statements can increase the free space of one or more data blocks:
1-DELETE statements
2-UPDATE statements that update existing values to smaller values

The released space from these types of statements is available for subsequent INSERT statements under the following conditions:
 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.
 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.

Oracle Database automatically and transparently coalesces the free space of a data block only when the following conditions are true:
• An INSERT or UPDATE statement attempts to use a block that contains sufficient free
space to contain a new row piece.
• The free space is fragmented so that the row piece cannot be inserted in a
contiguous section of the block.

 

 

 

 

• Managing Storage Space

Row Chaining and Migrating

 

Managing Storage Space
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 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.

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.

Note: 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.