Hi,
I've looked around but haven't found anyone talking about performance issues specifically with the use of "IGNORE NULLS" within a LAG() analytical function in an inline view.
This is a cut down version of my sql, isolated to the issue in question.
select * from (
SELECT /* use_nl (j,gjt,jt) */
jt.id
,COALESCE
(lag(jt.my_column ignore nulls /* this is the nasty critter */
) over (order by jt.order_seq)
,0
)+1 AS start_days
,coalesce
(jt.my_column
,last_value(jt.my_column ignore nulls
) over (order by jt.order_seq desc)
) AS end_days
FROM a,bunch,of,tables
WHERE ...
) jt
where jt.id = '123456'
If I remove the ignore nulls from the lag, there is no performance issue.
Last_value() is not affected.
If I don't use an inline view then performance is fine, although there is no different in explain plan.
select ..
from ...
where ...
and id = '123456'
Has anyone encountered something like this, or have any suggestions?
Happy to try build a test case if it helps discussion, but I thought I'd just pose the question first.
Scott