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

Table Variable Deferred Compilation: When It Works

Milos Radivojevic gives us a good example of when table variable deferred compilation is a good thing: As mentioned in the previous article, SQL Server 2019 cardinality estimations for a table variable are based on actual table variable row counts. Therefore, in SQL Server 2019, we should expect better estimations and better plans for queries […]

Read More

Batch Mode Processing On Rowstore Tables

Dmitry Pilugin shares some thoughts on the expansion of batch mode processing to rowstore tables: The main advantages of Batch Mode are: Algorithms optimized for the multi-core modern CPUs; Better CPU cache utilization and increased memory throughput; Reduced number of CPU instructions per processed row. All these features make Batch Mode much faster than Row […]

Read More

Categories

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