A popular question people new to Oracle ask involves the location of data in a table; the popular belief by many is that new data always goes at the end of the current data. This may not always be true as such behaviour is governed by the type of insert and by a mechanism involving two often misunderstood parameters, PCTFREE and PCTUSED.
How does the type of insert govern where the data resides? Using the /*+ append */ hint causes Oracle to do just that: append the data to the end of the current set, populating new blocks in the process. Such a process bypasses the pctfree/pctused mechanism and thus makes for a speedier insert of data as Oracle need not access the freelists to find possible locations for the new data amongst the old. Should a ‘plain vanilla’ insert be executed Oracle goes about the task of accessing the freelists (at least one for each block) to find and use (if possible) free space in already populated data blocks. The locations on these freelists is governed by two parameters mentioned earlier: PCTFREE and PCTUSED. So, what are these parameters and how do they work to trigger inserts into populated data blocks? Let’s start with PCTFREE and discover its purpose.
PCTFREE sets the threshold, indirectly, for the percentage of the data block which can contain new data. 100 – PCTFREE returns the percentage of the data block new data can occupy; the rest (PCTFREE) is reserved for updates to existing data in that block. So, for example, the default value for PCTFREE (10) reserves 10 percent of the available data block space for updates, and provides 90 percent for data inserts.
But, wait! Simply because PCTFREE is set to 10 doesn’t mean that when the block falls below 90% populated Oracle will automatically insert new data in that block. There is another parameter, PCTUSED, which governs when a populated data block can be considered available to accept new data. PCTUSED is the threshold which controls when a populated data block can be used for inserts; if this parameter is not explicitly set when a table is created it defaults to a value of 40. When the percentage of the block populated by data reaches or becomes less than PCTUSED that block is placed on the freelist. Oracle accesses the freelist during inserts to find available locations to store data; of course simply being available doesn’t mean Oracle automatically uses these locations as there may be insufficient space in the block to store the row or rows in question. The PCTUSED threshold triggers the access of the freelist during inserts; this threshold is evaluated for every populated block in a table, so there may be a number of blocks on the freelists for Oracle to search. Other populated blocks which haven’t met the PCTUSED setting will be bypassed.
I know, this sounds confusing. Let’s look at an example and see how this mechanism operates; for the purposes of this example we’ll set db_block_size to 8192, PCTFREE to 10 and PCTUSED to 50:
We have a brand new, never before used table into which we’ll insert data. What data is not important at this point, the issue is to discuss the mechanism.
Inserting data into this table for the first time provides us with 90% of the available block space for new rows. Estimating the total available space in an 8k block to be 8000 bytes (192 bytes for the header and other overhead) we have 7200 bytes available for new rows per block. As we insert data Oracle notes how much space each row occupies and how much of the 7200 bytes remains for the next insert. New data stops flowing into a given block when either the 7200 byte limit has been reached or when the next row which could be inserted into the block would put that block over the 7200 byte limit. Oracle disallows any new inserts to that block and proceeds to populate a new block subject to the same conditions. [PCTFREE and PCTUSED are set at the table level and apply to all blocks associated with that table; different tables can have differing PCTFREE/PCTUSED settings.] To confuse matters further since the block isn’t fully populated according to PCTFREE (presuming the 7200 byte limit hasn’t been reached) another insert, with a shorter row, can add that row to this same block because even though it wouldn’t take the larger insert the block has not yet been taken off of the freelist. It’s only removed from the freelist when the occupied space meets the 100 – PCTFREE setting.
Once a block is populated to the PCTFREE threshold (leaving no less than PCTFREE available for updates) no new data can enter that block via an insert until the populated percentage is equal to or is less than the PCTUSED threshold. In our populated data block we now delete some rows, leaving 60% of the block populated. As this is not less than the 50 to which PCTUSED is set Oracle does not make this location available to the next insert. Further deletions reduce that percentage further, until we reach 49% population of the data block. We have now reached the point where the existing block is put on the freelist and made available to Oracle for the next round of inserts. Presuming the incoming data will fit in the holes we left behind Oracle will reuse these locations until, you guessed it, the free space reaches the PCTFREE threshold (or, as stated earlier, no row will fit in the available space remaining). At this point the process starts over again, removing the block from the available list until such time as its populated space is at or below the setting for PCTUSED.
In a nutshell (so to speak):
Data inserted into block until PCTFREE threshold is reached.
Data block removed from consideration until deletes put the used space at or below PCTUSED.
Data block again available for inserts until PCTFREE threshold is reached.
And this repeats until the table no longer exists, or is placed in a read-only tablespace, or is archived to history … I think you get the idea.
This, of course, means there can be no ‘order’ to the rows in a heap table (the default table type in Oracle) since new data can be inserted in the middle of existing data which may be months old. The ROWID tells the tale of where this data is stored, and if the table contains a date column to track when data changes occur you will, more likely than not in an active table, find that newer data has ROWID values which once belonged to data originally loaded into the table and since deleted. So asking for, say, row 342 in a table is … silly, because a heap is not an ordered list.
Yes, this topic can be confusing, but patience is rewarded. Read through the text again, and again if necessary. All will be made clear … eventually.