Allow Index on Metric

Allow Index on Metric is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Allow Index on Metric property determines whether or not to use fact or metric columns in index creation. You can see better performance in different environments, especially in Teradata, when you add the fact or metric column in the index. Usually, the indexes are created on attribute columns; but with this setting, the fact or metric columns are added as well. All fact or metric columns are added.

Levels at Which You Can Set This

Database instance, report, and template

Example

Do not allow creation of indexes on fact or metric columns (default)

create table ZZT8L005Y1YEA000 (
 CATEGORY_ID BYTE, 
 REGION_ID BYTE, 
 YEAR_ID SHORT, 
 WJXBFS1 DOUBLE, 
 WJXBFS2 DOUBLE)
insert into ZZT8L005Y1YEA000 
select a13.[CATEGORY_ID] AS CATEGORY_ID,
 a15.[REGION_ID] AS REGION_ID,
 a16.[YEAR_ID] AS YEAR_ID,
 sum((a11.[QTY_SOLD] * (a11.[UNIT_PRICE] -
a11.[DISCOUNT]))) as WJXBFS1,
 sum((a11.[QTY_SOLD] * ((a11.[UNIT_PRICE] -
a11.[DISCOUNT]) - a11.[UNIT_COST]))) as
WJXBFS2
from [ORDER_DETAIL] a11, 
 [LU_ITEM] a12, 
 [LU_SUBCATEG] a13, 
 [LU_EMPLOYEE] a14, 
 [LU_CALL_CTR] a15, 
 [LU_DAY] a16
where a11.[ITEM_ID] = a12.[ITEM_ID] and 
 a12.[SUBCAT_ID] = a13.[SUBCAT_ID] and 
 a11.[EMP_ID] = a14.[EMP_ID] and 
 a14.[CALL_CTR_ID] = a15.[CALL_CTR_ID] and 
 a11.[ORDER_DATE] = a16.[DAY_DATE]
 and a15.[REGION_ID] in (1)
group by a13.[CATEGORY_ID],
 a15.[REGION_ID],
 a16.[YEAR_ID]
create index ZZT8L005Y1YEA000_i on ZZT8L005Y1YEA000
(CATEGORY_ID, REGION_ID, YEAR_ID)

Allow the creation of indexes on fact or metric columns

This example is the same as the example above except that the last line of code should be replaced with the following:

create index ZZT8L005YAGEA000_i on ZZT8L005YAGEA000
(CATEGORY_ID, REGION_ID, YEAR_ID, WJXBFS1, WJXBFS2)

Index Prefix

This property allows you to define the prefix to add to the beginning of the CREATE INDEX statement when automatically creating indexes for intermediate SQL passes.

For example, the index prefix you define appears in the CREATE INDEX statement as shown below:

create index(index prefix)
IDX_TEMP1(STORE_ID, STORE_DESC)

Levels at Which You Can Set This

Database instance, report, and template