Skip to Main Content
  • Questions
  • Are results from SELECT with no order by and with ROWNUM predicate used in WHERE condition stable?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: October 20, 2017 - 4:46 pm UTC

Last updated: October 21, 2017 - 1:49 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Consider the following simple scenario:

PREPARATIONS:
1) Let's say we have a table USERS with one COLUMN NAME: create table USERS(NAME varchar(100));
2) Let's put some values there:

insert into USERS(name) values('User1');
insert into USERS(name) values('User2');
insert into USERS(name) values('User3');
insert into USERS(name) values('User4');
insert into USERS(name) values('User5');
insert into USERS(name) values('User6');
insert into USERS(name) values('User7');
insert into USERS(name) values('User8');
insert into USERS(name) values('User9');
insert into USERS(name) values('User10');
insert into USERS(name) values('User11');
insert into USERS(name) values('User12');
...
up to 10 000 000 values...

PROBLEM:
I am using old but kinda "standard" way of paging WITHOUT OVER, here example of paging of (0, 5> users:
select * from (select NAME, ROWNUM r from users where rownum <= 5) where r > 0;

Some users are returned, let's say:

NAME R
User1 1
User2 2
User3 3
User4 4
User5 5

But the "problem?" happens when I change the boundaries, let's say to (0, 8> users:
select * from (select NAME, ROWNUM r from users where rownum <= 8) where r > 0;
NAME R
User3 1
User4 2
User5 3
User6 4
User7 5
User8 6
User9 7
User10 8

As you can see, DIFFERENT users are returned, for example User1 is not returned, even it was returned in the first query.
The problem IS NOT replicable with such simple data set, but I replicated with set of 13 millions of rows.
Results returned for (0, 2 000 000> are not contained in set returned for (0, 10 000 000)


QUESTION:
As you can see the query HAS NO ORDERING so based on the definition the result can be in ANY ordering, rownum just cuts result when predicates applies - so from my understanding it is not bug, but need confirmation from you, experts. Is it valid to assume that Oracle optimiser just decides to use different way of execution for boundaries (0, 2 000 000> compare to a case when boundaries are (0, 10 000 000>? If so, is the only way how to fix it use of ROW_NUMBER OVER some ordering or alternatively order by BEFORE any rownum?

Thanks a lot for your answer.

NOTES:
I am using Oracle 12.1.0.2. Table has no primary keys. The problem is ALWAYS replicable in one environment and never replicable in another environments with the same data, Oracle version and patches...



and Connor said...

Yup....without some sort of ordering clause (row_number, or order by, etc) you can safely assume that rownum gives you a "pseudo-random" set of rows.

And just the rownum to nominate the number of rows request can influence the optimizer, eg

SQL> create table t as
  2  select mod(rownum,2) col, d.* from dba_objects d;

Table created.

SQL>
SQL> create index ix on t ( col );

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where col = 0 and rownum <= 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 658510075

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |   135 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                       |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |   135 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX   |       |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1)
   3 - access("COL"=0)

SQL> select * from t where col = 0 and rownum <= 100000;

Execution Plan
----------------------------------------------------------
Plan hash value: 508354683

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 39201 |  5168K|   432   (1)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 39201 |  5168K|   432   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=100000)
   2 - filter("COL"=0)



Even without indexes etc, on a simple plain heap table, the rows that come back from a query with *just* rownum is basically at the whim of how the reads are performed and dispatched down the OS, and what was going on at the time when the rows were inserted. And the very next delete/insert could shift rows around. Hence "random" because rownum isn't really a column - it is a counter: "I got row back that matched my criteria, cool, add 1 to rownum."

That's why

select * from table where rownum = 2

never gets a result. It becomes:

- We get a row,
- it has "potentially" a rownum of 1.
- Does it equal 2 ?
- No, get the next row
- it has "potentially" a rownum of 1.
- Does it equal 2 ?
- it has "potentially" a rownum of 1.
- Does it equal 2 ?
etc etc

Rating

  (1 rating)

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

Comments

5 minutes

David Carbol, October 21, 2017 - 5:39 am UTC

Thanks a lot for the confirmation of the hypothesis. And of course, thanks for really fast and accurate answer!

Nice day.
David Carbol

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