Many applications need to paginate rows fetched from the database, or at least retrieve the first N rows. In most cases the data needs to be returned in some kind of order too. If you are an old-school developer, then you are likely to use a variation on this theme:

select * from ( select * from the_table order by object_id ) where rownum <= 10;

It's not pretty, but it is effective. In fact, it is very effective if the column (or columns) in the ORDER BY have a b-tree index. Oracle reads the index entries in order so that it can avoid having to sort the entire result set. This can speed things up very considerably.

If you are new-school, then this is what you probably use instead:

select * from the_table order by object_id fetch first 10 rows only;

This is much prettier, but I'm afraid it has not always been as effective. The snag has been that the optimizer did not always cost this type of query correctly. This meant that the index access 'trick' was sometimes missed, potentially resulting in a large sort.

Now for the good news: a one-off patch (22174392) is available and it works with OFFSET too:

This change can have a dramatic effect on the performance for these types of queries, so it's well worth a look if you have FETCH FIRST ROWS queries.

I've uploaded a demo with before and after examples to GitHub.

Comments welcome!