Suboptimal performance might be observed when running a query
with the following characteristics:
1. the query contains at least one outer join
2. the row preserving side column referenced in an outer join
predicate can be sourced down to a constant
3. between the outer join and the constant, there is a
sub-select containing OLAP functions
Example queries:
Query 1:
with
valueBox (const) as (VALUES (1))
select * from
(select row_number() over() as t1_1, t12, const from t1,
valueBox) t1_value
left join t2
on t1_value.t12=t2.t22 and t1_value.const = t2.t21
Query 2:
with
valueBox (const) as (VALUES (1))
select * from
(select row_number() over() as t1_1, t12, const from t1,
valueBox) t1_value
left join t2
on t1_value.t12=t2.t22 and t1_value.const = 1
work-around:
manually rewrite the query to replace the row preserving column
in the outer join predicate with the constant, and manually
eliminate the outer join predicate if possible. For ex., the
example queries can be rewritten to:
Rewritten query 1:
with
valueBox (const) as (VALUES (1))
select * from
(select row_number() over() as t1_1, t12, const from t1,
valueBox) t1_value
left join t2
on t1_value.t12=t2.t22 and 1 = t2.t21 -- manually replace
t1_value.const with "1"
Rewritten query 2:
with
valueBox (const) as (VALUES (1))
select * from
(select row_number() over() as t1_1, t12, const from t1,
valueBox) t1_value
left join t2
on t1_value.t12=t2.t22 -- manually eliminate "t1_value.const =
1" as it is an always true predicate |
