[ Pobierz całość w formacie PDF ]
.On average, the fixed and variable portions of datablock overhead total 84 to 107 bytes.Row DataThis portion of the data block contains table or index data.Rows can spanblocks; see  Row Chaining and Migrating on page 2-9.Free SpaceFree space is allocated for insertion of new rows and for updates to rows thatrequire additional space (for example, when a trailing null is updated to a non-null value).Whether issued insertions actually occur in a given data block is afunction of current free space in that data block and the value of the spacemanagement parameter PCTFREE.The next section,  An Introduction toPCTFREE, PCTUSED, and Row Chaining , contains more information onspace management parameters.In data blocks allocated for the data segment of a table or cluster, or for theindex segment of an index, free space can also hold transaction entries.Atransaction entry is required in a block for each INSERT, UPDATE, DELETE,and SELECT.FOR UPDATE statement accessing one or more rows in theblock.The space required for transaction entries is operating systemdependent; however, transaction entries in most operating systems requireapproximately 23 bytes.An Introduction to PCTFREE, PCTUSED, and Row ChainingTwo space management parameters, PCTFREE and PCTUSED, enable you tocontrol the use of free space for inserts of and updates to the rows in all thedata blocks of a particular segment.You specify these parameters whencreating or altering a table or cluster (which has its own data segment).You canalso specify the storage parameter PCTFREE when creating or altering anindex (which has its own index segment).Note: This discussion does not apply to the LOB datatypes (BLOB, CLOB,NCLOB, and BFILE)  they do not use the PCTFREE storage parameter or freelists.See  LOB Datatypes on page 9-9 for more information.The PCTFREE ParameterThe PCTFREE parameter sets the minimum percentage of a data block to bereserved as free space for possible updates to rows that already exist in thatblock.For example, assume that you specify the following parameter within aCREATE TABLE statement:Data Blocks, Extents, and Segments 2-5PCTFREE 20This states that 20% of each data block in this table s data segment will be keptfree and available for possible updates to the existing rows already within eachblock.New rows can be added to the row data area, and correspondinginformation can be added to the variable portions of the overhead area, untilthe row data and overhead total 80% of the total block size.Figure 2-3illustrates PCTFREE.Figure 2-3: PCTFREEData BlockPCTFREE = 2020% Free SpaceBlock allows row insertsuntil 80% is occupied,leaving 20% free for updatesto existing rows in the blockThe PCTUSED ParameterThe PCTUSED parameter sets the minimum percentage of a block that can beused for row data plus overhead before new rows will be added to the block.After a data block is filled to the limit determined by PCTFREE, Oracleconsiders the block unavailable for the insertion of new rows until thepercentage of that block falls below the parameter PCTUSED.Until this valueis achieved, Oracle uses the free space of the data block only for updates torows already contained in the data block.For example, assume that youspecify the following parameter in a CREATE TABLE statement:PCTUSED 402-6 Oracle8 Server ConceptsIn this case, a data block used for this table s data segment is consideredunavailable for the insertion of any new rows until the amount of used spacein the block falls to 39% or less (assuming that the block s used space haspreviously reached PCTFREE).Figure 2-4 illustrates this.Figure 2-4: PCTUSEDData BlockPCTUSED = 4061% FreeSpaceNo new rows areinserted until amountof used space fallsbelow 40%How PCTFREE and PCTUSED Work TogetherPCTFREE and PCTUSED work together to optimize the utilization of space inthe data blocks of the extents within a data segment.Figure 2-5 illustrates theinteraction of these two parameters.Data Blocks, Extents, and Segments 2-7Figure 2-5: Maintaining the Free Space of Data Blocks with PCTFREE andPCTUSED2 Updates toexisiting rowsuse the freespaceRows are1 reserved ininserted up tothe block.80% only,No new rowsbecausecan bePCTFREEinserted intospecifies thatthe block20% of theuntil theblock mustamount ofremain openusedfor updates ofspace is 39%existing rows.or less.4 Rows areinserted up to80% only,because3 After the PCTFREEamount of specifies thatused space 20% of thefalls below block must40%, new remain openrows can for updates ofagain be existing rows.inserted into This cyclethis block.continues.In a newly allocated data block, the space available for inserts is the block sizeminus the sum of the block overhead and free space (PCTFREE) [ Pobierz całość w formacie PDF ]