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

Efficiently filter the top 10 records in Oracle database: subquery and ROWNUM technology

This article aims to solve the problem of sorting by STORAGE_GB from the HISTORY table, excluding rows that meet specific conditions, and ultimately selecting only the first 10 records. Using ROWNUM directly to limit the results before sorting can cause problems.

Solution using subquery:

The solution is to create a subquery that selects the required records based on all criteria including sort order. Then, use this subquery as the data source and use ROWNUM to select the first 10 rows.

<code class="language-sql">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;</code>

Use EXISTS for optimization:

For large data sets, Oracle's ROWNUM is not efficient. Consider using the EXISTS operator instead of NOT IN to improve performance.

<code class="language-sql">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 NOT EXISTS (
    SELECT 1
    FROM HISTORY
    WHERE
      APP_ID = APP_ID
      AND TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') = '06.02.2009'
  )
ORDER BY STORAGE_GB DESC
FETCH FIRST 10 ROWS ONLY;</code>

(Note: LIMIT 10 is not directly supported in Oracle, FETCH FIRST 10 ROWS ONLY should be used instead)

Through the above method, you can efficiently filter out the top 10 records that meet the conditions from the Oracle database. Choosing the version using EXISTS can significantly improve query efficiency, especially when processing large data sets.

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