Right click a table in the left pane and selecting Display Indexes from the context menu lists the indexes for the table in the middle pane. Alternatively, you can double-click the Index object type in the left pane, to display all indexes in the repository or a specific group or project.
In the middle pane, right-click an index and the following options are displayed:
- Properties
- Create Index
- Drop Index
- Delete Metadata and Drop Index
- Create via Scheduler
- Projects
- Build
Properties
The Properties screen (see example below) can be selected via the right-click context menu when positioned on an index name in the middle pane. The Update Buttons: Update <- and Update -> are used to move to the previous and next index respectively. The Update Buttons are not available when browsing all indexes in a group, project or repository.
SQL Server example:
Oracle example:
Note
- Oracle 11g allows Indexes to be marked as invisible.
- Invisible Indexes are maintained like any other index but they are ignored by the optimizer, unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to true at the instance or the session level. The default of this option is not set.
IBM DB2 example:
The fields in the General section are described below:
Field | Description |
|---|---|
Index Name | Typically the table short name followed by:
|
Index Description | Free flow description of the index. |
Active |
|
Business Key | Denotes a business key. |
Artificial Key | When enabled indicates that this is the surrogate (artificial) key generated by the system. |
Unique | Specifies that the index is a unique index. Note If both unique and artificial are set it is assumed to be a primary key constraint and it is added as such. |
Index Type | Database-specific type of index On SQL Server the options are:
|
Index Columns | Shows the columns in the order they will be applied to the index. The order can be changed using the up/down buttons at the left. |
Non-Key (Include) Columns | Extend a Non-clustered Index to include Non-Key columns in addition to the key columns. |
Index Filter Predicate | A filtered index is an optimized Non-clustered index, which uses a filter predicate to |
Enable Parallel Build Degree | Activates parallel creation /build of the index. For unsupported database versions (Pre SQL2005) leave disabled. |
Additional Index Create Options | Database-specific-and-compliant options to include in the generated CREATE INDEX statement. |
Compute Statistics | For Oracle. When enabled, RED automatically collects statistics during index creation and rebuild. NOTE: Deprecated since 10g (but supported for backwards compatibility) as now statistics are always collected during index creation and rebuild. |
The fields in the Storage section are described below:
Field | Description |
|---|---|
Filegroup/Tablespace | Default Filegroup/Tablespace of the index that determines the storage location it is created. Select (Default) to use the default. |
Initial Extent (KB) | For Oracle. Size of the first extent allocated to the index when it is created. The default value of 0 will instead use the value from the Tablespace in which the index is stored. |
Next Extent (KB) | For Oracle. Size of the next extent allocated to the index, unless Percent Increase is specified. |
Minimum Extents | For Oracle. Number of extents to allocate when the index is created. The default value of 0 will instead use the value from the Tablespace in which the index is stored. |
Maximum Extents | For Oracle. Total number of extents that can be allocated to the index. The default value of 0 will instead use the value from the Tablespace in which the index is stored. |
Percent Increase | For Oracle. Percent by which the third and subsequent grow over the preceding extent. The default value of 0 will instead use the value from the Tablespace in which the index is stored. |
Percent Free | For Oracle. Percentage [0-99] of space in each data block of the index to reserve for future updates. The default value of 0 will instead use the value from the Tablespace in which the index is stored. |
Buffer Pool | For Oracle. Default buffer pool used to cache the data blocks of the index. |
Compress | Compress index to reduce disk and memory use. For unsupported database versions (Pre SQL2008) select (Not Defined). |
Enable Fill Factor | Setting this will enable setting of the Fill Factor. For unsupported database versions (Pre SQL2005) leave disabled. |
Sort In tempdb | Use the tempdb to store the intermediate sort results that are used to build the index, which may reduce the index build time if tempdb is on different disks to the index. |
Enable Percent Free | For DB2. Setting this will enable setting of the Percent Free to something other than the Database defaults. |
Percent Free | For DB2. The amount of free space to leave in each extent/page of the index. |
Enable Level2 Percent Free | For DB2. Setting this will enable setting of the Level2 Percent Free to something other than the Database defaults. |
Level2 Percent Free | For DB2. The amount of free space in each index level 2 page. |
Enable Minimum Percent Used | For DB2. Setting this will enable setting of the Minimum Percent Used to something other than the Database defaults. |
Minimum Percent Used | For DB2. The threshold of the minimum percentage of space used on an index leaf page. |
The fields in the Scheduler section are described below:
Field | Description |
|---|---|
Drop Index before Table Update | Drop the index before running the table update procedure and recreate the index after the update procedure has completed. |
Index Rebuild Frequency | Optional frequency that the index is automatically rebuilt by the Scheduler. |
Indexes are normally managed by the scheduler as part of the normal processing of a table.
Refer to Ws_Maintain_Indexes. This function allows the control of index drop and creation from within a procedure. Typically, this function is called when using partitioned tables.
Extended Properties
Extended property variables can be set for index objects. An extended property's scope is defined by object type but the value for the variable can be set for the table and each index independently. Refer to Creating an Extended Property Definition for details.
Automatically created indexes are recreated each time the table's procedure is rebuilt. Therefore, the values set for extended properties in automatically created indexes are lost when they are recreated. Manually created indexes and their extended properties are persisted.
Similar to the extended properties defined for tables and connections, extended properties defined in indexes can be accessed from templates, including create index DDL, drop index DDL and update procedure/script.