In Oracle database, table rows are stored in data blocks. Usually a block contains mutiple rows. However, in some cases, a certain row can reside in more than one data block. Also, the rows can moves from a block to another block.
* Row migration -- When the length of a row increases, exceeding the available free space in the block, the entire row moves to a new data block. The system leaves a pointer at the original location of the row. That pointer points to the new migrated location of the row.
* Row Chaining -- Another case is when we insert a very long(large) row that doesn't fit into a single data block. Then, the system stores the rows in a chain of data blocks(one or more)
* As we add, delete and modify data in a table, the rows could be easily spread out(location-wise). We all know the logical structure of the database: Segments, Extents, Data Blocks. A segment is a set of extents. And an extent is a specific number of contiguous data blocks. As we insert more data, delete and update data, the segment can be easily a sparsely populated segment. Then, what is the problem with a sparsely populated segment? We might have to read in more data blocks when we run a query because the rows are spread out across more data blocks. It is especially true for a full table scan. Reading more data blocks could degrade our database performance.
As shown below, we can improve our database performance and increase space utilization after SHRINK segment(space). The tablespace must be in Automatic Segment Space Managed(ASSM) mode, and ROW MOVEMENT attribute of the table should be enabled in order to use the SHRINK feature.
The SHRINK process first moves sparsed rows so that the rows are put together. Then, it adjusts the HWM(the High Water Mark) so that the unused space can be released. With COMPACT option, it allows us to move the rows without adjusting HWM.
No comments:
Post a Comment