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

Question

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?

Solution 1

My requirement have been added into DB2 9.7.2 already.

DB2 9.7.2 adds new syntax for limit query result as illustrate below:

SELECT * FROM TABLE LIMIT 5 OFFSET 20

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

Solution 2

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

Solution 3

Not sure why you are creating the TMP table. Isn't RUNNING_NO aready in ascending sequence? 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

would give the same results.

Having an INDEX over NAME on the EMPLOYEE table will boost performance of this query.

Comments

[CodingWithSpike]

I think 'LIMIT' only works if MySQL compatibility is enabled? Running DB2 9.7.3 does not seem to support this by default (SQL0104N An unexpected token "LIMIT" was found following "".)

[AngocA]

Yes; it works in the DB2 9.7.4. This is an article it talks about this new feature: ibm.com/developerworks/data/library/techarticle/…

[AngocA]

In seems that it only works for DB2 Express-C and you have to change the compatibility_vector to mys, as it is explained in the article.

[Lukas Eder]

You really shouldn't use this MySQL syntax in DB2, if you can avoid it. Better use DB2's own paging syntax...

[Beez]

Hey, @Paul, this is a great example! Thank you. Is the ORDER BY NAME DESC redundant in the outer query?

[Polykrom]

probably so but I'm just copying his example without the tmp table. Could use just DESC instead.

[suhas0sn07]

I observed latency of close to 10 seconds for 2.3 million records. Reason for upvote is it worked without changing core database settings. :)

[bluelurker]

Is there a way to specify offset and range ?

[pratham gn]

Does this work on Union of two tables. Please suggest the changes because ROW_NUMBER() should be added to all queries of the union. Please suggest.