такой запрос отработал за 25 сек
SELECT ID, D
FROM ( SELECT ID, D
FROM (SELECT ID, D
FROM ( SELECT dk_idd id, nkniga D
FROM debet_kredit
ORDER BY ID DESC)
WHERE ROWNUM <= 30)
ORDER BY ID ASC)
WHERE ROWNUM <= 10
такой за 5 сек
SELECT ID, dk.nkniga d
FROM ( SELECT ID
FROM (SELECT ID
FROM ( SELECT dk_idd id
FROM debet_kredit
ORDER BY ID DESC)
WHERE ROWNUM <= 30)
ORDER BY ID ASC) t, debet_kredit dk
WHERE ROWNUM <= 10
and dk.dk_idd = t.id
а такой за 0.001 сек
SELECT ID, D
FROM ( SELECT ID, D
FROM (SELECT ID, D
FROM ( SELECT dk_idd id, nkniga D
FROM debet_kredit
where dk_idd >= (SELECT max(dk_idd) id FROM debet_kredit)-100
ORDER BY ID DESC)
WHERE ROWNUM <= 30)
ORDER BY ID ASC)
WHERE ROWNUM <= 10
результаты идентичны
в таблице debet_kredit 3.5 млн строк, партицирована, dk_idd - PK
Выделенная желтым строка является довольно спорной