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
.
part_code
andfunc_coll
in theorder by
for that. Why do you have the hint in there - is the index not taken otherwise? What sort of stats do you have on the relevant objects?part_code
(redundant withdate
) andfunc_col1
(once again not functionally relevant because of the=
) in theorder by
clause if it helps.