Eliminate predicate propulsion

Today, using the example from the first blog post, let's see how to eliminate predicate advancement. First up, this is an example of predicate progression.

sql：

SELECT COUNT(1) AS count

FROM (

SELECT a.ykf272, a.akb020, a.akb021, a.yka094, a.ake005

, a.yke936, a.ykf282, a.aac003, a.aac001, a.aac002

, a.akc225, a.akc226, a.ykf275, a.ykf281, a.ykf284

, a.ykf283, a.yke453, a.aae011, a.aae036, a.yke923

, a.yke925, a.yab003, b.yka059, b.yka002, b.yke627

, b.yka095, a.yke938, a.yke940, x.ake006

, pkg_common.fun_getAaa103('YKF062', x.ykf062) AS ykf062

, (

SELECT c.aaa103

FROM bm_ykf063 c

WHERE b.ykf063 = c.ykf063

) AS ykf063, b.yka007, x.ykf064

, (

SELECT pkg_common.fun_getAaa103('AKA070', c.aka070)

FROM ka02 c

WHERE b.yka002 = c.yka002

) AS aka070, pkg_common.fun_getAaa103('YKE469', b.yke469) AS yke469, b.aka074

FROM kz37k1 a, ka05 b, (

SELECT akb020, ake005, yka094, ake006, ykf062

, ykf064

FROM (

SELECT x.akb020, x.ake005, x.yka094, x.ake006, x.ykf064

, x.ykf062, DENSE_RANK() OVER (PARTITION BY x.akb020, x.ake005, x.yka094 ORDER BY aae030 DESC) AS r

FROM kb05k1 x

)

WHERE r = 1

) x

WHERE a.yka094 = b.yka094

AND a.akb020 = x.akb020

AND a.ake005 = x.ake005

AND a.yka094 = x.yka094

AND a.akb020 = '855855'

AND a.yab003 = '0001'

AND trunc(a.aae036, 'dd') >= '08-APR-18'

AND trunc(a.aae036, 'dd')