Skip to Main Content
  • Questions
  • What is the difference rownum < 2 and rownum = 1

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Furkan.

Asked: January 09, 2023 - 8:48 am UTC

Last updated: January 09, 2023 - 10:06 am UTC

Version: 13.0.6.1911

Viewed 1000+ times

You Asked

Hi Tom,

What is the difference rownum < 2 and rownum = 1 in PL/SQL ?

Can you explain with an example.

Thank you so much in advance.

and Connor said...

Absolutely no difference.

If you look at a simple explain plan on "select * from emp where" (the rownum clause), you will get:


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    37 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<2)


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    37 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)


so in both cases, we FILTER by counting until the condition is false.

The difference is purely personal preference of the developer.

(Obviously rownum=2 and rownum <3 are vastly DIFFERENT things - see the video).




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library