If what you are describing is completely accurate, ( no DML, change S_A_S fixes the problem ) then it would appear you have encountered a bug.
A search on MetaLink is in order, and failing that, you need to open a TAR. Jared On Thu, 2004-01-29 at 04:59, Wendry wrote: > I have the same problem like you Rajesh, the query also gives different > rowcount each time executed eventhough there's no one updating base > tables, in my opinion it's because of the sorting operation (your group > by clause). In my case after I remove some group functions, the result > goes well. Also I reduce the use of order by clause where it's not > needed. > > I still haven't found the exact solution to this problem. But just now > I've tried to decrease the sort area size parameter value (I think I > oversize it), and run the query again, the result goes stable with the > problematic query but it runs slower. I haven't tried intensively, I try > to do that tomorrow. Meanwhile if, there's any of the Gurus can give us > clearer explanation, please do so... Thank you all in advance. > > Regards, > > Wendry. > > -----Original Message----- > Pillai, Rajesh > Sent: Thursday, January 29, 2004 2:24 AM > To: Multiple recipients of list ORACLE-L > > Hi Jared, > Thanks for your response. different results mean that number of > records are different sometimes, and sometimes the some of the > quantities are not correct. Your help is really appreciated. > > Thanks, > Rajesh > -----Original Message----- > Sent: Tuesday, January 27, 2004 2:29 PM > To: Multiple recipients of list ORACLE-L > > Q: What does "different results" mean? > > Different row count? > > Completely different data? > > Partially different data? > > Some columns have incorrect value? > > What about doing it without the parallel hints? The tables aren't > so big that it would take a long time to find out. > > Jared > > > > > "Pillai, Rajesh" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/27/2004 01:09 PM > Please respond to ORACLE-L > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: UNION ALL Query: Riddle > > > > Hi All, > The following query is giving different results in each > run. I assure that no data modified between consecutive runs - > INSERT /* append parallel (z,8) */ > INTO some_table > (SELECT /*parallel (a,8) */ > a.item, > a.loc, > SUM(a.qty_type_1), > SUM(a.qty_type_2) > FROM > (select /*parallel (x,8) */ > item, > loc, > qty_type_1, > to_number(NULL) > > from > table_a x > UNION ALL > select /*parallel (y,8) */ > item, > loc, > to_number(NULL), > qty_type_2 > > from > table_b y > ) a > GROUP BY > a.item, > a.loc); > > Additional info - > > Number of records in table_a and table_b is around 3M and 6M. > > SQL> select * from v$version; > > BANNER > ---------------------------------------------------------------- > Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production > PL/SQL Release 8.1.7.2.0 - Production > CORE 8.1.7.0.0 Production > TNS for Solaris: Version 8.1.7.2.0 - Production > NLSRTL Version 3.4.1.0.0 - Production > > I would appreciate any help in solving this mystery and all hints are > welcome. > > Thanks, > Rajesh Pillai > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Pillai, Rajesh > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
