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))
Alexander Anokhin 