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.

MYSQL SYNTAX

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

ORACLE SYNTAX

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