Knowledge Base Academy Community My Tickets
Fabric 8.1
Fabric 8.1 Fabric 8.0 Fabric 7.2 Fabric 7.1 Fabric 7.0 Fabric 6.5 Fabric 6.4

Table Indexes

What Is an LU Table Index?

An index can be created for an LU table in order to improve performance when querying/updating LU tables in memory during the Sync process. Indexes are created on a table level and can be built for either one field or many fields. Each LU table can have several indexes, where each index can contain several columns.

When Do I Create an Index?

When querying LU tables, indexes should be considered for fields used in WHERE clauses. Indexes should always be built in the same order as in the conditions of the WHERE clause of the query. This also applies when using actions like Group By or Distinct that must be applied on indexed fields as displayed in Example 1. When an LU table is invoked as a Lookup table, create an index for the lookup input links that are used in the WHERE statement. It is recommended to create an index on the fields of a parent LU table that are used as a key to a child table within the LU Schema, as displayed in Example 2.

Example 1 The USAGE table contains 100K records per customer. The SELECT SQL statement is:

SELECT sum (CALLS) from USAGE where TYPE = ‘I’ and PRIORITY = ‘High’

To improve the performance of this statement, a combined index should be created for the TYPE and PRIORITY columns, where TYPE is the 1st column in the index and PRIORITY is the 2nd.

Example 2 The ORDERS table is a parent table of the CONTRACT and ORDERS_SUMMARY tables:

  • The CONTRACT table is linked to the ORDERS table via the CONTRACT_ID field.
  • The ORDERS_SUMMARY table is linked to the ORDERS table via the ORDER_TYPE field. It is therefore recommended to create 2 indexes for the ORDERS table:
    • one for the CONTRACT_ID field, and
    • one for the ORDER_TYPE field.

image

Index Definition

LU table indexes are defined in the Indexes Tab in the Table window, where they can also be added, modified or removed from a table. The following Index settings can be defined:

Index ID

(Read only). An Index identifier in the current table.  

PK

Defines whether the current index is a primary key. Check to set or unset this definition.

Unique

Defines whether the current index is unique. Check the checkbox for setting/unsetting this definition.

Columns

(Read only). A list of LU table columns that define the current index.  

How Do I Add an Index?

  1. Go to Project Tree > Logical Units > [LU Name] > Tables > double-click [Table Name] to open the table’s settings.
  2. In the Columns tab, right-click one field or more fields and select Create Index from Selected Columns.
  3. Click the Indexes tab to display a list of indexes.
  4. Check/uncheck PK and Unique checkboxes to complete the index definition.
  5. Save the table.

How Do I Delete an Index?

  1. Go to Project Tree > Logical Units > [LU Name] > Tables > double-click [Table Name] to open the table’s settings.
  2. In the Indexes tab, click Delete to delete the index.

Index Definition

  1. To select the LU table, either
    • Go to Project Tree > Logical Units > [LU Name] > Tables > double-click [Table Name] to open the table, or
    • Go to Project Tree > Logical Units > [LU Name] > Schema > select the relevant table.
  2. Reveal the table's properties side panel by clicking the img icon in the upper-right corner of the window (it may already be open).
  3. Expand the Indexes tab, where you can see the already defined indexes.
  4. Click the '+' to add indexes
    • The index automatically gets an index ID.
    • You can choose whether it will be a unique index.
    • Select the table's fields to be part of the index.
  5. You can delete an index by clicking on the trash icon aside its ID.

How Do I Get a List of Indexes?

The DESCRIBE command is one of Fabric's view commands and it is used for querying the Fabric metadata structure on a deployed project in the Fabric console. To get the list of indexes for a specified table, use one of the following commands:

Syntax:

  • Get all system indexes: DESCRIBE INDEX;

  • Get all indexes of the schema of an LU: DESCRIBE INDEX LU-Name;. For example: to get all indexes of the schema of LU Customer: DESCRIBE INDEX Customer;.

  • Get the indexes of a specific table within the LU name: DESCRIBE INDEX LU-Name.Table-Name. For example:

    • Get the indexes of a specific table within the Customer LU: DESCRIBE INDEX Customer.address_billing;
    • Get the indexes of all tables starting with address:DESCRIBE INDEX Customer.address%;

