SQL 如何以最高性能查询 DB2 中的数据范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/265850/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to query range of data in DB2 with highest performance?
提问by Fuangwith S.
Usually, I need to retrieve data from a table in some range; for example, a separate page for each search result. In MySQL I use LIMIT keyword but in DB2 I don't know. Now I use this query for retrieve range of data.
通常，我需要从某个范围内的表中检索数据；例如，每个搜索结果都有一个单独的页面。在 MySQL 中我使用 LIMIT 关键字，但在 DB2 中我不知道。现在我使用这个查询来检索数据范围。
SELECT *
FROM(
SELECT
SMALLINT(RANK() OVER(ORDER BY NAME DESC)) AS RUNNING_NO
, DATA_KEY_VALUE
, SHOW_PRIORITY
FROM
EMPLOYEE
WHERE
NAME LIKE 'DEL%'
ORDER BY
NAME DESC
FETCH FIRST 20 ROWS ONLY
) AS TMP
ORDER BY
TMP.RUNNING_NO ASC
FETCH FIRST 10 ROWS ONLY
but I know it's bad style. So, how to query for highest performance?
但我知道这是不好的风格。那么，如何查询以获得最高性能呢？
回答by Fuangwith S.
My requirement have been added into DB2 9.7.2 already.
我的需求已经添加到 DB2 9.7.2 中了。
DB2 9.7.2 adds new syntax for limit query result as illustrate below:
DB2 9.7.2 为限制查询结果添加了新语法，如下图所示：
SELECT * FROM TABLE LIMIT 5 OFFSET 20
the database will retrieve result from row no. 21 - 25
数据库将从行号检索结果。21 - 25
回答by Paul Morgan
Not sure why you are creating the TMP table. Isn't RUNNING_NO aready in ascending sequence? I would think:
不确定为什么要创建 TMP 表。RUNNING_NO 不是按升序排列的吗？我会想：
SELECT SMALLINT(RANK() OVER(ORDER BY NAME DESC)) AS RUNNING_NO,
DATA_KEY_VALUE,
SHOW_PRIORITY
FROM EMPLOYEE
WHERE NAME LIKE 'DEL%'
ORDER BY NAME DESC
FETCH FIRST 10 ROWS ONLYwould give the same results.
会给出相同的结果。
Having an INDEX over NAME on the EMPLOYEE table will boost performance of this query.
在 EMPLOYEE 表上的 NAME 上有一个 INDEX 将提高此查询的性能。
回答by Pixie
It's very difficult, it is depends which database do you have.
这很困难，这取决于你有哪个数据库。
for example:
例如：
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY ID_USER ASC) AS ROWNUM,
ID_EMPLOYEE, FIRSTNAME, LASTNAME
FROM EMPLOYEE
WHERE FIRSTNAME LIKE 'DEL%'
) AS A WHERE A.rownum
BETWEEN 1 AND 25
回答by Kélisson Jean
It's simple, to make a pagination just follow the syntax.
很简单，只需按照语法进行分页即可。
LIMIT (pageSize) OFFSET ((currentPage) * (pageSize))
