Version 2021

Set Operator Optimization

Set Operator Optimization 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 Set Operator Optimization property determines whether to use set operators, such as EXCEPT and INTERSECT, to combine multiple filter qualifications rather than their equivalent logical operators such as AND NOT and AND. Set operators can be used to combine two or more of the following types of set qualifications:

  • Relationship qualifications
  • Metric qualifications when combined with other types of set qualifications with the logical operators AND, NOT, or OR
  • Report as filter qualifications when combined with the logical operators AND, NOT, or OR
  • Set operators can only be used to combine the filter qualifications listed above if they have the same output level. For example, a relationship qualification with an output level set to Year and Region cannot be combined with another relationship qualification with an output level of Year.
  • Metric qualifications and report-as-filter qualifications, when combined with AND, render as inner joins by default to avoid a subquery in the final result pass. When Set Operator Optimization is enabled, the inner joins are replaced by subqueries combined using INTERSECT.
  • Metric qualifications at the same level are combined into one set qualification before being applied to the final result pass. This is more efficient than using a set operator. Consult MicroStrategy Tech Note TN13536 for more details.
  • For more information on filters and filter qualifications, see the Advanced Filters section of the MicroStrategy Advanced Reporting Guide.

Along with the restrictions described above, SQL set operators also depend on the subquery type and the database platform. For more information on sub query type, see Set Operator Optimization. Set Operator Optimization can be used with the following sub query types:

  • WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT * ...) for multiple columns IN
  • WHERE (COL1, COL2...) IN (SELECT s1.COL1, s1.COL2...)
  • WHERE COL1 IN (SELECT s1.COL1...) falling back to EXISTS (SELECT col1, col2 ...) for multiple columns IN
  • Use Temporary Table, falling back to IN (SELECT COL) for correlated sub query

    If either of the two sub query types that use fallback actions perform a fallback, Set Operator Optimization is not applied.

The following database platforms support SQL set operators:

Database Intersect Intersect ALL Except Except ALL Union Union ALL

ANSI 92

Yes

Yes

Yes

Yes

Yes

Yes

DB2 UDB

Yes

Yes

Yes

Yes

Yes

Yes

Informix

No

No

No

No

Yes

Yes

Oracle

Yes

No

Yes (Minus)

No

Yes

Yes

RedBrick

Yes

Yes

Yes

Yes

Yes

Yes

SQL Server

Yes (2005 and later)

No

Yes (2005 and later)

No

Yes

Yes

Tandem

No

No

No

No

No

No

Teradata

Yes

Yes

Yes

Yes

Yes

Yes

If you enable Set Operator Optimization for a database platform that does not support operators such as EXCEPT and INTERSECT, the Set Operator Optimization property is ignored.

The Set Operator Optimization property provides you with the following options:

  • Disable Set Operator Optimization (default): Operators such as IN and AND NOT are used in SQL sub queries with multiple filter qualifications.
  • Enable Set Operator Optimization (if database support and [Sub Query Type]): This setting can improve performance by using SQL set operators such as EXCEPT, INTERSECT, and MINUS in SQL sub queries to combine multiple filter qualifications that have the same output level. All of the dependencies described above must be met for SQL set operators to be used. If you enable SQL set operators for a database platform that does not support them, this setting is ignored and filters are combined in the standard way with operators such as IN and AND NOT.

    For a further discussion on the Set Operator Optimization VLDB property, refer to MicroStrategy Tech Note TN13530.

Levels at Which You Can Set This

Database instance, report, and template