t_RelationalDatabaseUpgrade_CreateIndex

CreateIndex

Add a new index to a table.

Properties

aspects : aspectList

List of names of aspect indexes to include in or exclude from (if prefixed with !) this index. If an aspect index will be included in this index, the aspect index's columns are pre-pended to this index's columns.

Valid values based on“token”.
(!?:?[\p{L}_][\p{L}\p{N}_]*(:[\p{L}_][\p{L}\p{N}_]*)*\s+)*(!?:?[\p{L}_][\p{L}\p{N}_]*(:[\p{L}_][\p{L}\p{N}_]*)*)?	Each element in the list must begin with a letter or underscore. Elements are space delimited. To exclude a token, prefix it with the character "!".

description : string

Index description.

fill : unsignedByte

Index page fill factor percent, 1-100 (0 for the database default, empty for the schema default).

name : indexName

Index name, unique within the schema. It is usually prefixed by the table name: [<owner>.]<table>.<index>

Valid values based on“string”.
[A-Za-z][_A-Za-z\d]*(\.[A-Za-z][_A-Za-z\d]*){0,2}

relatedTable : tableName

Table referenced by this index serving as a foreign key.

Valid values based on“string”.
[A-Za-z][_A-Za-z\d]*(\.[A-Za-z][_A-Za-z\d]*)?

table : tableName

Table name, as specified in the data source: [<owner>.]<table>

Valid values based on“string”.
[A-Za-z][_A-Za-z\d]*(\.[A-Za-z][_A-Za-z\d]*)?

type

Index type.

Virtual indexes are used only for column grouping and do not correspond to physical indexes in the database. They often are a subset of a physical index's columns.

Query indexes are virtual indexes that can function as sort keys.

Btree creates a physical B-tree index in the database.

Cluster creates a physical clustered index in the database. A table can have at most one clustered index.

Text indexes are used to speed up full text search, and not usually used by DBMS for regular data lookup.

Aspect indexes define index columns that may be prepended to other non-aspect indexes.

Valid values based on“string”.
btree	btree works well for non-primitive attribute mapping. They are used as foreign keys. In these cases one should not create physical foreign keys (as opposed to just indexes) in the database.
cluster	An index on which most searches/sorts on table occur should be "clustered", unless a lot of insertions are expected on the table.
text	Text indexes are used to speed up full text search, and not usually used by DBMS for regular data lookup.
query	Virtual index which counts as a sort key. It should be used as a hint to the framework only in place of a btree or a cluster index that is not used by the database.
virtual	If a physical index is not needed (e.g. if another index starts with the same columns, or it is not anticipated that it will be used by the optimizer), set its type to "virtual". A typical case when a physical index is not needed is when it contains the same columns with the same sort direction that start another index. E.g. if four physical indexes are needed on the same table, A(a,b), B(a,b,c,d), C(a, c) and D(b,c), then A is redundant and should be virtual, as the database can use B instead, but C and D define a different sort order than B, so they should not be marked as virtual.
aspect	Aspect indexes are index column sets that can be prepended to other non-aspect indexes. They do not represent real indexes in the generated schema. An Aspect index is prepended to all non-aspect indexes that specify it by name in their "aspects" property.

unique : boolean

True if the index is unique.

Content

• Sequence of:

  • t_RelationalIndex_IndexColumn [0..*] - An index is made up of one or more columns.

Parents

• t_Upgrade_RelationalDatabaseUpgrade - The set of Relational Upgrade steps to describe what schema changes took place in this version

• t_RelationalIndex_IndexColumn
  An index is made up of one or more columns.

Parent topic: t_Upgrade_RelationalDatabaseUpgrade