Oracle database's explain plan sometimes includes Window (Buffer)
. What does this operator do?
Here is the SQL. The primary keys are RECORD_DATE
, TYPE
and KEY
.
select
source.RECORD_DATE,
source.TYPE,
source.KEY,
source.AD,
source.VALUE,
source.STATE,
source.SUB_STATE_KEY,
source.AMOUNT
from
COMPUTE_DETAIL_FACT source
where
source.RECORD_DATE >= date '2018-12-09' and
source.RECORD_DATE < date '2019-02-25' and
source.TYPE = 'banana' and
source.KEY = 316598 and
not exists
(
select
0
from
(
select
lead(test.RECORD_DATE) over (order by test.RECORD_DATE) RECORD_DATE
from
COMPUTE_DETAIL_FACT test
where
test.RECORD_DATE >= date '2018-12-09' and
test.RECORD_DATE < date '2019-02-25' and
test.TYPE = source.TYPE and
test.KEY = source.KEY and
(test.AD = source.AD or (test.AD is null and source.AD is null)) and
(test.VALUE = source.VALUE or (test.VALUE is null and source.VALUE is null)) and
(test.STATE = source.STATE or (test.STATE is null and source.STATE is null)) and
(test.SUB_STATE_KEY = source.SUB_STATE_KEY or (test.SUB_STATE_KEY is null and source.SUB_STATE_KEY is null)) and
(test.AMOUNT = source.AMOUNT or (test.AMOUNT is null and source.AMOUNT is null))
) prev
where
source.RECORD_DATE = prev.RECORD_DATE
)
lead(test.RECORD_DATE) over (order by test.RECORD_DATE)
Window (Buffer)
operator does? If I look at a query plan and seeSort
, then I know the operator is going to put the rows in a particular order. So, what doesWindow (Buffer)
operator do? Is it a cache that keeps that last N rows?