Create Table Wizard - Extent Size and Storage Scheme

Use this page to specify the extent size and the storage scheme for a new table.

Extent Size

Enter the extent size or calculate it by using the Extent Size Estimator.

First extent: Specify the length, in kilobytes, of the first extent for the table. The default is 16.
Next extent: Specify the length, in kilobytes, of each subsequent extent. The default is 16.
Extent Size Estimator: Specify the expected number of rows for the table and the expected growth rate (static, medium, or aggressive), and then click Estimate. The first extent size and next extent size for the table are estimated based on the row size of the defined columns.

Storage scheme

Specify a dbspace in which to store the data, or select a fragmentation strategy:

Single dbspace

Select this option to store the data in a single dbspace. Select the dbspace in the list of dbspaces.

Date-range fragmentation

Select this option to create a fragmented table and store the data, based on a date-range interval.

When a new row does not match the date range of an existing fragment, the database server creates a fragment based on the specified interval. You can set the interval by year, month, or day, or by a specific number of years, months, or days.

Fragmentation key: Select the column on which to base the fragmentation. The list displays the columns with the data type DATE. When year is selected as the time period in the Interval field, the list also displays the columns with the data type DATETIME.
Interval: Specify the date-range interval by selecting the length of the interval and the time period: year, month, or day. For example, to store the data by year, select 1 as the length of the interval and Year for the time period. To store the data in six-month periods, select 6 as the length of the interval and Month for the time period.
Start date: Specify the start date for fragmentation by date range. Rows that have a fragmentation key column with an earlier date are stored in a predefined partition.; For the month interval, the start date must be the first day of the month. For the year interval, the start day must be the first day of the year.
Dbspace: Select one or more dbspaces in which to store the data. The list displays the dbspaces available on the database server.; For this fragmentation strategy, all the dbspaces must have the same page size. When a dbspace is selected, only the dbspaces that have the same page size are displayed in the list. To select dbspaces with a different page size, first clear the selected dbspace.
Null Partition: Optional: Select a column in which to store the rows with null values for the fragmentation key.

Range fragmentation

Select this option to create a fragmented table and store the data, based on a range of numeric values other than dates. For dates, use the date-range fragmentation scheme.

When a new row does not match the range of an existing fragment, the database server creates a fragment based on the specified interval.

Fragmentation key: Select the column on which to base the fragmentation. The list displays the columns with numeric values: BIGINT, BIGSERIAL, DECIMAL, FLOAT, INTEGER, MONEY, SERIAL, SMALLINT, and SMALLFLOAT.
Interval: Specify the interval size.
Start value: Specify the start value for fragmentation by range. Rows that have a fragmentation key column with a value that is less than the start value are stored in a predefined partition.
Dbspace: Select one or more dbspaces in which to store the data. The list displays the dbspaces available on the database server.; For this fragmentation strategy, all the dbspaces must have the same page size. When a dbspace is selected, only the dbspaces that have the same page size are displayed in the list. To select dbspaces with a different page size, first clear the selected dbspace.
Null Partition: Optional: Select the space in which to store the rows with null values for the fragmentation key.

List fragmentation

Select this option to create a fragmented table and store the data, based on a list of values.

Rows that have a fragmentation key column with the specified values are stored in the same fragment.

Fragmentation key: Select the column on which to base the fragmentation. The list displays all the columns except columns with these data types: BLOB, CLOB, TEXT, and BYTE.
Dbspace: Select one or more dbspaces in which to store the data. The list displays the dbspaces available on the database server.
Values: To specify the list of values for the dbspace, click the icon in the Values column, and complete the fields on the Add Values page.
Null Partition: Optional: Select a column in which to store the rows with null values for the fragmentation key.
Remainder Partition: Optional: Select the space in which to store the rows that have values for the fragmentation key that do not match any of the specified values.

Round-robin fragmentation

Select this option to create a fragmented table and store the data in two or more dbspaces. Select at least two dbspaces in the list of dbspaces. As records are inserted into the table, they are placed in the first available fragment. The database server balances the load among the specified fragments and distributes the rows so that the fragments maintain approximately the same number of rows.

Expression-based fragmentation

Select this option to create a fragmented table and store the data in the dbspace specified by a fragment expression. To specify the expression, click Add, and complete the following fields on the Expression page:

Fragment name: Type a name for the fragment that is unique among fragment names in the table. The name must follow the guidelines of SQL syntax for an identifier.
Dbspace: Select the dbspace in which to store the fragment. The list displays the dbspaces available on the database server.
Expression: Enter the fragmentation expression that defines criteria for assigning a set of rows to the fragment.

Rolling window options

For these actions, OAT requires Informix® 12.10.

If you select the Date-range fragmentation or the Range fragmentation scheme, you can control the size of the table by automatically removing the oldest fragments based on a threshold and a policy.

Thresholds

Select to limit the size of the table by the maximum number of fragments or by the total table size. If you select Maximum fragments, only interval fragments are eligible for removal. They will be detached or discarded starting from the fragment with the oldest value. If you select Maximum total size, you can choose what kind of fragments are eligible for removal.

Policy for the oldest fragment

Detach: Removes the oldest fragment and places the contents in a new nonfragmented table.
Discard: Permanently removes the fragment and its contents.

Fragments eligible for removal:

These options only apply if you enter a value for the Maximum total size threshold:

Interval first: Interval fragments starting from the fragment with the oldest value will be detached or discarded. If the table exceeds the maximum total size after all the interval fragments are removed, range fragments starting from the fragment with the oldest value will be removed.
Interval only: Only interval fragments will be detached or discarded, starting from the fragment with the oldest value.
Any: Any fragment, range or interval, will be detached or discarded, starting from the fragment with the oldest value.