When you want only 10 rows of query result.
Mysql: Select top 10 * from foo.... 😁
Sql server: select top 10 * from foo.. 😁
PostgreSQL: select * from foo limit 10.. 😁

Oracle: select * from foo FETCH NEXT/FIRST 10 ROWS ONLY. 🌚

Oracle, are you trying to be more expressive/verbose because if that's the case then your understanding of verbosity is fucked up just like your understanding of clean-coding, user experience, open source, productivity...

  • 3

    select * from foo where rownum < 11;

    -- at least from 11g. IDK about older ones
  • 2
    It used to be a lot worse

    Select *
    From (
    Select *
    From actual_table
    Order by id
    Where ROWNUM <= 10
  • 2
    In the first versions when the fetch first was introduced I've read a blog post that the rownum version had better performance.
  • 3
    The optimizer didn't really work well for small sets with FETCH FIRST. If you didn't supply a limit, it would run the full table before finding a single id. I can't say if it's better now, but as long as you supply cardinality and row only, it won't be too bad.
  • 1
    @SortOfTested reminds me of postgresql :D (apparently this subquery-based-approach performs significantly better than a limitation w/o a subquery)
  • 0
    As long as it's off an indexed field, yes. Otherwise it will likely construct an index as part of the operation.
Add Comment