Monday, March 13, 2006

How many rows fit into DUAL?

Let's consider the contents of the famous dual table:

SQL>select * from dual;

D
-
X

SQL>select rownum from dual;

ROWNUM
----------
1

Now let's try to get multiple rows returned form dual:


SQL>select rownum from dual connect by rownum < = 5;

ROWNUM
----------
1

So there's still one row, whichs results in a rownum of 1. Now let's ask the max of rownum:

SQL>select max(rownum) from dual connect by rownum < = 5;

MAX(ROWNUM)
-----------
5

Kinda strange isn't it? Now let's try to get these 5 rows:

SQL>select * from (select rownum from dual connect by rownum < = 5);

ROWNUM
----------
1
2
3
4
5

Another strange feature is that in TOAD

select rownum from dual connect by rownum < = 5

returns 5 rows (and in SQL*Plus only one as you can see above).

Does this kind of behaviour also occurs on other tables/views?
In my application I've got one materialized view, so a select from all_mviews returns one row (just like dual):

SQL>select count(*) from all_mviews;

COUNT(*)
----------
1

SQL>select rownum from all_mviews;

ROWNUM
----------
1

SQL>select rownum from all_mviews connect by rownum< =5;

ROWNUM
----------
1
2
3
4
5

SQL>select rownum from dual connect by rownum< =5;

ROWNUM
----------
1

So the one row from dual behaves different than the one row from all_mviews....

So what you see is what you get, but what you get is not what you have!
Post a Comment