Row chaining most often occurs with large rows such as rows that contain a

Row chaining most often occurs with large rows such

This preview shows page 259 - 263 out of 370 pages.

(one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of data type 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, the Oracle Database server migrates the data for the entire row to a new data block, assuming that the entire row can fit in a new block. The database 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, input/output (I/O) performance associated with this row decreases because the Oracle Database server must scan more than one data block to retrieve the information for the row. The Segment Advisor finds the segments containing migrated rows that result from an UPDATE . Oracle Database 12 c : Administration Workshop 8 - 5 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Row Chaining and Migration Example: On update : Row length increases, exceeding the available free space in the block. Data needs to be stored in a new block. Original physical identifier of row ( ROWID ) is preserved. The Oracle Database server needs to read two blocks to retrieve data. The Segment Advisor finds segments containing the migrated rows. There is automatic coalescing of fragmented free space inside the block. Old Original block with pointer to migrated row New data
Image of page 259
The Oracle Database server automatically and transparently coalesces the free space of a data block when: An INSERT or UPDATE statement attempts to use a block with sufficient free space for a new row piece The free space is fragmented, so that the row piece cannot be inserted in a contiguous section of the block After coalescing, the amount of free space is identical to the amount before the operation, but the space is now contiguous. Oracle Database 12 c : Administration Workshop 8 - 6
Image of page 260
Answer: a Oracle Database 12 c : Administration Workshop 8 - 7 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Quiz When a row is chained or migrated, the I/O performance associated with this row decreases because the Oracle Database server must scan more than one data block to retrieve the information for the row. a. True b. False
Image of page 261
Free space can be managed automatically inside database segments. The in-segment free or used space is tracked with bitmaps. To take advantage of this feature, specify Automatic Segment Space Management when you create a locally managed tablespace. Your specification then applies to all segments subsequently created in this tablespace.
Image of page 262
Image of page 263

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture