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_at_gmail.com> 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.

>

>

>


--
http://www.freelists.org/webpage/oracle-l