Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: UNION ALL Query: Riddle
-----Original Message-----
From: Jared.Still@radisys.com [mailto:Jared.Still@radisys.com]
Sent: Tuesday, January 27, 2004 2:29 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: UNION ALL Query: Riddle
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@nordstrom.com>
Sent by: ml-errors@fatcity.com01/27/2004 01:09 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L@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@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@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 -- 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).Received on Wed Jan 28 2004 - 13:24:28 CST