Oracle’s OFFSET .. FETCH Can be Slower than Classic ROWNUM Filtering

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

One of Oracle 12c’s coolest features was the introduction of the SQL standard OFFSET .. FETCH clause, as we can now write things like:

SELECT *
FROM film
ORDER BY film_id
FETCH FIRST 1 ROW ONLY

This is querying the Sakila database. Most other databases had this clause (or a non-standard version of it) for ages, e.g. MySQL with LIMIT. For all the different LIMIT syntaxes, check out the jOOQ manual.
Implementation wise, the Oracle folks chose to rewrite this clause as a simple window function filter. In principle, behind the scenes, the following is executed:
Teradata syntax

SELECT *
FROM film
QUALIFY row_number() OVER (ORDER BY film_id) = 1
ORDER BY film_id

Standard syntax

SELECT * — Except rn
FROM (
SELECT film.*, row_number() OVER (ORDER BY film_id) rn
FROM film
) t
WHERE rn = 1
ORDER BY film_id

This does definitely look much better than the “old” approach using ROWNUM filtering, which many of us have written for years:
Legacy Oracle syntax

SELECT t.*
FROM (
SELECT *
FROM film
ORDER BY film_id
) t
WHERE ROWNUM = 1

What I don’t like about this “old” approach is that we’re relying on the ORDER BY clause of a derived table, which should not guaranteed to be retained in the outer most query in my opinion (although it is, in Oracle, in this case).
So, having the SQL standard syntax is definitely good.
What’s the problem?
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”

X ITM Cloud News

Emily

Next Post

Beware of Hidden PL/SQL to SQL Context Switches

Sun Nov 24 , 2019
Spread the love          I recently stumbled upon a curious query on a customer’s productive Oracle database: SELECT USER FROM SYS.DUAL Two things caught my attention: The query was executed many billions of times per month, accounting for about 0.3% of that system’s load. That’s 0.3% for something extremely silly! I don’t […]
X- ITM

Cloud Computing – Consultancy – Development – Hosting – APIs – Legacy Systems

X-ITM Technology helps our customers across the entire enterprise technology stack with differentiated industry solutions. We modernize IT, optimize data architectures, and make everything secure, scalable and orchestrated across public, private and hybrid clouds.

This image has an empty alt attribute; its file name is x-itmdc.jpg

The enterprise technology stack includes ITO; Cloud and Security Services; Applications and Industry IP; Data, Analytics and Engineering Services; and Advisory.

Watch an animation of  X-ITM‘s Enterprise Technology Stack

We combine years of experience running mission-critical systems with the latest digital innovations to deliver better business outcomes and new levels of performance, competitiveness and experiences for our customers and their stakeholders.

X-ITM invests in three key drivers of growth: People, Customers and Operational Execution.

The company’s global scale, talent and innovation platforms serve 6,000 private and public-sector clients in 70 countries.

X-ITM’s extensive partner network helps drive collaboration and leverage technology independence. The company has established more than 200 industry-leading global Partner Network relationships, including 15 strategic partners: Amazon Web Services, AT&T, Dell Technologies, Google Cloud, HCL, HP, HPE, IBM, Micro Focus, Microsoft, Oracle, PwC, SAP, ServiceNow and VMware

.

X ITM