Home » RDBMS Server » Server Administration » Same query produces different results
Same query produces different results [message #372294] Wed, 31 January 2001 08:50 Go to next message
Kristen
Messages: 6
Registered: January 2001
Junior Member
On my project, we are using Oracle 8.1.6. We are experiencing issues with
queries returning different results when run multiple times with the same data.
Our tables have been analyzed and our database is running with the Cost-based Optimizer.
We even tried adding a RULE hint to the query and it still produced different results.
All of the tables the queries pull from look fine, and one week we will have the problem
with one query and the next week it will be a different query. We have an identical
database running on 8.0.5 and the same queries work consistently on this database.

Has anyone ever seen anything like this?!?!?!
Re: Same query produces different results [message #372295 is a reply to message #372294] Wed, 31 January 2001 11:29 Go to previous messageGo to next message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
It would be interesting to see your query to make sure that there is nothing in the statement causing the problem. e.g. Using something like "where rownum <= 1" could/would cause problems.

Maybe "analyze table x validate structure cascade;"
Re: Same query produces different results [message #372297 is a reply to message #372295] Wed, 31 January 2001 15:46 Go to previous messageGo to next message
Kristen
Messages: 6
Registered: January 2001
Junior Member
Here's the query:

SELECT DECODE ( AL1.CURRENT_CHNL_ID, '04','04', '05', '05', '08', '08', 'Exit' ),
AL5.FMLY_PARENT_NAME,
AL5.FMLY_PARENT_DESC,
AL4.RPT_YR_QTR_WK,
AL4.RPT_WK_IN_QTR,
AL3.MIN_ORD_WK,
AL2.WEEK,
SUM ( AL2.PURCHASER_COUNT ),
SUM ( AL2.LEASE_PURCHASER_COUNT ),
SUM ( AL2.SYS_QTY ),
SUM ( AL2.LEASE_SYS_QTY ),
SUM ( AL2.COMB_REV_DISC_AMT ),
SUM ( AL2.LEASE_COMB_REV_DISC_AMT ),
SUM ( AL2.COMB_MARGIN_AMT ),
SUM ( AL2.LEASE_COMB_MARGIN_AMT )

FROM BSDD.CUSTOMER_BILL_TO_ADDRESS AL1,
BSDD.FP_WEEKLY_SUMMARY AL2,
BSDD.V_CUST_FIRST_ORD_WK AL3,
BSDD.V_REPORT_CALENDAR_WEEK AL4,
BSDD.V_FP_PLP AL5

WHERE ( AL3.CUST_NUM (+)= AL2.CUST_NUM
AND AL2.CUST_NUM = AL1.CUST_NUM (+)
AND AL5.FMLY_PARENT_NAME=AL2.FMLY_PARENT_NAME
AND AL4.FISC_WEEK_NUM=AL2.WEEK)
AND (AL5.PROD_LINE_PARENT_NAME='4IN')

GROUP BY DECODE ( AL1.CURRENT_CHNL_ID, '04','04', '05', '05', '08', '08', 'Exit' ), AL5.FMLY_PARENT_NAME,
AL5.FMLY_PARENT_DESC,
AL4.RPT_YR_QTR_WK,
AL4.RPT_WK_IN_QTR,
AL3.MIN_ORD_WK,
AL2.WEEK

We have tried different variations, like taking a field out of the SELECT statement, and sometimes it produces consistent results. For example, we took out FMLY_PARENT_DESC (which is one-to-one with FMLY_PARENT_NAME) and got the same row count 3 times. Our DBA even exported our 8.1.6 database to 8.0.5 and then we did not experience the problem. This is why we are thinking it is a bug or something. Thanks for any help you can provide.
Re: Same query produces different results [message #372302 is a reply to message #372297] Wed, 31 January 2001 20:27 Go to previous messageGo to next message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
Interesting problem. I presume it is dynamic SQL - not compiled. The things which catch my eye are the 2 outer joins and their position in the where clause. I guess I would shift the outer joins down (but for no good reason which I could site). Also as you will be aware, the generation of fresh stats could change your execution plan which could cause the "bug" to manifest itself???

select table_name, num_rows, CHAIN_CNT, LAST_ANALYZED
from user_tables
where table_name in ('BSDD.CUSTOMER_BILL_TO_ADDRESS',
'BSDD.FP_WEEKLY_SUMMARY',
'BSDD.V_CUST_FIRST_ORD_WK',
'BSDD.V_REPORT_CALENDAR_WEEK',
'BSDD.V_FP_PLP')

I'd do an explain plan on the 2 different versions to see what happens?? Beware of trying to capture the plan in a trace file - it's only determined when you run TKPROD on the trace file! Maybe enabling autotrace in sqlplus is the easiest way.
Re: Same query produces different results [message #372306 is a reply to message #372297] Thu, 01 February 2001 07:42 Go to previous messageGo to next message
me
Messages: 66
Registered: August 2000
Member
You did not state (or I missed it) what is returning different. The number of records for the grouped by clause or the summed values. If it is the summed values and the fields being summed are nullable fields, you will need to NVL the summed fields.
SUM ( AL2.PURCHASER_COUNT ),
SUM ( NVL(AL2.LEASE_PURCHASER_COUNT,0) ),
SUM ( NVL(AL2.SYS_QTY,0) ),
SUM ( NVL(AL2.LEASE_SYS_QTY,0) ),
SUM ( NVL(AL2.COMB_REV_DISC_AMT,0) ),
SUM ( NVL(AL2.LEASE_COMB_REV_DISC_AMT,0) ),
SUM ( NVL(AL2.COMB_MARGIN_AMT,0) ),
SUM ( NVL(AL2.LEASE_COMB_MARGIN_AMT,0) )
Re: Same query produces different results [message #372307 is a reply to message #372297] Thu, 01 February 2001 09:20 Go to previous messageGo to next message
Kristen
Messages: 6
Registered: January 2001
Junior Member
The number of records for the grouped by clause are returning different. For example, I used the query:
SELECT COUNT(1) FROM
( my query);
When run 3 times in a row, I received different row counts. Plus, there are no null values in the summed fields.

Thanks for the help.
Re: Same query produces different results [message #372320 is a reply to message #372297] Fri, 02 February 2001 07:01 Go to previous messageGo to next message
John R
Messages: 156
Registered: March 2000
Senior Member
Just an obvious question:

Are the numbers of rows increasing all the time, and are you running this query on live data?

If both of those are 'Yes', I think I know what the problem is. Otherwise, I'm stumped.
Re: Same query produces different results [message #372369 is a reply to message #372297] Mon, 05 February 2001 17:18 Go to previous messageGo to next message
Kristen
Messages: 6
Registered: January 2001
Junior Member
Answers to both are 'No'. We found out that there is a bug in oracle 8.1.6 where the combination of group by and a function (like decode) can return wrong results on a parallel query. We had to apply a patch.

For anyone having the same problem - here is the info....

This is bug 1253654.
This problem was introduced in 8.1.5
Parallel query may return the wrong results if a GROUP BY clause
is used with a function rather than a column.
The wrong results may also occur if a parallel plan is run serially.

For Windows NT, looks like first fixed in patch set 81630. It is also fixed in 81613.)
Re: Same query produces different results [message #372370 is a reply to message #372297] Mon, 05 February 2001 17:19 Go to previous messageGo to next message
Kristen
Messages: 6
Registered: January 2001
Junior Member
Answers to both are 'No'. We found out that there is a bug in oracle 8.1.6 where the combination of group by and a function (like decode) can return wrong results on a parallel query. We had to apply a patch.

For anyone having the same problem - here is the info....

This is bug 1253654.
This problem was introduced in 8.1.5
Parallel query may return the wrong results if a GROUP BY clause
is used with a function rather than a column.
The wrong results may also occur if a parallel plan is run serially.

For Windows NT, looks like first fixed in patch set 81630. It is also fixed in 81613.)
Re: Same query produces different results [message #372371 is a reply to message #372297] Mon, 05 February 2001 17:21 Go to previous message
Kristen
Messages: 6
Registered: January 2001
Junior Member
Answers to both are 'No'. We found out that there is a bug in oracle 8.1.6 where the combination of group by and a function (like decode) can return wrong results on a parallel query. We had to apply a patch.

For anyone having the same problem - here is the info....

This is bug 1253654.
This problem was introduced in 8.1.5
Parallel query may return the wrong results if a GROUP BY clause
is used with a function rather than a column.
The wrong results may also occur if a parallel plan is run serially.

For Windows NT, looks like first fixed in patch set 81630. It is also fixed in 81613.)
Previous Topic: restricting rows
Next Topic: How convert varchar2 to RAW in PL/SQL
Goto Forum:
  


Current Time: Mon Dec 23 00:11:51 CST 2024