Re: Scary 10gR2 query problem

Just curious.
What happens if you change the "create table very_odd as select * from odd;" to "create table very_odd as select * from odd where 1=2; insert into very_odd select * from odd" ?
How many rows are reported as "... rows inserted" ? Hemant

At 09:03 PM Wednesday, Ian Cary \(C\) wrote:

>As there seem to a few people using 10.2 (10.2.0.2 on Solaris 9 in my
>case) I thought I'd share a scary issue with you all.
>
>Odd in the example below is a reasonably complex view that has been
>working fine in 10.1 for quite a while.
>
>MP409SH2> select count(*) from odd;
>
> COUNT(*)
>----------------
> 67311
>
>Elapsed: 00:01:15.56
>MP409SH2> create table very_odd as select * from odd;
>
>Table created.
>
>Elapsed: 00:07:34.00
>MP409SH2> select count(*) from very_odd;
>
> COUNT(*)
>----------------
> 1371016
>
>Elapsed: 00:00:01.84
>
>This is a 100% genuine case where the select statement returns the wrong
>(substantially) number of rows. In 10.1 the view returns the correct
>67311 rows.
>There doesn't seem to be any pattern or reason for the error and I only
>noticed it because I had the 10.1 results to hand.
>I'll be raising a tar as soon as Metalink sorts itself out but I thought
>I'd share this as I'm not sure I can 100% trust the results of any
>select statement on 10.2
>
>Cheers,
>
>Ian
>.
>
>
>This email is only intended for the person to whom it is addressed and may
>contain confidential information. If you have received this email in
>error, please notify the sender and delete this email which must not be
>copied, distributed or disclosed to any other person.
>
>Unless stated otherwise, the contents of this email are personal to the
>writer and do not represent the official view of Ordnance Survey. Nor can
>any contract be formed on Ordnance Survey's behalf via email. We reserve
>the right to monitor emails and attachments without prior notice.
>
>Thank you for your cooperation.
>
>Ordnance Survey
>Romsey Road
>Southampton SO16 4GU
>Tel: 023 8079 2000
>http://www.ordnancesurvey.co.uk
>
>--
>http://www.freelists.org/webpage/oracle-l

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

--
http://www.freelists.org/webpage/oracle-l