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@(protected)> 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. > > >
I just did a little experiment on 10gR1.<br> <br> as SYS<br> <br> insert into sys.dual values('A');<br> insert into sys.dual values('B');<br> commit;<br> <br> 18:04:00 SQL>select * from dual;<br> <br> D<br> -<br> X<br> <br> 1 row selected.<br> <br> <br> Login as myself:<br> <br> 18:04:50 SQL>create table my_dual as select * from dual;<br> <br> Table created.<br> <br> 18:04:59 SQL>select * from dual;<br> <br> D<br> -<br> X<br> <br> 1 row selected.<br> <br> 18:05:05 SQL>select * from my_dual;<br> <br> D<br> -<br> X<br> A<br> B<br> <br> 3 rows selected.<br> <br> <br> So Oracle (the optimizer?) is filtering out the extra<br> rows when doing a 'select * from dual';.<br> <br> Use it in CTAS though, and all the row appear in the new table.<br> <br> Interesting, no?<br> <br> I found this out first by doing block dumps (yuck!), then discovered<br> this other method of determining what is happening.<br> <br> Now it is time to go delete those rows.<br> <br> -- <br> Jared Still<br> Certifiable Oracle DBA and Part Time Perl Evangelist<br> <br><br><div><span class="gmail_quote">On 11/8/05, <b class="gmail_sendername" >Jared Still</b> <<a href="mailto:jkstill@(protected)">jkstill@(protected)</a>> ; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb (204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> <span class="q"></span>That is version dependent.<br> <br> At least as far back as <a href="http://8.1.7.4" target="_blank" onclick= "return top.js.OpenExtLink(window,event,this)">8.1.7.4</a>, there can<br> be only one row in sys.dual.<br> <br> Try it.<br><span class="sg"><br clear="all"><br></span></blockquote></div><br> <br>
