Given: Suppose an 8K (8,192 bytes) block has six rows each exactly 1,000 bytes for a total of 6,000 bytes. The six rows are end-on-end from row 1 to row 6 with no space between them. Assume the block header is no more than 192 bytes, so we have (at least) 2,000 logically contiguous bytes free in our block. Assume each row has less than 255 columns. Assume it is not a clustered table, so this block only has rows from a single table. Assume no compression.
When: Row 3 is updated from 1,000 bytes to 1,100 bytes by changing a single column's value (e.g. like a VARCHAR2 column's value). Assume that there are subsequent columns with non-null values in this row.
Then:
- Does Oracle intra-block migrate the entire row 3 from its current location between rows 2 and 4 to now be after row 6 leaving roughly a 1,000 byte gap between row 2 and row 4 (except for row 2's forwarding address to its new location within the block)?
or
- Does Oracle move only a row piece of row 3 to now be after row 6 leaving a gap between the end of one of row 3's row piece and the start of row 4? If so, is the row split based upon the location of the column that was updated. All columns up to the changed column remain in the same location on the block, but all subsequent columns, including the column that was changed, move into the new row piece located after the end of row 6?
or
- Does Oracle split the row into three row pieces and move only the updated column to after the end of row 6 leaving a gap between two of row 3's row pieces where the updated column used to be?
or
- Does Oracle do something else?
Note: This question is for gaining an understanding of the concepts involved rather than trying to solve an actual problem.
Using Oracle 19c Enterprise Edition.
Thank you in advance.