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 秒

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 ;

执行时间大约 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 和 fetch first 2 rows 有什么区别，我应该做些什么来改进或说服任何人采用.

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

数据库管理系统: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 将优化您的查询并且永远不会查看超过 2nd 的行.因为只有前 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.

然后你添加 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.

在您的情况下，两个查询都给出相同的结果，因为前 2 行已经按 cust 和 cust_id 排序.但如果前 2 行具有不同的 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 来自 order by，这意味着 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屋！