Re: Select from dual return 3 rows !

From: Eric Jenkinson <erichjenkinson_at_gmail.com>
Date: Wed, 9 Nov 2005 11:36:06 -0600
Message-ID: <5a2c54650511090936h6949b491wf1adcbe537c84f06@mail.gmail.com>

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: exact fetch returns more than requested number of rows appears.

test_at_SCRATCH> 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_at_SCRATCH> create table my_dual as select * from dual;

Table created.

Elapsed: 00:00:00.09
test_at_SCRATCH> select * from dual;

D
-
X

Elapsed: 00:00:00.00
test_at_SCRATCH> select * from my_dual;

D
-
X
Y
Y

Elapsed: 00:00:00.00
test_at_SCRATCH> drop table my_dual purge;
drop table my_dual purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows

 If you delete the rows as the sys user the table drop succeeds

sys_at_SCRATCH> delete from dual where dummy = 'Y';

1 row deleted.

Elapsed: 00:00:00.00
sys_at_SCRATCH> delete from dual where dummy = 'Y';

1 row deleted.

Elapsed: 00:00:00.00
sys_at_SCRATCH> commit;

Commit complete.

Elapsed: 00:00:00.01
sys_at_SCRATCH>

Elapsed: 00:00:00.00
test_at_SCRATCH> drop table my_dual purge;

Table dropped.
Elapsed: 00:00:00.01
test_at_SCRATCH>

 On 11/8/05, Jared Still <jkstill_at_gmail.com> 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_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

Received on Wed Nov 09 2005 - 11:42:03 CST