Cartesian Join Evaluation

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

This property allows the MicroStrategy SQL Engine to use a new algorithm for evaluating whether or not a Cartesian join is necessary. The new algorithm can sometimes avoid a Cartesian join when the old algorithm cannot. For backward compatibility, the default is the old algorithm. If you see Cartesian joins that appear to be avoidable, use this property to determine whether the engine's new algorithm avoids the Cartesian join.

Levels at Which You Can Set This

Database instance, report, and template

Examples

Do Not Reevaluate Cartesian Joins (default)

select a12.ATTR1_ID ATTR1_ID, 

max(a12.ATTR1_DESC) ATTR1_DESC, 

a13.ATTR2_ID ATTR2_ID, 

max(a13.ATTR2_DESC) ATTR2_DESC, 

count(a11.FACT_ID) METRIC 

from FACTTABLE a11 

cross join LU_TABLE1 a12 

join LU_TABLE2 a13 

on (a11.ATTR3_ID = a13.ATTR3_ID and 

a12.ATTR1_ID = a13.ATTR1_CD) 

group by a12.ATTR1_ID, 

a13.ATTR2_ID 

Reevaluate the Cartesian Joins

select a12.ATTR1_ID ATTR1_ID, 

max(a12.ATTR1_DESC) ATTR1_DESC, 

a13.ATTR2_ID ATTR2_ID, 

max(a13.ATTR2_DESC) ATTR2_DESC, 

count(a11.FACT_ID) METRIC 

from FACTTABLE a11 

join LU_TABLE2 a13 

on (a11.ATTR3_ID = a13.ATTR3_ID) 

join LU_TABLE1 a12 

on (a12.ATTR1_ID = a13.ATTR1_CD) 

group by a12.ATTR1_ID, 

a13.ATTR2_ID