Re: Select from dual return 3 rows !
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Rudy.Zung@xxxxxxx
- Date: Wed, 9 Nov 2005 02:04:28 +0000
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:
