Re: 8.1.5 - Dual gone schizoid

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 Received on Thu Dec 30 1999 - 15:45:24 CST