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 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:
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:
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:
DESCRIBE INDEX Customer.address_billing;
DESCRIBE INDEX Customer.address%;
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 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:
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:
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:
DESCRIBE INDEX Customer.address_billing;
DESCRIBE INDEX Customer.address%;