ROWNUM vs ROW_NUMBER in Oracle

Both ROWNUM and ROW_NUMBER() are used to assign a unique number to rows in a result set. However, they differ in their functionality and behavior, particularly when it comes to sorting and performance.

ROWNUM

Definition: ROWNUM is a pseudo column in Oracle that returns a unique number for each row in the result set. The numbering starts at 1 for the first row and increases by 1 for each subsequent row.

Usage: It is mainly used when you need to limit the number of rows returned by a query or assign a row number without any specific ordering.

Behavior:

ROWNUM is assigned before the ORDER BY clause is applied. This means that if you use ROWNUM without ordering your results, the row numbers will be assigned in the order in which Oracle retrieves the rows (which is typically the order in which rows are stored).

If used with ORDER BY, the row numbering will still happen first, and the actual sorting will occur after that, which can lead to unexpected results.

Example:

SELECT ROWNUM, ename
FROM emp;

This will return a result with the row number assigned to each row from the emp table, but the order in which rows appear is not guaranteed unless you explicitly use ORDER BY.

SELECT ROWNUM, ename
FROM emp
ORDER BY ename;

Even though the result is ordered by ename, the row numbers are assigned based on the initial order of retrieval, not the final sorted order.

Limiting Rows: ROWNUM is often used to limit the number of rows in a result set.

SELECT * FROM emp WHERE ROWNUM <= 10;

ROW_NUMBER()

Definition: ROW_NUMBER() is an analytic function in Oracle SQL that assigns a unique number to each row within a partition of the result set. It is defined as part of the OVER() clause.

Usage: It is used when you want to assign a row number based on a specific order (e.g., sorting rows) and allows partitioning data into groups.

Behavior:

ROW_NUMBER() is always assigned after sorting the rows using the ORDER BY clause within the OVER() clause.

It can be used with PARTITION BY to reset the numbering for each group.

It is more flexible than ROWNUM because it allows for sorting, partitioning, and even skipping rows in specific cases.

Example:

SELECT ename, ROW_NUMBER() OVER (ORDER BY ename) AS row_num
FROM emp;

This query assigns a sequential number to each row ordered by the employee name (ename).

If you want to reset the row number for each department, you can use PARTITION BY:

SELECT ename, department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY ename) AS row_num
FROM emp;

In this case, the row numbers are reset for each department, and rows within each department are numbered based on the alphabetical order of employee names.

Limiting Rows: You can also use ROW_NUMBER() to limit the number of rows returned. For example, to get the first employee in each department:

SELECT ename, department_id
FROM (
SELECT ename, department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY ename) AS row_num
FROM emp
)
WHERE row_num = 1;

Key Differences

Conclusion

Use ROWNUM when you need a quick, basic row numbering mechanism or when limiting rows in a query without concern for the order.

Use ROW_NUMBER() when you need precise control over the row numbering, including sorting or partitioning the data based on specific columns. It is ideal for complex queries that require ordered or grouped row numbers.