Oracle SELECT TOP 10 records

Advanced performance tuning: Have a need for speed?

Working with large datasets requires efficient query construction. The usage of indexes in combination with functions such as TO_DATE (preserving the index usability better than TO_CHAR on date fields) can facilitate a performance boost.

Graduate from ROWNUM and wield the oracle analytic functions such as ROW_NUMBER() or RANK() for precise results:

SELECT your_columns FROM (
  SELECT your_columns, RANK() OVER (ORDER BY your_order_column) ranking FROM your_table
) WHERE ranking <= 10;


Our good ol’ friend Distinct comes handy when unique records are the dish of the day. It's better served after retrieving your top N rows to reduce processing overhead.

Dealing with subqueries? Use NOT EXISTS instead of NOT IN for the bell of the performance ball as it dances with NULL values more elegantly.

Harness the power of modern Oracle syntax

Starting from Oracle 12c, use the FETCH NEXT N ROWS ONLY clause for Indian Jones-level straightforwardness:

SELECT your_columns FROM your_table
ORDER BY your_order_column
FETCH NEXT 10 ROWS ONLY;


Remember, even SQL likes things efficient and sleek.

SQL Artistry: Crafting accurate and performant queries

Following the right sequence of steps

The cha-cha of queries follows the rhythm of filtering, ordering, and then limiting. Like in ballroom dancing, misplaced steps might lead to confusion and unexpected results.

Checking the query's health: Execution plan analysis

For queries that exhibit heavy lifting symptoms, a regular checkup of the execution plan can help identify and resolve any potential performance issues.

Serving sorted results hot with ROWNUM

Pairing ROWNUM with ORDER BY can mix up the dish. Our friend ROWNUM gets assigned before sorting, and that can lead to a random shuffle of the results:

SELECT * FROM your_table WHERE ROWNUM <= 10 ORDER BY some_column; 
-- 🎲 Roll the dice and get random records!


To get the right order, team up ROWNUM with a sorted subquery.