In Strategy SQL Generation Engine, a VLDB (Very Large Data Base) property "Set Operator Optimization" provides the option to use database-side set operators to combine intermediate results sets representing set qualifications in filters.
Why set operators?
Some databases evaluate set operators more efficiently than the equivalent logical operators in SQL. For instance, consider two general queries such as the following:
The script file is empty. | The script file is empty. |
metric from fact table where (filtering_attribute in (select key from table1 where condition1)) AND NOT (filtering_attribute in (select key from table2 where condition2)) | metric from fact table where (filtering_attribute in (select key from table1 where condition1) EXCEPT (select key from table2 where condition2)) |
Some databases can apply optimization techniques to the special case of set operators which do not apply to the general case of logical operators applied to subquery conditions.
How can set operators be enabled?
The use of set operators is controlled by a VLDB (Very Large Data Base) property, "Set Operator Optimization," located in the Query Optimizations folder within the VLDB property editor. This property is available at database instance and report level.
NOTE: This property will be visible only if "Show Advanced Settings" is enabled in the Tools menu of the VLDB property editor, as shown below:
It has two options:
It is not necessary to specify set operators explicitly in the filter editor. In fact, there is no change in the filter editor itself. The relationships between qualifications are specified in terms of logical operators. When set operators apply, a logical operator in the filter definition is automatically translated into the corresponding set operator.
where condition2)) | where condition2)) |
where condition2)) | where condition2)) |
where condition2)) | where condition2)) |
where condition2)) | where condition2)) |
where condition2)) | where condition2)) |
EXCEPT and MINUS are equivalent in function, and their usage depends on the database platform.
When does Set Operator Optimization apply?
Set operators may be used to combine sets of data within a larger query, most typically when there are multiple SELECT clauses (subqueries) within the WHERE clause of a given query. In the Strategy SQL Generation Engine, subqueries are usually generated as the result of the following types of filter qualifications:
All of these qualifications fall under the general category of set qualifications. See the following sections of the Strategy PDF documentation distributed with the installation for complete details on the uses and construction of set qualifications:
Notes:
How does it affect SQL generation?
Consider a simple report with a pair of relationship filters:
Without the set operator optimization, this report generates the following SQL in Oracle:
select a12.QUARTER_ID QUARTER_ID,
a13.QUARTER_DESC QUARTER_DESC,
sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) WJXBFS1
from ORDER_DETAIL a11
join LU_DAY a12
on (a11.ORDER_DATE = a12.DAY_DATE)
join LU_QUARTER a13
on (a12.QUARTER_ID = a13.QUARTER_ID and
a12.YEAR_ID = a13.YEAR_ID)
where ((
(a11.CUSTOMER_ID) -- first relationship filter
in (select r11.CUSTOMER_ID
from ORDER_DETAIL r11
where r11.ITEM_ID in (184)))
and (
(a11.CUSTOMER_ID) -- second relationship filter
in (select r11.CUSTOMER_ID
from ORDER_DETAIL r11
where r11.ITEM_ID in (74)))
and a11.ITEM_ID in (184))
group by a12.QUARTER_ID,
a13.QUARTER_DESC
Set operator optimization changes the query as follows:
select a12.QUARTER_ID QUARTER_ID,
a13.QUARTER_DESC QUARTER_DESC,
sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) WJXBFS1
from ORDER_DETAIL a11
join LU_DAY a12
on (a11.ORDER_DATE = a12.DAY_DATE)
join LU_QUARTER a13
on (a12.QUARTER_ID = a13.QUARTER_ID and
a12.YEAR_ID = a13.YEAR_ID)
where ((a11.CUSTOMER_ID)
in ((
(select r11.CUSTOMER_ID
from ORDER_DETAIL r11
where r11.ITEM_ID in (184))
intersect
(select r11.CUSTOMER_ID
from ORDER_DETAIL r11
where r11.ITEM_ID in (74))))
and a11.ITEM_ID in (184))
group by a12.QUARTER_ID,
a13.QUARTER_DESC
Note the difference in the construction of the where clause:
where condition2)) | where condition2)) |
in (select ...)) and (Customer_ID in (select ...)) | in (select ...) intersect (select ...)) |
Which databases support set operators?
The following table details the databases that support specific set operators.
intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) |
intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) |
intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) |
intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) |
intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) |
intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) |
intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) | intersect (select ...)) |
