1

Oracle database's explain plan sometimes includes Window (Buffer). What does this operator do?

Explain Plan

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
   )      
3
  • I think the answer should be clear now: it's about computing the window function lead(test.RECORD_DATE) over (order by test.RECORD_DATE)
    – mustaccio
    Commented Aug 3, 2020 at 19:39
  • Um, yes. I knew that. But the question is about what the Window (Buffer) operator does? If I look at a query plan and see Sort, then I know the operator is going to put the rows in a particular order. So, what does Window (Buffer) operator do? Is it a cache that keeps that last N rows?
    – Nathan
    Commented Aug 4, 2020 at 20:42
  • It does what it says -- it buffers rows that constitute a window in order to calculate the window function.
    – mustaccio
    Commented Aug 4, 2020 at 21:41

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.