rownum/获取前n行 [英] rownum / fetch first n rows

问题描述

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

执行时间约2分10秒

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

执行时间约00.069毫秒

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.

那么Rownum和获取前两行之间有什么区别，我应该怎么做才能改善或说服任何人采用.

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

DBMS : DB2 LUW

推荐答案

尽管两个SQL最终都给出了相同的结果集，但这仅适用于您的数据.结果集很有可能会有所不同.让我解释一下原因.

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.

为了简化理解，我将简化您的SQL:

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

SELECT * FROM customer
WHERE ROWNUM BETWEEN 1 AND 2;

在此SQL中，只需要第一行和第二行.没关系.DB2将优化您的查询，并且永远不会查找第二个以后的行.因为只有前两行符合条件您的查询.

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.

然后添加 ORDER BY 子句:

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

在这种情况下，DB2首先获取2行，然后按cust和cust_id对其进行排序.然后发送给客户(您).到现在为止还挺好.但是，如果要先按cust和cust_id排序，然后再要求前2行怎么办?它们之间有很大的区别.

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.

这是此情况下的简化SQL:

This is the simplified SQL for this case:

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

在此SQL中，所有行均符合查询条件，因此DB2获取所有行，然后对它们进行排序，然后将前2行发送给客户端.

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.

在您的情况下，两个查询都给出相同的结果，因为前两行已按cust和cust_id进行排序.但是，如果前两行具有不同的cust和cust_id值，将无法正常工作.

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.

关于此的提示是先按后先进行FETCH FIRST n ROWS ，这意味着DB2先对结果进行排序，然后检索前n行.

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

这篇关于rownum/获取前n行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！