Create Index Wizard - Index Options and Storage

Use this page to specify the index options and storage scheme for a new index.

Index Creation Options

For unique, cluster, and duplicate indexes, specify whether to create the index offline.

Offline: Place an exclusive lock on the indexed table to prevent all other users from accessing the table while the index is being created (default).
Online: Create the index online while concurrent users can continue to access the table. Creating the index online can reduce the risk of non-exclusive access errors and increase the availability of the indexed table.

For unique, duplicate, and forest of trees indexes, specify whether to compress the index when it is created. For compressed indexes, the OpenAdmin Tool (OAT) for Informix® requires IBM® Informix 12.10.

Stop Words

For basic text search indexes, specify the words to exclude from the index. Use the default list or a customized list.

Load from a file: Enter the full path name for the file that contains the stop words. For example: /docs/stopwords.txt. You can use your own operating system file of stop words or copy and edit the file provided with the Basic Text Search module. Separate the stop words in the file or table by commas, spaces, new lines, or a combination of those separators.
Enter a list: Enter the stop words separated by commas. For example: am,be,are.
Load from a column: Enter the name of the table and the column that contains the stop words.

XML Tags

For XML indexes, use all the XML tags or paths in a column, or specify which XML tags or paths to use. If you use all the XML tags, all the full paths are indexed. If you specify the XML tags, you can index full or relative paths.

Load from a file: Enter the full path name for the file that contains the XML tags.
Enter a list: Enter the XML tags separated by commas. For example: /text/book/title,/text/book/author,/text/book/date. XML tags are case-sensitive. When you enter the tags, the names are transformed to lowercase characters. If the tags are uppercase or mixed case, use an external file or a table column instead.
Load from a column: Enter the name of the table and the column that contains the XML tags.

XML Options

For XML indexes, specify the options.

Enable XML path processing: Enable searches based on the XML paths that are specified with the XML Tags option.
Include namespaces: Index XML tags that include namespaces in the qualified namespace format prefix:localpart. For example: <book:title></book:title>.
Include subtag text: Index XML tags and subtags as one string. This option is useful when you want to index text that is formatted with bold <b></b> or italic <i></i> tags.

Number of subtrees

For forest of trees indexes, specify the number of subtrees (buckets) to create. The number of subtrees depends on your goal for the index.

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. 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 and the expected growth rate (static, medium, or aggressive), and then click Estimate. The first extent size and next extent size are estimated based on the row size of the defined columns.

Index Storage Options

Select the storage option.

Follow the table storage scheme: An index that implicitly follows the table storage scheme is also called an attached index. It uses the same fragmentation scheme and set of dbspaces for the fragments as the table.
Specify the storage scheme: An index with a storage scheme that is specified explicitly is called a detached index even if it has the same storage scheme as the table.

Storage Scheme

Specify a specific 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 index 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 index 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 index 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.

Expression-based

Select this option to create a fragmented index 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 index. The name must follow the guidelines of SQL syntax for an identifier.
Dbspace: Select the dbspace to store the fragment.
Expression: Enter the fragmentation expression that defines criteria for assigning a set of rows to the fragment.