Indexing AR System tables
Indexes are automatically maintained for all the tables created by BMC Remedy AR System. Some are defined by BMC Remedy AR System, and others are defined by an administrator. If a table is restructured through BMC Remedy AR System, all indexes are re-created for the new table.
The main data table has an index supported by BMC Remedy AR System defined for the C1 column. This column corresponds to the Request ID field of the form. (In Microsoft SQL Server databases, the table is created using a primary key, which enables database replication.) The index is a unique index and is used extensively as the main index of the table.
For the main data table, the administrator can create additional indexes for the form. The indexes are unique only if defined as such. These additional indexes are not clustered because there can be only one clustered index, and it is reserved for the main index supported by BMC Remedy AR System.
The status history table has an index supported by BMC Remedy AR System defined on the entryId column. This column also corresponds to the Request ID field of the form. The index is a unique clustered index and is the main index of the table. BMC Remedy AR System does not create additional indexes for the status history table.
The Attachment Data and the Attachment Details tables each have unique indexes supported by BMC Remedy AR System. For the Attachment Data table, the index is defined on the entryId column, and for the Attachment Details table, the index is defined on the C1 column. These columns correspond to the Request ID field of the form. The administrator cannot create additional indexes.
Indexing a currency field requires special considerations. Because a currency field is represented by multiple columns in the main data table, multiple columns are indexed. Standard queries against a currency field could potentially use any of several different columns, depending on the currency type specified. To provide comprehensive coverage, indexing a currency field requires an index for the value column, the type column, and for each functional currency column. This can produce significant overhead for the main data table. Therefore, consider indexing a currency field carefully before doing so.
Indexes cannot be created for join forms. The form definition is just a view and the database does not support indexes for views. Indexes defined for the underlying tables are available and are used when performing operations against the join form. For view forms, you must create indexes within the database. The BMC Remedy AR System cannot create indexes on the view of the external database's table. For vendor forms, the administrator who implemented the ARDBC data source must define and document a mechanism to establish indexes on the underlying data. For more information about ARDBC, see the Developing an API application.
Rebuilding indexes (DB2 only)
The clustered index on the arschema table is created on the schemaId field instead of the name field.
If you are upgrading your Microsoft SQL Server or Sybase database, the change is included with the installation. For other databases, you must manually create the arschema table on the schemaId field.
For more information, see Preparing your database.
To create a clustered index on DB2
- List the tables that have indexes on the name column, and re-create indexes on those tables as described in the following steps.
- Drop the existing index.
DROP INDEX schema_id_ind DROP INDEX schema_ind
- Create an index as required.
CREATE UNIQUE INDEX schema_id_ind ON arschema (schemaId) CLUSTER CREATE UNIQUE INDEX schema_ind ON arschema (name)
- Reorganize all the indexes.
REORG INDEXES ALL FOR TABLE arschema