Fetch First Rows Just Got Faster

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 - there's a fix:

• The fix is included in Oracle Database 19c and onwards
• For Oracle Database 12c, there are on-off patches (22174392)

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.