Re: Scary 10gR2 query problem

  • From: "Glenn Santa Cruz" <glenn.santacruz@xxxxxxxxx>
  • To: Ian.Cary@xxxxxxxxxxxxxxxxxxxx
  • Date: Thu, 25 May 2006 10:16:26 -0500

Does your reasonably complex view happen to make use of "connect by"
and the analytic function "first_value()" ?  We had a very similar
problem, in that a query written (improperly) in 9.2.0.4 would return
a given number of rows, using a connect-by query where part of the
join criteria made use of "first_value( col ) over ()".  We did not
have an explicit "order by" on the partition clause of the analytic
function, and proper results were (thankfully) returned in 9.2.0.4.

Results were quite different in 10.2.0.2, since the ordering of the
partition changed (similar to the ordering being shifted in a "group
by" query).  We do understand the reason behind all of this, but it
was quite disconcerting.

HTH

Glenn

On 5/24/06, Ian Cary (C) <Ian.Cary@xxxxxxxxxxxxxxxxxxxx> 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

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



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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2006