Greetings!
I have a question regarding LAG/LEAD functions in ver 12.2.
I've encountered a problem with the below query that executes in seconds as is, but if I would change the last filter from
WHERE ID_QLFY IS NOT NULL
to
WHERE ID_QLFY IS NULL
then the same query is hanging.
I also found out that with the same above change, it would still work fast if only I would remove the IGNORE NULLS from the LAG and LEAD functions.
So my question is what does IGNORE NULLS in the LAG/LEAD functions have to do with the query filter (WHERE ID_QLFY IS NULL), operating on one of the attributes in the table. My understanding was that IGNORE NULLS should apply only on the window function in the inline query.
select * from
(
select
ID,
ATTR,
ID_QLFY,
(LAG (ID_QLFY) IGNORE NULLS
OVER ( PARTITION BY SRC_CD
ORDER BY ATTR )) LAG_ID,
(LEAD (ID_QLFY) IGNORE NULLS
OVER ( PARTITION BY SRC_CD
ORDER BY ATTR )) LEAD_ID
from TBL
where DTE = TO_DATE('20210330, 'YYYYMMDD')
)
where ID_QLFY is not null;
When you have ignore nulls, LEAD/LAG will find the next non-null value in the result set. Without this they return whatever value is in the next row.
I'm not sure exactly what's going on in your example, here's one hypothesis:
When sorted by attr, the table has many consecutive null values for ID_QLFY. So it has to search more rows to find the next non-null when you have IGNORE NULLS. But remove this and it can return the next row every time.
To validate this we need to get more details about what's going in in your queries. To do this, we need to see the execution plans!
Please get the execution plans including the runtime stats (A-rows, A-time, etc.) for each of your queries and post them here.
Do this with the following script:
alter session set statistics_level = all;
set serveroutput off
<your query>
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));