Click for more information about The Fabric View commands.

Previous

Table Indexes

What Is an LU Table Index?

An index can be created for an LU table in order to improve performance when querying/updating LU tables in memory during the Sync process. Indexes are created on a table level and can be built for either one field or many fields. Each LU table can have several indexes, where each index can contain several columns.

When Do I Create an Index?

When querying LU tables, indexes should be considered for fields used in WHERE clauses. Indexes should always be built in the same order as in the conditions of the WHERE clause of the query. This also applies when using actions like Group By or Distinct that must be applied on indexed fields as displayed in Example 1. When an LU table is invoked as a Lookup table, create an index for the lookup input links that are used in the WHERE statement. It is recommended to create an index on the fields of a parent LU table that are used as a key to a child table within the LU Schema, as displayed in Example 2.

Example 1 The USAGE table contains 100K records per customer. The SELECT SQL statement is:

SELECT sum (CALLS) from USAGE where TYPE = ‘I’ and PRIORITY = ‘High’

To improve the performance of this statement, a combined index should be created for the TYPE and PRIORITY columns, where TYPE is the 1st column in the index and PRIORITY is the 2nd.

Example 2 The ORDERS table is a parent table of the CONTRACT and ORDERS_SUMMARY tables:

  • The CONTRACT table is linked to the ORDERS table via the CONTRACT_ID field.
  • The ORDERS_SUMMARY table is linked to the ORDERS table via the ORDER_TYPE field. It is therefore recommended to create 2 indexes for the ORDERS table:
    • one for the CONTRACT_ID field, and
    • one for the ORDER_TYPE field.

image

Index Definition

LU table indexes are defined in the Indexes Tab in the Table window, where they can also be added, modified or removed from a table. The following Index settings can be defined:

Index ID

(Read only). An Index identifier in the current table.  

PK

Defines whether the current index is a primary key. Check to set or unset this definition.

Unique

Defines whether the current index is unique. Check the checkbox for setting/unsetting this definition.

Columns

(Read only). A list of LU table columns that define the current index.  

How Do I Add an Index?

  1. Go to Project Tree > Logical Units > [LU Name] > Tables > double-click [Table Name] to open the table’s settings.
  2. In the Columns tab, right-click one field or more fields and select Create Index from Selected Columns.
  3. Click the Indexes tab to display a list of indexes.
  4. Check/uncheck PK and Unique checkboxes to complete the index definition.
  5. Save the table.

How Do I Delete an Index?

  1. Go to Project Tree > Logical Units > [LU Name] > Tables > double-click [Table Name] to open the table’s settings.
  2. In the Indexes tab, click Delete to delete the index.

Index Definition

  1. To select the LU table, either
    • Go to Project Tree > Logical Units > [LU Name] > Tables > double-click [Table Name] to open the table, or
    • Go to Project Tree > Logical Units > [LU Name] > Schema > select the relevant table.
  2. Reveal the table's properties side panel by clicking the img icon in the upper-right corner of the window (it may already be open).
  3. Expand the Indexes tab, where you can see the already defined indexes.
  4. Click the '+' to add indexes
    • The index automatically gets an index ID.
    • You can choose whether it will be a unique index.
    • Select the table's fields to be part of the index.
  5. You can delete an index by clicking on the trash icon aside its ID.

How Do I Get a List of Indexes?

The DESCRIBE command is one of Fabric's view commands and it is used for querying the Fabric metadata structure on a deployed project in the Fabric console. To get the list of indexes for a specified table, use one of the following commands:

Syntax:

  • Get all system indexes: DESCRIBE INDEX;

  • Get all indexes of the schema of an LU: DESCRIBE INDEX LU-Name;. For example: to get all indexes of the schema of LU Customer: DESCRIBE INDEX Customer;.

  • Get the indexes of a specific table within the LU name: DESCRIBE INDEX LU-Name.Table-Name. For example:

    • Get the indexes of a specific table within the Customer LU: DESCRIBE INDEX Customer.address_billing;
    • Get the indexes of all tables starting with address:DESCRIBE INDEX Customer.address%;

Click for more information about The Fabric View commands.

Previous

© Copyright 2024 K2VIEW