Mohamed Houri’s Oracle Notes

March 20, 2014

Window SORT and Window BUFFER

Filed under: Oracle — hourim @ 2:55 pm

I was thinking that I will see a WINDOW SORT operation in an execution plan for every over ( ) clause statement that differs in the partition and the order by options. For example

SQL> select
        id
       ,n_5000
       ,lead(id) over (partition by n_5000 order by id)
     from t1
     where n_5000 = 1778;

---------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    94 (100)|
|   1 |  WINDOW SORT          |        |    22 |   572 |    94   (6)|
|*  2 |   INDEX FAST FULL SCAN| MY_IND |    22 |   572 |    93   (5)|
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N_5000"=1778)

But it suffices to have (1) an index that starts by the order by column (id) or by the partition by column(n_5000) and (2) add a predicate on the order by column or on the partition by column to that original query and the WINDOW SORT will be transformed into a less costly WINDOW BUFFER.

SQL> select
        id
       ,n_5000
       ,lead(id) over (partition by n_5000 order by id)
    from t1
    where id = 1778;

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |
|   1 |  WINDOW BUFFER    |        |     1 |    26 |     3  (34)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| MY_IND |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1778)

That’s pretty straightforward. The index MY_IND (id, n_5000, n_10000) has been used to avoid the WINDOW SORT analytical operation. This transforms my initial thinking to: “I will see a WINDOW SORT operation in an execution plan for every over () clause statement that differs in the partition by and the order by options unless the CBO finds a suitable index that permits bypassing a SORT operation”

But does this mean that I will not see a parent WINDOW BUFFER operation without a child index scan operation?

SQL> select
       id
      ,n_5000
      ,padding
      ,sum(id) over (partition by n_5000)
     from t1
     where n_5000 = 444;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   478 (100)|          |
|   1 |  WINDOW BUFFER     |      |    20 |  2200 |   478   (2)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| T1   |    20 |  2200 |   477   (2)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N_5000"=444)

I get rid of the order by option in the over() operation and added a predicate on the partition by column.

And maybe I can add this:

I will NOT see a WINDOW SORT operation in an execution plan for every over () clause statement that contains ONLY a partition by option and where the container query includes the partition by column in the predicate part”.

Bottom Line: from now and on when I see an over () clause statement I will be paying more attention to the partition by clause to see if its related column is in the predicate part or not. It might explain why I have a WINDOW SORT instead of a WINDOW BUFFER

FootNote:  I have to warn that the above conclusions, despite they might be correct, they nevertheless remains to be sourced from a one day experiment. As such you should consider them with a careful attention before taken them as definitely demonstrated. It is not because they have been published that they are correct.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: