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 Aug 3 '20 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 Aug 4 '20 at 20:42
  • It does what it says -- it buffers rows that constitute a window in order to calculate the window function. – mustaccio Aug 4 '20 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.