Re: Select from dual return 3 rows !

I just did a little experiment on 10gR1.

as SYS

insert into sys.dual values('A');
insert into sys.dual values('B');
commit;

18:04:00 SQL>select * from dual;

D
-
X

1 row selected.


Login as myself:

18:04:50 SQL>create table my_dual as select * from dual;

Table created.

18:04:59 SQL>select * from dual;

D
-
X

1 row selected.

18:05:05 SQL>select * from my_dual;

D
-
X
A
B

3 rows selected.


So Oracle (the optimizer?) is filtering out the extra
rows when doing a 'select * from dual';.

Use it in CTAS though, and all the row appear in the new table.

Interesting, no?

I found this out first by doing block dumps (yuck!), then discovered
this other method of determining what is happening.

Now it is time to go delete those rows.

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


On 11/8/05, Jared Still <jkstill@xxxxxxxxx> wrote:
>
> That is version dependent.
>
> At least as far back as 8.1.7.4 <http://8.1.7.4>, there can
> be only one row in sys.dual.
>
> Try it.
>
>
>

Other related posts: