index Command

[add|copy|modify|enable|list|print|disable|validate|delete] index NAME {CLAUSE};

• NAME is the name you assign to the role or group. This name must be unique and cannot be shared with any other type of user (groups, roles, persons, associations). Assign a name that has meaning to both you and users. For more information, see About Administrative Object Names
• CLAUSEs provide additional information about the index.

Syntax

add index NAME [unique] [ADD_ITEM {ADD_ITEM}];

• The unique keyword that indicates that a unique constraint is to be placed on the index table in the database schema. This allows a very efficient mechanism for implementations to require unique combinations of values on business objects. The unique setting only applies to objects within a single vault.
• ADD_ITEM provides additional information about the index:

  description VALUE

  icon FILENAME

  attribute NAME{, NAME}

  field FIELD_VALUE{ FIELD_VALUE} [size SIZE]

  [!|not]unique

  [!|not] hidden

  property NAME [to ADMINTYPE NAME] [value STRING]

  shortdescription

  history STRING

Attribute Clause

This clause assigns attributes to the index. These attributes must be previously defined with the add attribute command (for more information, see MQL Concepts: Defining an Attribute), and cannot include multiline attributes. Use the Attribute clause if only attributes will be included in the index. To include basic properties, see Field FIELD_VALUE.

Multi-line attributes cannot be added to an index.

A maximum of 25 items can be placed in a single index. However, a typical index will have between 2 and 5 items. For example:

add index CostAndWeight
  description “Actual Costs and Weight”
  attribute “Actual Cost”,“Actual Weight”;

In this syntax, attribute names are listed separated by a comma but no space, with the keyword “attribute” used only once. Quotes are needed for names that include spaces.

Generally, all attributes placed in an index should exist in the same type definitions. For example, if an index is created with the attributes Cost, Weight, and Quantity, then all relationships or types that reference Cost, Weight, or Quantity should reference all three of them. If there is a type or relationship that references only some of the attributes in an index, a warning is generated. You can proceed with the index creation, but it will take longer to create an index where this completeness test fails.

For expands, 3DSpace looks for and uses an index that is associated with relationships; that is, an index that has a relationship attribute as the first item in it.

There are special rules that apply when including a long string attribute in an index. Since a string attribute can be any length, and the index tables are constructed using fixed length columns, string attributes are truncated to 251 characters when written to an index table. This means that only the first 251 characters are searchable on the index. As mentioned below, the same applies to descriptions, but these fields are truncated (and therefore searchable) on the first 2040 characters.

Only the first 251 characters of string attributes and 2040 characters of descriptions are indexed.

This should not really be a concern, since string attributes designed to hold this much data are generally defined as “multi-line” and multi-line attributes cannot be included in an index.

Field FIELD_VALUE

Field FIELD_VALUE is used when you want to include basic properties in an index. FIELD_VALUE can be any of the following:

attribute[NAME]
type
name
revision
description
policy
current
owner
locker
modified
originated

For example:

add index “Shipping Details” unique field attribute[Cost] 
attribute[Weight] attribute[Quantity] current owner;

In this syntax, attributes names are in square brackets and include the keyword attribute with each one. Also, there is a space but no comma between fields.

You can assign any combination of basic properties (type, name, revision, description, owner, locker, policy, creation date, modification date, and current state) and defined attributes to the index, except for multiline attributes. A typical index will have between 2 and 5 items. While it is possible to have more, most indices should have no more than 5 items.

The first item in the index determines which objects go into the index table. Also, only queries and expands that include the first item will ever use it. If the first item in an index is an attribute, then all business objects and relationships that have that attribute will be added to the index table. If the first item in an index is a basic property, then all business objects (since all business objects have basics) and no relationships are added. Care must be taken when adding an index that has a basic property as its first field. If there are many such indices, performance will suffer, and storage requirements might exceed expectations.

Indices defined with a basic property first include all business objects and require maximum storage facilities. Therefore, adding basic properties first in an index should be limited to those that include only basic properties.

Generally, all attributes placed in an index should exist in the same type definitions. For example, if an index is created with the attributes Cost, Weight, and Quantity, then all relationships or types that reference Cost, Weight, or Quantity should reference all three of them. If there is a type or relationship that references only some of the attributes in an index, a warning is generated. You can proceed with the index creation, but it might take longer to create an index where this attribute completeness test fails.

