OFFSET – FETCH Versus ROWNUM In Oracle

Lukas Eder compares the OFFSET FETCH logic versus using ROWNUM for grabbing an ordered sub-selection of rows in Oracle:

Now, while the SQL transformation from FETCH FIRST to ROW_NUMBER() filtering is certainly correct, the execution plan doesn’t really make me happy. Consider the ROWNUM based query:

---------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | COUNT STOPKEY | | |
| 2 | VIEW | | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| FILM | 1000 |
| 4 | INDEX FULL SCAN | PK_FILM | 1 |
---------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter(ROWNUM=1)

And compare that to the FETCH FIRST query:

-------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | VIEW | | 1 |
|* 2 | WINDOW SORT PUSHED RANK| | 1000 |
| 3 | TABLE ACCESS FULL | FILM | 1000 |
-------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "FILM"."FILM_ID")<=1)

Lukas digs into this and is not the biggest fan of OFFSET-FETCH.  On the SQL Server side, my anecdotal experience has been that it doesn’t perform nearly as well as you’d like either.

Related Posts

Nested Loops, Hash, Or Merge: Which Is Best?

Grant Fritchey dodges the important questions: First response, also a joke, was the question at the title of this post: What is the preferred operator when joining tables: Hash Match, Nested Loops or Merge? While my immediate response to this question is, yes. Meaning, they’re all preferred, situationally. I decided to expand on that a […]

Read More

COUNT And NULL Values

Kenneth Fisher digs into the COUNT() function and sees how it deals with NULL values: Count the number of values 1 2 3 SELECT COUNT(FieldName) FROM TableName; -- or SELECT COUNT(ALL FieldName) FROM TableName; The ALL argument is the default and is unnecessary (I didn’t even know it existed until I started this post). Here you are counting […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

June 2018
MTWTFSS
« May Jul »
 123
45678910
11121314151617
18192021222324
252627282930