-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major
-
None
-
Affects Version/s: 3.4.0
-
Component/s: SQL
Transforms the SelfJoin resulting in duplicate rows used for IN predicate to aggregation.
For IN predicate, duplicate rows does not have any value. It will be overhead.
Ex: TPCDS Q95: following CTE is used only in IN predicates for only one column comparison (
{@code ws_order_number}).
This results in exponential increase in Joined rows with too many duplicate rows.
WITH ws_wh AS
(
SELECT ws1.ws_order_number,
ws1.ws_warehouse_sk wh1,
ws2.ws_warehouse_sk wh2
FROM web_sales ws1,
web_sales ws2
WHERE ws1.ws_order_number = ws2.ws_order_number
AND ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
Could be optimized as below:
WITH ws_wh AS
(SELECT ws_order_number
FROM web_sales
GROUP BY ws_order_number
HAVING COUNT(DISTINCT ws_warehouse_sk) > 1)
Optimized CTE scans table only once and results in unique rows.