RE: UNION ALL Query: Riddle

It would be my guess that someone was doing DML on your table while you're running the first query, and you don't see the results of that until the second query.

Try running your SQL statement twice in a single transaction and see if the results are the same then.

eg.

rollback;
set transaction read only;

<run SQL once here>
<run it a second time here>

The results should be the same.

Or, you could get the old ORA-1555, if a number of changes have been made and your rollback segments can't keep up.

Jared

On Wed, 2004-01-28 at 11:24, Pillai, Rajesh wrote:
> 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-----
> From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_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_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
-- 
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).