Your second query variant should select the partition in table A. If that's not the case, something seems wrong. Could you provide a profile that shows the lack of partitioning pruning with the second query in your use case? The the first and second query variant have a different meaning, and hence different optimizations apply. The ON and WHERE clauses have very specific meanings in SQL, in particular with outer joins. The ON clause affects which rows are considered a "match" for he purpose of the outer join, so if you put "A.yearweek = 201710" in the ON clause, then those rows not satisfying that condition are considered a join non-match. The meaning of a LEFT OUTER JOIN is that the left side rows are returned even for join non-matches (with NULLs on the right side). The WHERE clause is logically applied *after* the FROM clause, so all rows produced by the FROM clause are filtered (including non-matches of the outer join, so we can move a WHERE-clause predicate on A into the scan in your second query variant).
... View more
