Free Space and Parallel DDL
When you create indexes and tables in parallel, each parallel execution server allocates a new extent and fills the extent with the table or index data. Thus, if you create an index with a DOP of 4, the index has at least four extents initially. This allocation of extents is the same for rebuilding indexes in parallel and for moving, splitting, or rebuilding partitions in parallel.
Serial operations require the schema object to have at least one extent. Parallel creations require that tables or indexes have at least as many extents as there are parallel execution servers creating the schema object.
When you create a table or index in parallel, it is possible to create areas of free space. This occurs when the temporary segments used by the parallel execution servers are larger than what is needed to store the rows.
-
If the unused space in each temporary segment is larger than the value of the
MINIMUMEXTENTparameter set at the tablespace level, then Oracle Database trims the unused space when merging rows from all of the temporary segments into the table or index. The unused space is returned to the system free space and can be allocated for new extents, but it cannot be coalesced into a larger segment because it is not contiguous space (external fragmentation). -
If the unused space in each temporary segment is smaller than the value of the
MINIMUMEXTENTparameter, then unused space cannot be trimmed when the rows in the temporary segments are merged. This unused space is not returned to the system free space; it becomes part of the table or index (internal fragmentation) and is available only for subsequent insertions or for updates that require additional space.
For example, if you specify a DOP of 3 for a CREATE TABLE AS SELECT statement, but there is only one data file in the tablespace, then internal fragmentation may occur, as shown in Figure 8-5. The areas of free space within the internal table extents of a data file cannot be coalesced with other free space and cannot be allocated as extents.
Oracle Database SQL Tuning Guide for more information about creating tables and indexes in parallel
Figure 8-5 Unusable Free Space (Internal Fragmentation)
Description of "Figure 8-5 Unusable Free Space (Internal Fragmentation)"