In Oracle Database migrated and chained rows are often confused. In my opinion, this is for two main reasons. First, they share some characteristics, so it’s easy to confuse them. Second, Oracle, in its documentation and in the implementation of its software, has never been very consistent in distinguishing them. So, it’s essential to understand the differences between the two.
When rows are inserted into a block, the database engine reserves some free space for future updates. You define the amount of free space reserved for updates by using the PCTFREE parameter. To illustrate, I inserted six rows in the block depicted in the next figure. Since the limit set through PCTFREE has been reached, this block is no longer available for inserts.
When a row is updated and its size increases, the database engine tries to find enough free space in the block where it’s stored. When not enough free space is available, the row is split into two pieces. The first piece (containing only control information, such as a rowid pointing to the second piece) remains in the original block. This is necessary to avoid changing the rowid. Note that this is crucial because rowids might not only be permanently stored in indexes by the database engine, but also be temporarily stored in memory by client applications. The second piece, containing all the data, goes into another block. This kind of row is called a migrated row. For example, in the following figure, row 4 has been migrated.
When a row is too big to fit into a single block, it’s split into two or more pieces. Then, each piece is stored in a different block, and a chain between the pieces is built. This type of row is called a chained row. To illustrate, the next figure shows a row that is chained over three blocks.
There’s a second situation that causes row chaining: tables with more than 255 columns. In fact, the database engine isn’t able to store more than 255 columns in a single row piece. Consequently, whenever more than 255 columns have to be stored, the row is split into several pieces. This situation is particular, in that several pieces belonging to the same row can also be stored in a single block. This is called intra-block row chaining. The following figure shows a row with three pieces (since it has 654 columns).
Note that migrated rows are caused by updates, while chained rows are caused by either inserts or updates. When chained rows are caused by updates, the rows might be migrated and chained at the same time.
Very nice information Sir..
Very good explanation, thanks for that.
Would be interesting to know from your experience and knowledge. What happens exactly when the row is updated for example in that sence its size is increasing , but provided it still fits to the same block(hence no migration is happening, also complete row fits in one piece, also table with no more than 254 columns). To be more concrete, lets assume your very first picture above. Row 5 is updating and enlarging because of update. What will happen: the new version of row will be on his previous place(hence pushing forward Row 6 further into the free space), or it will relocate the free space zone, or none of the mentioned actions will happen. I know according to documentation it is stored in a kind of sequential format, first comes column lenghts, then the data itself etc. But the real internals what is happening during update is not completeley clear. Of course this might be a ‘secret’ of an oracle code, so not expecting from you concrete answer, asked just in case :)
First of all it’s important to point out two basic concepts:
a) The position of the row in a block can change.
b) A row piece is always stored sequentially and can’t be divided.
Provided that enough space is available (and that’s the case based on your description), there are two cases to consider:
a) If the updated row fits in the free space (above row 6 in the first figure), the updated row is moved in the free space.
b) If the updated row doesn’t fit in the free space, the block needs to be reorganized (you can consider such a reorganization a kind of compaction to merge the whole free space available in the block). As a result, both row 5 and row 6 will be moved.
PS: for 2017 I’m thinking about organizing an online class to describe how all these things works…
Thanks for the explanations. So that means in a worst case the majority of rows might get affected with this movement/reorganization, right ?