How to Efficiently Select the Top 10 Records in Oracle with Filtering and Ordering?

Oracle Top 10 Record Selection: Filtering, Ordering, and Optimization

Retrieving the top ten records in Oracle is usually simple, but adding filters and prioritizing performance introduces challenges. A recent Stack Overflow question highlighted this, focusing on selecting the top ten records based on a specific column while excluding records meeting a certain condition.

The Original Query and its Shortcomings

The initial SQL query aimed to select unique records based on several criteria: non-null storage capacity, exclusion of records from a specific date, and descending order by storage capacity. The query, however, failed to correctly limit the results to the top ten.

SELECT DISTINCT 
  APP_ID, 
  NAME, 
  STORAGE_GB, 
  HISTORY_CREATED, 
  TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE  
FROM HISTORY WHERE 
      STORAGE_GB IS NOT NULL AND 
        APP_ID NOT IN (SELECT APP_ID
                       FROM HISTORY
                        WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009') 

Simply adding ROWNUM to this query wouldn't produce the correct top 10 because <code>ROWNUM is assigned before the ORDER BY clause is processed.

The Solution: A Subquery Approach

The effective solution employed a subquery to correctly apply filtering and ordering:

SELECT * FROM (
  SELECT DISTINCT 
  APP_ID, 
  NAME, 
  STORAGE_GB, 
  HISTORY_CREATED, 
  TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE  
  FROM HISTORY WHERE 
    STORAGE_GB IS NOT NULL AND 
      APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') ='06.02.2009')
  ORDER BY STORAGE_GB DESC )
WHERE ROWNUM <= 10

This nested structure ensures that the records are ordered first, then the top ten are selected.

Performance Improvements

Beyond the ordering correction, using EXISTS instead of NOT IN significantly enhances performance. EXISTS generally offers better optimization opportunities, minimizing joins and improving query execution speed.

The above is the detailed content of How to Efficiently Select the Top 10 Records in Oracle with Filtering and Ordering?. For more information, please follow other related articles on the PHP Chinese website!