Index schema
Index name
Table schema
Table name
Available columns
>>, >, <, <<
Selected columns
Include columns
Unique
Cluster
Allow reverse scans
Percentage of free space to be left on index pages
Percentage of minimum amount of used space to be left
on index pages
Comment
Ascending and Descending
Up
Down
Show SQL
Index schema
Use this box to specify the schema of the index that you're creating. The initial value in this box is the user ID under which you connected to the database. To specify a schema with a name that's different than this ID, use one of these methods:
Index name
Type a name for the index that you are creating. This name:
Attention: This name must be unique within the indexes' schema. No other object in the schema can have the same name.
Table schema
Use this box to specify the schema of the table on which you're creating an index. The initial value in this box is the user ID under which you connected to the database. To specify a schema with a name that's different than this ID, use one of these methods:
Table name
Type the name of the table on which the index is created. The table must be a base table (not a view) described in the catalog. It must not be a catalog table. The table name:
Available columns
This list box lists all of the columns defined for the table specified in the Table name field. Use this box to select the column or columns that you want to define as part of the index key. You can select one or more columns at the same time.
You can specify up to 16 columns. At least one column must be specified to define an index.
The sum of length attributes cannot be longer than 255.
Selected columns
Use this box to specify the column or columns that you want to define as part of the index key.
To add a column name to this box, select it from the Available columns list box and select the > push button.
You can specify up to 16 columns. At least one column must be specified to define an index.
The sum of the length attributes of the specified columns must not be greater than 255 bytes.
>>, >, <, <<
Use these push buttons to change the list of columns displayed in the Available columns, Selected columns, and Include columns list boxes. You can use:
Ascending and Descending
Use these radio buttons to specify the sort order of the columns that you selected in the Selected columns list box.
Unique
Select this check box to indicate that a table will not contain two or more rows with the same value of the index key. The constraint is enforced when rows of the table are updated or new rows are inserted. The constraint is also checked during the execution of the CREATE INDEX statement. If the table already contains rows with duplicate key values, the index is not created.
When the Unique check box is selected, null values are treated as any other values. For example, if the key is a single column that can contain null values, that column can contain no more than one null value.
Cluster
Select this check box to specify that an index is the clustering index of the table. Only one clustering index may exist for a table so Cluster may not be specified if it was used in the definition of any existing index on the table.
A clustering index may not be created on a table that is set to use append mode.
Allow reverse scans
Select this check box to specify that Reverse scans can be performed on the index that you are creating. When the check box is checked, the index can support both forward and reverse scans; that is, the scan can be performed in the order defined at INDEX CREATE time and in the opposite (or reverse) order. This eliminates the need for the optimizer to create a temporary table for a reverse scan and it eliminates the need for you to create 2 indexes for the same columns on the table, one for forward and one for reverse scanning. It also facilitates determination of the maximum key of an index.
When the check box is not checked, the index only supports forward scans or scanning of the index in the order defined at INDEX CREATE time.
Percentage of free space to be left on index pages
Use this field to specify the percentage of each index page to leave as free space when building the index.
The first entry in a page is added without restriction. When additional entries are placed in an index page, the value specified in this field determines the percentage of free space left on each page.
Select a percent from 0 to 99. The default value is 10 percent. If you select a value greater than 10, only 10 percent free space will be left in non-leaf pages.
Percentage of minimum amount of used space to be left on index
pages
This field specifies for Online Index Reorganization the threshold for the minimum percentage of used space on an index leaf page. If the percentage of space used on the page is at or below integer % after a key is deleted from an index leaf page, an attempt will be made to merge the remaining keys on this page with those of a neighbouring page. If there's sufficient space on one of these pages, the merge will be performed and one of the pages will be deleted.
The value of integer can be from 0 to 99. However, a value of 50 or below is recommended for performance reasons. The default value is 10 percent.
Comment
Type a comment to document the index that you are creating or altering. You can type up to 254 characters, including embedded blanks.
Include columns
Use this box to specify additional columns to be included in the index but not as part of the unique index key. These columns may improve the performance of some queries through index-only access. The columns must be distinct from the columns used to enforce uniqueness.
To add a column name to this box, select it from the Available columns list box and select the > push button.
You can specify up to 16 columns. The sum of the length attributes of the specified columns must not be greater than 255 bytes.