You cannot include Description in an index in SQL Server environments.

The use of Description in an index should be avoided; the advanced search option indexes descriptions much more efficiently.

Size Clause

You can define a unique size restriction up to 2KB for each string based field FIELD_VALUE in an index. The size must be explicitly attached to the field it is restricting. For example:

add index CostAndWeight field description size 10;

Unique Clause

An index can be defined as “unique.” When this is specified, a unique constraint is placed on the index table in the database schema. This allows a very efficient mechanism for implementations that want to require unique combinations of values on business objects.

The unique setting only applies to objects within a single vault.

Shortdescription Clause

The shortdescription clause lets you create an index for the short description.

History Clause

The history keyword adds a history record marked “custom” to the index that is being added. The STRING argument is a free-text string that allows you to enter some information describing the nature of the addition. For more information, see Adding History to Administrative Objects.

copy index SRC_NAME DST_NAME [MOD_ITEM {MOD_ITEM}];

• SRC_NAME is the name of the existing index.
• DST_NAME is the name of the index to create based on the existing index.
• MOD_ITEM clauses can be any of the clauses listed in Modify Index.

modify index NAME [MOD_ITEM {MOD_ITEM}];

• NAME is the name of the index you want to modify.
• MOD_ITEM is the modification you want to make.
• You can make the following modifications. Each is specified in a Modify Index clause, as listed in the following table. You only need to specify fields to be modified.

  Modify Index Clause	Specifies
  name NEW_NAME	The current index name changes to that of the new name entered.
  description VALUE	The current description, if any, changes to the value entered.
  icon FILENAME	The image is changed to the new image in the file specified.
  add attribute NAME	The named attribute is added to the index's list of items.
  remove attribute NAME	The named attribute is removed from the index's list of items.
  add field NAME	The named field is added to the index's list of items. This syntax is only valid if you are adding a NEW field. If the index already has this field defined, an error is returned and to modify this field, you must use this syntax: modify index NAME modify field NAME;
  remove field NAME	The named field is removed from the index's list of items.
  hidden	The hidden option is changed to specify that the object is hidden.
  [!|not]hidden	The hidden option is changed to specify that the object is not hidden.
  unique	A unique constraint is added to the index's table.
  [!|not]unique	The unique constraint is removed from the index's table.
  modify field FIELD_NAME [size SIZE]	Changes the size of the specified field.
  property NAME [to ADMINTYPE NAME] [value STRING]	The named property is modified.
  add property NAME [to ADMINTYPE NAME] [value STRING]	The named property is added.
  remove property NAME [to ADMINTYPE NAME] [value STRING]	The named property is removed.
  history STRING	Adds a history record marked "custom" to the index that is being modified. The STRING argument is a free-text string that allows you to enter some information describing the nature of the modification. For more information, see Adding History to Administrative Objects.

enable index NAME;

As soon as you enable the index, a table is created in every vault. (The tables are named in the form ixXXXXXX_XXXXX, created by concatenating a prefix associated with the index and a suffix associated with the vault. The prefix can be obtained from print index command and the suffix from the print vault command.) If you then disable the index, or add or remove items, these tables are dropped. Also, if you add an attribute to a type or relationship that is part of an index, the index is disabled. To use the index, it must be re-enabled.

For example, to enable the index named “Index for Finance queries,” enter the following MQL command:

enable index “Index for Finance queries”;

Creating and enabling indices are not appropriate actions to be performed within explicit transaction boundaries, particularly if additional operations are also attempted before a commit.

To disable a defined index, use:

disable index NAME;

You can use one of these formats to issue the Validate Index command:

validate [level 0|1|2|3|4] [output FILENAME] index [summary];

validate [level 0|1|2|3|4] [output FILENAME] index INDEX_NAME {,INDEX_NAME};

validate [level 0|1|2|3|4] [output FILENAME] index vault VAULT_NAME table TABLE_NAME;

This command assumes statistics are already up-to-date for all other tables.

delete index NAME:

• NAME is the name of the index to be deleted.

This command searches the list of indices. If the name is not found, an error message is displayed. If the name is found, the index is deleted. For example, to delete the index named “Index for Finance queries,” enter the following command:

delete index “Index for Finance queries”;

For example:

add index CostAndWeight
  description “Actual Costs and Weight”
  field attribute[Actual Cost],attribute[ActualWeight],owner;