rownum / fetch first n rows

Question

select * from Schem.Customer 
  where cust='20' and cust_id >= '890127'
  and rownum between 1 and 2 order by cust, cust_id;

Execution time appr 2 min 10 sec

   select * from Schem.Customer where cust='20' 
   and cust_id >= '890127' 
   order by cust, cust_id fetch first 2 rows only ;

Execution time appr 00.069 ms

The execution time is a huge difference but results are the same. My team is not adopting to later one. Don't ask why.

So what is the difference between Rownum and fetch first 2 rows and what should I do to improve or convince anyone to adopt.

DBMS : DB2 LUW

Answer 1

Although both SQL end up giving same resultset, it only happens for your data. There is a great chance that resultset would be different. Let me explain why.

I will make your SQL a little simpler to make it simple to understand:

SELECT * FROM customer
WHERE ROWNUM BETWEEN 1 AND 2;

In this SQL, you want only first and second rows. That's fine. DB2 will optimize your query and never look rows beyond 2nd. Because only first 2 rows qualify your query.

Then you add ORDER BY clause:

SELECT * FROM customer
WHERE ROWNUM BETWEEN 1 AND 2;
ORDER BY cust, cust_id;

In this case, DB2 first fetches 2 rows then order them by cust and cust_id. Then sends to client(you). So far so good. But what if you want to order by cust and cust_id first, then ask for first 2 rows? There is a great difference between them.

This is the simplified SQL for this case:

SELECT * FROM customer
ORDER BY cust, cust_id
FETCH FIRST 2 ROWS ONLY;

In this SQL, ALL rows qualify the query, so DB2 fetches all of the rows, then sorts them, then sends first 2 rows to client.

In your case, both queries give same results because first 2 rows are already ordered by cust and cust_id. But it won't work if first 2 rows would have different cust and cust_id values.

A hint about this is FETCH FIRST n ROWS comes after order by, that means DB2 orders the result then retrieves first n rows.