DSS Star Join

DSS Star Join 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 DSS Star Join property specifies whether a partial star join is performed or not. A partial star join means the lookup table of a column is joined if and only if a column is in the SELECT clause or involved in a qualification in the WHERE clause of the SQL. In certain databases, for example, RedBrick and Teradata, partial star joins can improve SQL performance if certain types of indexes are maintained in the data warehouse. Notice that the lookup table joined in a partial star join is not necessarily the same as the lookup table defined in the attribute form editor. Any table that acts as a lookup table rather than a fact table in the SQL and contains the column is considered a feasible lookup table.

Levels at Which You Can Set This

Database instance, report, and template

Examples

No Star Join (default)

select distinct a11.PBTNAME PBTNAME

from STORE_ITEM_PTMAP a11

where a11.YEAR_ID in (1994)

select a11.ITEM_NBR ITEM_NBR,

 a11.CLASS_NBR CLASS_NBR,

 a13.ITEM_DESC ITEM_DESC,

 a13.CLASS_DESC CLASS_DESC,

 a11.STORE_NBR STORE_NBR,

 a14.STORE_DESC STORE_DESC,

 sum(a11.REG_SLS_DLR) WJXBFS1

from STORE_ITEM_94 a11, 

 LOOKUP_DAY a12, 

 LOOKUP_ITEM a13, 

 LOOKUP_STORE a14

where a11.CUR_TRN_DT = a12.CUR_TRN_DT and 

 a11.CLASS_NBR = a13.CLASS_NBR and 

 a11.ITEM_NBR = a13.ITEM_NBR and 

 a11.STORE_NBR = a14.STORE_NBR

 and a12.YEAR_ID in (1994)

group by a11.ITEM_NBR,

 a11.CLASS_NBR,

 a13.ITEM_DESC,

 a13.CLASS_DESC,

 a11.STORE_NBR,

 a14.STORE_DESC

Partial Star Join

select distinct a11.PBTNAME PBTNAME

from STORE_ITEM_PTMAP a11, 

 LOOKUP_YEAR a12

where a11.YEAR_ID = a12.YEAR_ID

 and a11.YEAR_ID in (1994)

Pass1 - Duration: 0:00:00.49

select a11.ITEM_NBR ITEM_NBR,

 a11.CLASS_NBR CLASS_NBR,

 a13.ITEM_DESC ITEM_DESC,

 a13.CLASS_DESC CLASS_DESC,

 a11.STORE_NBR STORE_NBR,

 a14.STORE_DESC STORE_DESC,

 sum(a11.REG_SLS_DLR) WJXBFS1

from STORE_ITEM_94 a11, 

 LOOKUP_DAY a12, 

 LOOKUP_ITEM a13, 

 LOOKUP_STORE a14

where a11.CUR_TRN_DT = a12.CUR_TRN_DT and 

 a11.CLASS_NBR = a13.CLASS_NBR and 

 a11.ITEM_NBR = a13.ITEM_NBR and 

 a11.STORE_NBR = a14.STORE_NBR

and a12.YEAR_ID in (1994)

group by a11.ITEM_NBR,

 a11.CLASS_NBR,

 a13.ITEM_DESC,

 a13.CLASS_DESC,

 a11.STORE_NBR,

 a14.STORE_DESC