Oracle & MySQL - Selecting Requested Number of Records from Database
Listing records from database causes critical performance bottlenecks when there are huge amount of data exists. For example, in an online shopping page, retrieving 1.000.000 products from database to application server surely takes too much time or even crash of the server with clients load. Instead of retrieving all products once, requesting only a part of product list is favorable. Below, SQL examples are selecting five rows between 5th and 10th rows from table on both Oracle and MySQL.
SELECT S.ROW_NUM, S.COLUMN1 FROM (SELECT (@ROWNUM:=@ROWNUM+1) AS ROW_NUM, COLUMN1 FROM DEMO_TABLE D, (SELECT @ROWNUM:=0) R ) S WHERE S.ROW_NUM <= 10 AND S.ROW_NUM > 5
SELECT A.ROW_NUM, A.COLUMN1 FROM (SELECT ROWNUM AS ROW_NUM, COLUMN1 FROM DEMO_TABLE ORDER BY COLUMN1) A WHERE A.ROW_NUM <= 10 AND A.ROW_NUM > 5