Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Index options (Index)

Applies to
Platform: Microsoft SQL Server · Oracle

The index options are the properties of a custom index that you add in Manage Indexes. They set the columns, the uniqueness and the physical placement of the index that the deployment creates. The options a new index offers depend on its Index Type.

Only Microsoft SQL Server and Oracle generators offer index types. On the other platforms, for example Snowflake, the menu of a model object has no Manage Indexes, and there are no custom indexes.

Where to set them

Open Manage Indexes of a model object, choose the target object and add or edit an index, see Add an Index. The panel groups Create as Unique Index and Create as local Index under Index, and Tablespace Index under Technology. The generator adds the custom indexes to the default indexes of the target object, unless you switch to Only Custom Indexes, see Understand Indexes overview.

Options per index type

  • Microsoft SQL Server, Clustered Rowstore Index: Index Columns, Create as Unique Index
  • Microsoft SQL Server, Non-clustered Rowstore Index: Index Columns, Create as Unique Index, Included Columns
  • Microsoft SQL Server, Clustered Columnstore Index: no options; the index covers the whole table
  • Microsoft SQL Server, Non-clustered Columnstore Index: Included Columns
  • Oracle, B-Tree Index: Index Columns, Create as Unique Index, Create as local Index, Tablespace Index
  • Oracle, Bitmap Index: Index Columns, Create as local Index, Tablespace Index

Index Columns

IndexColumns. The key columns of the index, in order, each with the sort order ASC (default) or DESC. Required: on Microsoft SQL Server a rowstore index without index columns fails validation with ID-32000, on Oracle an index without columns fails with ID-33000. A bitmap index takes its columns without sort order.

Create as Unique Index

UniqueIndex. Boolean, not set by default. When set, the statement is CREATE UNIQUE ... INDEX and the database rejects duplicate key values. Offered for the rowstore indexes on Microsoft SQL Server and the B-Tree index on Oracle.

Included Columns

IncludedColumns, Microsoft SQL Server only. For a Non-clustered Rowstore Index, optional non-key columns in the INCLUDE list. For a Non-clustered Columnstore Index, the columns of the index; without them validation fails with ID-32008. A column cannot be both an index column and an included column (ID-32004).

Create as local Index

LocalIndex, Oracle only. Boolean, not set by default. On a storage table with a Partition Clause the B-Tree index becomes a local partitioned index. On a table without a Partition Clause the setting is ignored, and validation shows warning ID-33001. A bitmap index on a table with a Partition Clause is always local, also without this setting.

Tablespace Index

IndexTablespace, Oracle only. The tablespace of the index, written as TABLESPACE clause. Without a value the statement names no tablespace.

Validation

Besides the messages above, Microsoft SQL Server checks the whole table. More than one clustered index fails with ID-32001, more than one columnstore index with ID-32003. A clustered custom index next to active default indexes shows warning ID-32002. Validation errors block the generation in the client; warnings do not.

Example

A Mart Fact cust_index_without_default_indexes on Microsoft SQL Server, with the default indexes switched off, has three custom indexes. Another Mart Fact has a Clustered Columnstore Index. The deployment creates, with the schema placeholder replaced by mart:

CREATE UNIQUE CLUSTERED INDEX [ix_doc_unique_clustered]
  ON [mart].[DM_MF_cust_index_without_default_indexes]
  ([common_pos_common_POS_SK] ASC,[BG_LoadTimestamp] DESC);
CREATE NONCLUSTERED INDEX [ix_doc_nonclustered_include]
  ON [mart].[DM_MF_cust_index_without_default_indexes]
  ([SALES_PRICE] DESC) INCLUDE ([QUANTITY],[SALES_AMOUNT]);
CREATE NONCLUSTERED COLUMNSTORE INDEX [ix_doc_nonclustered_columnstore]
  ON [mart].[DM_MF_cust_index_without_default_indexes]
  ([SALES_PRICE],[QUANTITY],[SALES_AMOUNT]);
CREATE CLUSTERED COLUMNSTORE INDEX [ix_doc_clustered_columnstore]
  ON [mart].[DM_MF_load_insert_without_effective_timestamp];

On Oracle, a Hub with the Partition Clause PARTITION BY HASH (HUB_HK) PARTITIONS 4 has a unique B-Tree index with Create as local Index and Tablespace Index USERS, and a bitmap index without Create as local Index. The deployment creates, with the schema placeholder replaced by RV:

CREATE UNIQUE INDEX RV.IX_DOC_BTREE_UNIQUE_LOCAL
 ON RV.RDV_HUB_LOAD_CUSTOMER_HUB (HUB_HK ASC,BG_LOADTIMESTAMP DESC)
TABLESPACE USERS
LOCAL
;
CREATE BITMAP INDEX RV.IX_DOC_BITMAP_NOT_LOCAL
 ON RV.RDV_HUB_LOAD_CUSTOMER_HUB (BG_SOURCESYSTEM)
LOCAL
;

In the database, USER_PART_INDEXES lists both indexes with locality LOCAL.

Release differences

  • Generator 2.4.1, Oracle: Create as local Index takes effect only on a table with a Partition Clause, and a bitmap index on such a table is always local. Up to Generator 2.4.0 the statement of either index type contained LOCAL exactly when Create as local Index was set, also on a table without a Partition Clause.

Related articles