I believe you should be aiming for a plan that avoid any actual sort operation, and "stops short" as soon as possible.
To avoid the sort (and "materializing" the inner view), your sort order must match exactly the index columns, or your where clauses must be strict equals only on all the leading columns. Otherwise there will be a need to sort subsets, and that will require evaluating the whole inner view.
Here's an example with a hypothetical foo table:
create table foo (a number, b number, c varchar(100));
-- fill with dummy data
exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');
create index foo_ix on foo(a, b, c);
Closest simple equivalent to your select:
select * from (
select rownum r, i.* from (
select a, b, c
from foo
where a in (3,4) and b = 3
order by c
) i
) where r between 5 and 10;
The explain plan isn't good:
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 163 | 14833 | 5 (20)| 00:00:01 |
|* 1 | VIEW | | 163 | 14833 | 5 (20)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | VIEW | | 163 | 12714 | 5 (20)| 00:00:01 |
| 4 | SORT ORDER BY | | 163 | 9291 | 5 (20)| 00:00:01 |
| 5 | INLIST ITERATOR | | | | | |
|* 6 | INDEX RANGE SCAN| FOO_IX | 163 | 9291 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R"<=10 AND "R">=5)
6 - access(("A"=3 OR "A"=4) AND "B"=3)
The count is too late, and isn't actually (I think) "stopping things short" in this case.
Add the index columns to the order (might not meet your requirements, sorry):
select * from (
select rownum r, i.* from (
select a, b, c
from foo
where a in (3,4) and b = 3
order by a, b, c
) i
) where r between 5 and 10;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 163 | 14833 | 4 (0)| 00:00:01 |
|* 1 | VIEW | | 163 | 14833 | 4 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | VIEW | | 163 | 12714 | 4 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
|* 5 | INDEX RANGE SCAN| FOO_IX | 163 | 9291 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R"<=10 AND "R">=5)
5 - access(("A"=3 OR "A"=4) AND "B"=3)
At least we got rid of the sort. Now let's try and "stop short":
select * from (
select rownum r, i.* from (
select a, b, c
from foo
where a in (3,4) and b = 3
order by a, b, c
) i where rownum < 10
) where r > 5;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 819 | 4 (0)| 00:00:01 |
|* 1 | VIEW | | 9 | 819 | 4 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 9 | 702 | 4 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
|* 5 | INDEX RANGE SCAN| FOO_IX | 9 | 513 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("R">5)
2 - filter(ROWNUM<10)
5 - access(("A"=3 OR "A"=4) AND "B"=3)
This might be enough for your query: notice the count stopkey (rownum < magic, doesn't kick in with between in what I've seen) and the "rows" column - the inner scan doesn't have to bother about fetching more rows after it's found N.
With the above, you'll still be reading N rows from the table.
You could limit that if all the search criteria are indexed: do the above filtering but retrieving only the ROWID from each match rather than all the columns, and then access the table by ROWID.
select * from foo where rowid in (
select rid from (
select rownum r, i.* from (
select rowid rid
from foo
where a in (3,4) and b = 3
order by a, b, c
) i where rownum < 10
) where r > 5
);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 6 (17)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 78 | 6 (17)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 9 | 108 | 4 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 225 | | |
|* 4 | VIEW | | 9 | 225 | 4 (0)| 00:00:01 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | VIEW | | 9 | 108 | 4 (0)| 00:00:01 |
| 7 | INLIST ITERATOR | | | | | |
|* 8 | INDEX RANGE SCAN | FOO_IX | 9 | 594 | 4 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY USER ROWID| FOO | 1 | 66 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("R">5)
5 - filter(ROWNUM<10)
8 - access(("A"=3 OR "A"=4) AND "B"=3)
This does not work if any of the search fields are not in the index. And make sure you trace this with real data & usual search criteria to see if it materially makes a difference - it might actually be worse especially for low values of M (which is probably the case you want to be the fastest) or high values of N-M.
Best Answer
The data returned should be always the same independent of the access paths used. It never depends. The result set is defined by the text of the sql statement. Hints are comments and do not influence what data is retrieved. Indexes are auxiliary data structures that facilitate data retrieval but have no influence on what data is returned. So this is a bug and you should contact Oracle support.