Extent sizes in table spaces

An extent is a block of storage within a table space container. It represents the number of pages of data that will be written to a container before writing to the next container. When you create a table space, you can choose the extent size based on your requirements for performance and storage management.

When selecting an extent size, consider:

• The size and type of tables in the table space.

  Space in DMS table spaces is allocated to a table one extent at a time. As the table is populated and an extent becomes full, a new extent is allocated. DMS table space container storage is pre-reserved which means that new extents are allocated until the container is completely used.

  Space in SMS table spaces is allocated to a table either one extent at a time or one page at a time. As the table is populated and an extent or page becomes full, a new extent or page is allocated until all of the extents or pages in the file system are used. When using SMS table spaces, multipage file allocation is allowed. Multipage file allocation allows extents to be allocated instead of a page at a time.

  Multipage file allocation is enabled by default. The value of the multipage_alloc database configuration parameter indicate whether multipage file allocation is enabled.

  Note: Multipage file allocation is not applicable to temporary table spaces.
  A table is made up of the following separate table objects:

  • A data object. This is where the regular column data is stored.
  • An index object. This is where all indexes defined on the table are stored.
  • A long field (LF) data object. This is where long field data, if your table has one or more LONG columns, is stored.
  • Two large object (LOB) data objects. If your table has one or more LOB columns, they are stored in these two table objects:
    • One table object for the LOB data
    • A second table object for metadata describing the LOB data.
  • A block map object for multidimensional clustering (MDC) tables.
  • An extra XDA object, which stores XML documents.

  Each table object is stored separately, and each object allocates new extents as needed. Each DMS table object is also paired with a metadata object called an extent map, which describes all of the extents in the table space that belong to the table object. Space for extent maps is also allocated one extent at a time. Therefore, the initial allocation of space for an object in a DMS table space is two extents. (The initial allocation of space for an object in an SMS table space is one page.)

  If you have many small tables in a DMS table space, you might have a relatively large amount of space allocated to store a relatively small amount of data. In such a case, specify a small extent size. However, if you have a very large table that has a high growth rate, and you are using a DMS table space with a small extent size, you might needlessly allocate additional extents more frequently.

• The type of access to the tables.

  If access to the tables includes many queries or transactions that process large quantities of data, prefetching data from the tables might provide significant performance benefits.

• The minimum number of extents required.

  If there is not enough space in the containers for five extents of the table space, the table space is not created.