| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: 8.1.5 - Dual gone schizoid
Jonathan Lewis wrote:
>
> The saga continues -
> Can anyone reproduce this:
> Still on 8.1.5.0 - compatibility = 8.1.5
> NT 4.0 under SP3
>
> The listing is a spool file from a single session.
> I suggest you don't do this on a serious database
>
> 1) First experiment - truncate the table sys.dual then follow the listing
> 2) Second experiment - drop and recreate the table sys.dual then
> following the listing
>
> SQL> describe sys.dual;
> Name Null? Type
> ----------------------------------------------------- -------- -------------
> -----------------------
> DUMMY VARCHAR2(1)
>
> SQL> insert into sys.dual values ('w');
>
> 1 row created. -- seems reasonable
>
> SQL> select dummy from sys.dual;
>
> D
> -
> w -- all happy so far
>
> SQL> insert into sys.dual values('x');
>
> 1 row created. -- good
>
> SQL> select dummy from sys.dual;
>
> D
> -
> w -- where's the "x" gone to ?
>
> SQL> delete from sys.dual;
>
> 1 row deleted. -- but there should be two rows
>
> SQL> select dummy from sys.dual;
>
> D
> -
> x -- but I just deleted every row unconditionally
>
> SQL> commit;
>
> Commit complete.
>
> SQL> spool off
>
> So that's what the manuals mean when
> they say 'the optimiser knows that DUAL
> holds only a single row' !
>
> NB The block dumps show that the rows exist
> in the first block of the table. The row returned is
> the first non-deleted row in the row directory.
>
> For those interested in the low-down approach, a
> trace file on the SQL 'select * from dual' (when there
> are several rows in the table) shows consecutive lines:
>
> PARSE #1 ........etc.
> EXECUTE #'1 ... etc.
> FETCH #1 ...... r = 1
> FETCH #1 ..... r = 0
>
> in other words Oracle tries a second fetch and finds
> no second row.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
I heard runours a while ago that DUAL was going to be a (for lack of a better term) "virtual" table as of 8.1.5 - ie some special internals rather than a 'genuine' table for performance reasons...Could this be a possible explanation ?
Connor
--
"Some days you're the pigeon, and some days you're the statue." Received on Mon Jan 03 2000 - 04:23:27 CST
 |
 |