 | | | Select from dual return 3 rows ! | Select from dual return 3 rows ! 2005-11-09 - By Eric Jenkinson
Meta Link note 185438.1 Select * from DUAL - Delete from DUAL behaviour
mentions that there is internalized code that ensures that a table scan of
SYS.DUAL only returns one row.
It would appear that this internalized code only fires on select * from
dual and select * from dual where dummy = value (where value is some value
known to be in dual other than 'X', that appears more than once) and delete
from dual in SQL*Plus where they are not the input to another function. It
does not appear to fire on CTAS, insert into ... select * from dual or
opening a cursor for select * from dual. Also all the rows appear, if more
than one exist, in a select * from dual issued through JDBC.
Another interesting thing occurs when you try to drop a table that was
either a CTAS from dual or the recipient of an insert into select * from
dual. When attempting to drop the table an ORA-01422 (See ORA-01422.ora-code.com): exact fetch returns
more than requested number of rows appears.
test@(protected)> select * from v$version;
BANNER
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for 32-bit Windows: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
Elapsed: 00:00:00.10
test@(protected)> create table my_dual as select * from dual;
Table created.
Elapsed: 00:00:00.09
test@(protected)> select * from dual;
D
-
X
Elapsed: 00:00:00.00
test@(protected)> select * from my_dual;
D
-
X
Y
Y
Elapsed: 00:00:00.00
test@(protected)> drop table my_dual purge;
drop table my_dual purge
*
ERROR at line 1:
ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level 1
ORA-01422 (See ORA-01422.ora-code.com): exact fetch returns more than requested number of rows
If you delete the rows as the sys user the table drop succeeds
sys@(protected)> delete from dual where dummy = 'Y';
1 row deleted.
Elapsed: 00:00:00.00
sys@(protected)> delete from dual where dummy = 'Y';
1 row deleted.
Elapsed: 00:00:00.00
sys@(protected)> commit;
Commit complete.
Elapsed: 00:00:00.01
sys@(protected)>
Elapsed: 00:00:00.00
test@(protected)> drop table my_dual purge;
Table dropped.
Elapsed: 00:00:00.01
test@(protected)>
On 11/8/05, Jared Still <jkstill@(protected)> wrote:
>
> 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@(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.
> >
> >
> >
>
>
<div>Meta Link note 185438.1 Select * from DUAL - Delete from DUAL behaviour
mentions that there is internalized code that ensures that a table scan of SYS
.DUAL only returns one row. </div>
<div> </div>
<div>It would appear that this internalized code only fires on select * from
dual and select * from dual where dummy = value (where value is some
value known to be in dual other than 'X', that appears more than once
) and delete from dual in SQL*Plus where they are not the input to another
function. It does not appear to fire on CTAS, insert into ... select *
from dual or opening a cursor for select * from dual. Also all the rows appear,
if more than one exist, in a select * from dual issued through JDBC.
</div>
<div> </div>
<div>Another interesting thing occurs when you try to drop a table that
was either a CTAS from dual or the recipient of an insert into select *
from dual. When attempting to drop the table an ORA-01422 (See ORA-01422.ora-code.com): exact fetch
returns more than requested number of rows appears.
</div>
<div> </div>
<p><a href="mailto:test@(protected)">test@(protected)</a>> select * from v$version;<
/p>
<p>BANNER<br>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
<br>Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod<br>PL/SQL
Release 10.1.0.3.0 - Production<br>CORE 10.1.0.3.0  
; Production
<br>TNS for 32-bit Windows: Version 10.1.0.3.0 - Production<br>NLSRTL Version
10.1.0.3.0 - Production</p>
<p>Elapsed: 00:00:00.10<br><a href="mailto:test@(protected)">test@(protected)</a>>
create table my_dual as select * from dual;</p>
<p>Table created.</p>
<p>Elapsed: 00:00:00.09<br><a href="mailto:test@(protected)">test@(protected)</a>>
select * from dual;</p>
<p>D<br>-<br>X</p>
<p>Elapsed: 00:00:00.00<br><a href="mailto:test@(protected)">test@(protected)</a>>
select * from my_dual;</p>
<p>D<br>-<br>X<br>Y<br>Y</p>
<p>Elapsed: 00:00:00.00<br><a href="mailto:test@(protected)">test@(protected)</a>>
drop table my_dual purge;<br>drop table my_dual purge<br>*<br>ERROR at line 1:
<br>ORA-00604 (See ORA-00604.ora-code.com): error occurred at recursive SQL level 1<br>ORA-01422 (See ORA-01422.ora-code.com): exact fetch
returns more than requested number of rows
</p>
<p> </p>
<p>If you delete the rows as the sys user the table drop succeeds</p>
<p><a href="mailto:sys@(protected)">sys@(protected)</a>> delete from dual where
dummy = 'Y';</p>
<p>1 row deleted.</p>
<p>Elapsed: 00:00:00.00<br><a href="mailto:sys@(protected)">sys@(protected)</a>>
delete from dual where dummy = 'Y';</p>
<p>1 row deleted.</p>
<p>Elapsed: 00:00:00.00<br><a href="mailto:sys@(protected)">sys@(protected)</a>>
commit;</p>
<p>Commit complete.</p>
<p>Elapsed: 00:00:00.01<br><a href="mailto:sys@(protected)">sys@(protected)</a>></p>
<p><br>Elapsed: 00:00:00.00<br><a href="mailto:test@(protected)">test@(protected)</a>
> drop table my_dual purge;</p>
<p>Table dropped.</p>
<div>Elapsed: 00:00:00.01<br><a href="mailto:test@(protected)">test@(protected)</a>>
</div>
<div> </div>
<div><br> </div>
<div><span class="gmail_quote">On 11/8/05, <b class="gmail_sendername">Jared
Still</b> <<a onclick="return top.js.OpenExtLink(window,event,this)" href=
"mailto:jkstill@(protected)" target="_blank">jkstill@(protected)</a>> wrote:
</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">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.<span><br><br>-- <br>Jared Still
<br>Certifiable Oracle DBA and Part Time Perl Evangelist<br><br><br></span>
<div><span>
<div><span class="gmail_quote">On 11/8/05, <b class="gmail_sendername">Jared
Still</b> <<a onclick="return top.js.OpenExtLink(window,event,this)" href=
"mailto:jkstill@(protected)" target="_blank">jkstill@(protected)</a>> wrote:
</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0
.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid"><span></span>That is version
dependent.<br><br>At least as far back as <a onclick="return top.js.OpenExtLink
(window,event,this)" href="http://8.1.7.4/" target="_blank">
8.1.7.4</a>, there can<br>be only one row in sys.dual.<br><br>Try it.<br><span>
<br clear="all"><br></span></blockquote></div><br><br></span></div></blockquote>
</div><br>
|
|
|