How to query a range of data in DB2 with the highest performance?

Question

Usually I need to get data from a table in a certain range; for example, a separate page for each search result. In MySQL, I use the LIMIT keyword, but in DB2 I do not know. Now I use this query to get a 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 this is a bad style. So how to request the highest performance?

Answer 1

My requirements have been added to DB2 9.7.2 already.

DB2 9.7.2 adds new syntax for the result of a terminal query, as shown below:

 SELECT * FROM TABLE LIMIT 5 OFFSET 20 

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

Answer 2

Not sure why you are creating a TMP table. Is RUNNING_NO running in ascending order? I would think:

 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 ONLY 

will give the same results.

The presence of the INDEX index over NAME in the EMPLOYEE table will improve the performance of this query.

Answer 3

This is very difficult, depending on which database you have.

eg:

 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