Comparative Analysis of Three Methods for Querying Top Three Highest Salaries in Oracle emp Table

Introduction

Querying highest salary records is a common business requirement in enterprise database applications. Oracle Database provides multiple implementation approaches, each with distinct characteristics in terms of performance, compatibility, and accuracy. Based on practical cases from the emp table, this paper systematically analyzes the implementation principles and applicable scenarios of three mainstream query methods.

Subquery Method with ROWNUM

This represents the most classical and performance-optimized approach in Oracle, particularly suitable for Oracle 10g and earlier versions. The core concept involves sorting data through a subquery first, then using the ROWNUM pseudocolumn to limit records.

SELECT * FROM 
    (
    SELECT * FROM emp 
    ORDER BY Salary DESC
    )
WHERE ROWNUM <= 3
ORDER BY Salary;

The method operates in three steps: first sorting all records by salary in descending order within the subquery, then using ROWNUM to restrict to the first three records, and finally ordering the result set by salary in ascending order for more user-friendly display. This approach offers advantages in execution efficiency and resource consumption, but requires attention to potential inaccuracies when duplicate salaries exist.

RANK() Window Function Method

For scenarios requiring precise handling of duplicate salary values, the RANK() window function provides a more comprehensive solution. This method properly handles cases with identical salaries, ensuring all qualified records are returned.

SELECT * FROM 
    (
    SELECT EMPLOYEE, LAST_NAME, SALARY,
    RANK() OVER (ORDER BY SALARY DESC) EMPRANK
    FROM emp
    )
WHERE EMPRANK <= 3;

The RANK() function assigns a ranking to each record. When multiple records share the same salary, they receive identical rankings, with subsequent rankings skipping accordingly. For example, if two records have the highest salary, both receive rank 1, and the next distinct salary receives rank 3. This characteristic gives the method significant advantages when processing duplicate data.

Traditional Correlated Subquery Method

For scenarios requiring compatibility with the oldest SQL versions, the traditional correlated subquery approach can be used. This method doesn't rely on any modern SQL features and offers the best backward compatibility.

SELECT a.ENAME, b.SAL
FROM emp a, emp b
WHERE a.EMPNO = b.EMPNO
      AND
      3 > (SELECT COUNT(*) FROM emp b
               WHERE a.SAL = b.SAL);

The core logic of this method involves calculating each salary's ranking through correlated subqueries: for each record, count how many other records have higher salaries, and if this count is less than 3, the record belongs to the top three highest salaries. Although this approach has more complex syntax, it maintains practical value in scenarios requiring maximum compatibility.

Performance Comparison Analysis

From an execution efficiency perspective, the ROWNUM-based method typically offers the best performance, requiring only one full table scan and sorting operation. The RANK() window function method, while more feature-rich, incurs additional window function computation overhead. The traditional correlated subquery method shows relatively poorer performance due to multiple table scans and join operations.

Regarding memory consumption, the ROWNUM method only needs to maintain the first few sorted records, while the RANK() method requires ranking calculations for all records, resulting in higher memory requirements. The traditional method, involving self-joins, may generate significant temporary space requirements on large tables.

Compatibility Considerations

Different Oracle versions exhibit varying support for SQL features. The ROWNUM method has been fully supported since Oracle 7, offering the best version compatibility. The RANK() window function requires Oracle 8i or later, while the traditional correlated subquery method operates normally across all Oracle versions.

For Oracle 10g environments, the ROWNUM method is recommended as the primary choice, unless precise handling of duplicate salaries is required. In scenarios needing duplicate value processing, the RANK() method provides better accuracy assurance.

Practical Application Recommendations

When selecting specific implementation methods, comprehensive consideration of business requirements, data characteristics, and system environment is necessary. For most OLTP scenarios, the ROWNUM-based method delivers optimal performance. In reporting and analysis systems requiring precise ranking information, the RANK() method proves more appropriate.

Notably, when dealing with large table data volumes, establishing appropriate indexes on the Salary field is recommended to enhance query performance. Meanwhile, in actual production environments, thorough testing and performance tuning of query statements should be conducted.

Conclusion

This paper provides detailed analysis of three primary methods for querying the top three highest salaries in Oracle Database. The ROWNUM-based subquery demonstrates optimal performance and compatibility, the RANK() window function shows advantages in handling duplicate data, and the traditional correlated subquery method offers the best backward compatibility. Developers should select the most suitable implementation based on specific requirements, ensuring functional correctness while optimizing system performance.