如何以最高性能查询 DB2 中的数据范围? [英] How to query range of data in DB2 with highest performance?

问题描述

通常，我需要从某个范围内的表中检索数据；例如，每个搜索结果都有一个单独的页面.在 MySQL 中我使用 LIMIT 关键字，但在 DB2 中我不知道.现在我使用这个查询来检索数据范围.

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.

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?

推荐答案

我的需求已经添加到 DB2 9.7.2 中.

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:

SELECT * FROM TABLE LIMIT 5 OFFSET 20

数据库将从行号检索结果.21 - 25

the database will retrieve result from row no. 21 - 25

这篇关于如何以最高性能查询 DB2 中的数据范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！