What is the optimal query for paging in Oracle?

Question

Oracle

Tell me the optimal query for page-by-page data output, what I found on the Internet does not work very fast.

Answer 1

If I understand you correctly, you are looking for an analogue of LIMIT, but under oracle ...
I once wrote such a crutch:

SELECT * FROM 
    (SELECT A.*, ROWNUM RNUM FROM
    (/*Основной запрос выборки со всеми JOIN, GROUP, HAVING, ORDER*/) A 
    WHERE ROWNUM <=5/*По какую запись выбирать (предел)*/) 
WHERE RNUM >=1/*С какой записи выбирать (начальная позиция)*/;

Somewhere still working successfully