Insights into Statistics, Query Optimization and the Oracle Optimizer

Fetch First Rows Just Got Faster

Nigel Bayliss
Product Manager

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!



Join the discussion

Comments ( 2 )
  • Randolf Geist Sunday, October 7, 2018
    Hi Nigel,

    that is good news. Is this patch already included in 18c, or in some other upcoming version (19c etc.)?

    Kind regards,
  • Nigel Bayliss Monday, October 8, 2018
    Hi Randolf, good to here from you. Currently it is not included in Oracle Database 18c, so a one-off would need to be requested. I expect it to be fixed in the main code-base, but I can't make guarantees about precisely when.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Integrated Cloud Applications & Platform Services