RE: UNION ALL Query: Riddle

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" <Rajesh.Pillai_at_nordstrom.com>
> Sent by: ml-errors_at_fatcity.com
> 01/27/2004 01:09 PM
> Please respond to ORACLE-L
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> 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: Rajesh.Pillai_at_nordstrom.com
>
> 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: ListGuru_at_fatcity.com (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: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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).