Home > Oracle > row_number < n

row_number < n

It’s about feature of WINDOW NOSORT STOPKEY and difference between row_number() = 1 and row_number() < 2.

This post related with a question on sql.ru which I answered a coulpe days ago.

Preparing of data:

SQL> create table test (id not null, val) pctfree 99
  2    as select level, level
  3         from dual
  4      connect by level <= 1000;

Table created

SQL> create index test_idx on test(id);

Index created

First, I try to use row_number() = 1

SQL> select id, val
  2    from (select --+ index(t test_idx)
  3                 id,
  4                 val,
  5                 row_number() over(order by id) as rn
  6            from test t)
  7   where rn = 1;

        ID        VAL
---------- ----------
         1          1

runtime execution plan of the query:

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |      1 |00:00:00.01 |       3 |
|*  1 |  VIEW                         |          |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   WINDOW NOSORT STOPKEY       |          |      1 |      1 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST     |      1 |      2 |00:00:00.01 |       3 |
|   4 |     INDEX FULL SCAN           | TEST_IDX |      1 |      2 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<=1)

As we can see, step 2 WINDOW NOSORT STOPKEY returns 1 row to parent rowsource after getting of 2 rows from child rowsource.

Now compare this with construction “row_number() < 2” which some developers prefer to use.

SQL> select id, val
  2    from (select --+ index(t test_idx)
  3                 id,
  4                 val,
  5                 row_number() over(order by id) as rn
  6            from test t)
  7   where rn < 2;

        ID        VAL
---------- ----------
         1          1

And runtime execution plan of the query:

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |      1 |00:00:00.01 |       3 |
|*  1 |  VIEW                         |          |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   WINDOW NOSORT STOPKEY       |          |      1 |      2 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST     |      1 |      3 |00:00:00.01 |       3 |
|   4 |     INDEX FULL SCAN           | TEST_IDX |      1 |      3 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"<2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<2)

In this case step 2 “WINDOW NOSORT STOPKEY” returns 2 rows to parent rowsource after getting of 3 rows from child rowsource.

Obviously in some cases this little difference (just one row) can lead to the large difference in the amount of work and, consequently, possible performance degradation of a query.
One example provided in thread on sql.ru. Child rowsource is “GROUP BY NOSORT” which needs to read N additional rows from an index and a table to return the third grouped value.
Another example (below) shows case where for return the third row Oracle should read and filter a lot of rows thanks to filter predicates.

SQL> select id, val
  2    from (select --+ index(t test_idx)
  3                 id,
  4                 val,
  5                 row_number() over(order by id) as rn
  6            from test t
  7           where val <= 2 or val > 900)
  8   where rn < 2;

        ID        VAL
---------- ----------
         1          1

 

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |      1 |00:00:00.01 |     180 |
|*  1 |  VIEW                         |          |      1 |      1 |00:00:00.01 |     180 |
|*  2 |   WINDOW NOSORT STOPKEY       |          |      1 |      2 |00:00:00.01 |     180 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TEST     |      1 |      3 |00:00:00.01 |     180 |
|   4 |     INDEX FULL SCAN           | TEST_IDX |      1 |    901 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"<2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<2)
   3 - filter(("VAL"<=2 OR "VAL">900))

Pay attention, 901 rows were fetched from the table, 3 + 180 logical reads were done, to check predicate “filter((“VAL”<=2 OR “VAL”>900))” and return the third row.

Compare the same query with rn = 1 instead of rn < 2

SQL> select id, val
  2    from (select --+ index(t test_idx)
  3                 id,
  4                 val,
  5                 row_number() over(order by id) as rn
  6            from test t
  7           where val <= 2 or val > 900)
  8   where rn = 1;

        ID        VAL
---------- ----------
         1          1
-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|*  1 |  VIEW                         |          |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   WINDOW NOSORT STOPKEY       |          |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TEST     |      1 |      2 |00:00:00.01 |       3 |
|   4 |     INDEX FULL SCAN           | TEST_IDX |      1 |      2 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<=1)
   3 - filter(("VAL"<=2 OR "VAL">900))

Only 2 rows were fetched from index TEST_IDX and only 3 logical reads only were done.

p.s.
By the way, usual rownum and COUNT STOPKEY read only 1 row from child rowsource in both cases.

SQL> select id, val
  2    from (select --+ index(t test_idx)
  3                 id,
  4                 val
  5            from test t
  6           where val <= 2 or val > 900)
  7   where rownum < 2;

        ID          N
---------- ----------
         1          1
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |      1 |00:00:00.01 |       3 |
|*  1 |  COUNT STOPKEY               |          |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TEST     |      1 |      1 |00:00:00.01 |       3 |
|   3 |    INDEX FULL SCAN           | TEST_IDX |      1 |      1 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<2)
   2 - filter(("VAL"<=2 OR "VAL">900))
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: