X

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 ( 4 )
  • 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,
    Randolf
  • 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.
    Regards,
    Nigel.
  • Rajeshwaran, Jeyabal Monday, December 10, 2018
    Hi Nigel,

    with the FIRST_ROWS(N) hints in place, the costing of the "New-School" approach is very similar to the "old school" approach - is so then, do we still need this patch?

    demo@ORA12C> create table t as select * from all_objects;

    Table created.

    demo@ORA12C> create index t_idx on t( owner,object_type,object_id);

    Index created.

    demo@ORA12C> set autotrace traceonly explain
    demo@ORA12C> select *
    2 from (
    3 select object_name,created,last_ddl_time
    4 from t
    5 where owner ='SYS'
    6 order by object_type,object_id
    7 )
    8 where rownum 20)
    2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_TYPE","OBJECT_ID")=0) THEN 20 ELSE 0 END +10)
    4 - access("OWNER"='SYS')

    demo@ORA12C> select /*+ first_rows(10) */ object_name,created,last_ddl_time
    2 from t
    3 where owner ='SYS'
    4 order by object_type,object_id
    5 offset 20 rows fetch first 10 rows only;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 877782538

    ---------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ---------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 10 | 1230 | 4 (0)| 00:00:01 |
    |* 1 | VIEW | | 10 | 1230 | 4 (0)| 00:00:01 |
    |* 2 | WINDOW NOSORT STOPKEY | | 10 | 720 | 4 (0)| 00:00:01 |
    | 3 | TABLE ACCESS BY INDEX ROWID| T | 10 | 720 | 4 (0)| 00:00:01 |
    |* 4 | INDEX RANGE SCAN | T_IDX | 2413 | | 3 (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"=0) THEN 20 ELSE 0 END +10 AND "from$_subquery$_002"."rowlimit_$$_rownumber
    ">20)
    2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_TYPE","OBJECT_ID")=0) THEN 20 ELSE 0 END +10)
    4 - access("OWNER"='SYS')

    demo@ORA12C>
    demo@ORA12C> set autotrace off
    demo@ORA12C>
  • Nigel Bayliss Monday, December 10, 2018
    Hi - that's a good question. If you are happy to use the hint and you get the plan you want - then you won't need the patch. The patch (in common with the hint) adjusts the costing, which in turn makes the index plan more likely. Using FIRST_ROWS(10) in the before.sql script (linked in the blog) yields the 'good' index-driven plan.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services