| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: UNION ALL Query: Riddle
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
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, RajeshReceived on Thu Jan 29 2004 - 06:59:26 CST
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 -- Author: Wendry INET: wendry_yang_at_indo.net.id 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).
 |
 |