What is the
Difference between ROWNUM And ROW_NUMBER()?
·
ROWNUM is a "Pseudocolumn" that assigns a number to each row returned by
a query.
·
ROW_NUMBER is an analytic function that
assigns a number to each row according to its ordering within a group of rows.
·
If
you place ORDER BY clause in
the query, the ROWNUM column’s value gets jumbled. The order is lost and no
more we see the values being fetched in sequence. The alternate
option is to use ROW_NUMBER analytical function.
· ROWNUM is useful when you have to limit a
number of row fetch, without worrying about the exact data being fetched.
·
ROW_NUMBER is more useful when you have to generate a
sequence of numbers after sorting the data fetch on specific column or limiting
data fetched after sorting.
·
ROWNUM is generated before sorting so it can be used in WHERE
clause whereas ROW_NUMBER cannot be used in WHERE clause, it can be used to
filter only after sorting, by using an outer query.
SQLSQL> SELECT ID, NAME, DEPTNO, ROWNUM From EMP_TEST;
ID NAME DEPTNO ROWNUM
---------- ---------- ---------- ----------
102 Ankit 20 1
104 Nikhil 10 2
105 Rajan 20 3
107 Karan 20 4
110 Sajal 10 5
103 Ravi 30 6
106 Surya 30 7
108 Sam 20 8
109 Jack 10 9
9 rows selected.
SQLSQL> SELECT ID, NAME, DEPTNO, ROWNUM From EMP_TEST ORDER BY DEPTNO;
ID NAME DEPTNO ROWNUM
---------- ---------- ---------- ----------
110 Sajal 10 5
109 Jack 10 9
104 Nikhil 10 2
107 Karan 20 4
108 Sam 20 8
105 Rajan 20 3
102 Ankit 20 1
103 Ravi 30 6
106 Surya 30 7
9 rows selected.
ROW_NUMBER is an analytic function that assigns a number to each row according to its ordering within a group of rows:
IF you want to Group Employee Details by Department then ROW_NUMBER will be used as shown in the Query below-
IF you want to Group Employee Details by Department then ROW_NUMBER will be used as shown in the Query below-
SQLSQL> SELECT ID, NAME, DEPTNO,
2 ROW_NUMBER() Over (Partition By DEPTNO Order By DEPTNO) ROW_NUMBER From EMP_TEST;
ID NAME DEPTNO ROW_NUMBER
---------- ---------- ---------- ----------
110 Sajal 10 1
109 Jack 10 2
104 Nikhil 10 3
107 Karan 20 1
108 Sam 20 2
105 Rajan 20 3
102 Ankit 20 4
103 Ravi 30 1
106 Surya 30 2
9 rows selected.
ROWNUM is useful when you have to limit a number of row fetch, without worrying about the exact data being fetched. For ex. If a specific column can have duplicate values and if you want to just check if at least one row is available with that value, then we can use ROWNUM < 2 or any number to limit the row fetch.
ROW_NUMBER is more useful when you have to generate a sequence of numbers after sorting the data fetch on specific column or limiting data fetched after sorting for ex. Top 10 salary of a Dept , type of requirements.
Since ROWNUM is generated before sorting so it can be used in WHERE clause whereas ROW_NUMBER cannot be used in WHERE clause, it can be used to filter only after sorting, by using an outer query.
ROW_NUMBER is more useful when you have to generate a sequence of numbers after sorting the data fetch on specific column or limiting data fetched after sorting for ex. Top 10 salary of a Dept , type of requirements.
Since ROWNUM is generated before sorting so it can be used in WHERE clause whereas ROW_NUMBER cannot be used in WHERE clause, it can be used to filter only after sorting, by using an outer query.
Explain
Plans:
ROWNUM:
SQLSQL> EXPLAIN PLAN FOR
2 SELECT ID, ROWNUM From EMP_TEST Order By 1;
Explained.
SQL> SELECT * From TABLE(DBMS_XPLAN.Display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2943253795
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 36 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 9 | 36 | 4 (25)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| EMP_TEST | 9 | 36 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
10 rows selected.
ROW_NUMBER()
SQLSQL> EXPLAIN PLAN FOR
2 SELECT ID, ROWNUM ROW_NUM,
3 ROW_NUMBER() Over (Order By ID ASC) ROW_NUMBER
4 From EMP_TEST;
Explained.
SQL> SELECT * From TABLE(DBMS_XPLAN.Display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 856322488
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 36 | 4 (25)| 00:00:01 |
| 1 | WINDOW SORT | | 9 | 36 | 4 (25)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| EMP_TEST | 9 | 36 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
10 rows selected.
From the above Plans, we can see that cost and bytes are same for both the type of queries, but in case of ROW_NUMBER, data is fetched based on WINDOW sort than ordinary SORT.
Note: If you use any analytic functions then it will result the Window functions plan to be included in the plan.
Get involved and leave your Comments in the Box Below. The more people get involved, the more we all
benefit.So, leave your thoughts before you leave the page.
Note: If you use any analytic functions then it will result the Window functions plan to be included in the plan.
Get involved and leave your Comments in the Box Below. The more people get involved, the more we all
benefit.So, leave your thoughts before you leave the page.
This is awesome
ReplyDeletegood explaination
ReplyDelete