Home » SQL & PL/SQL » SQL & PL/SQL » problem in oracle 10g release 2
problem in oracle 10g release 2 [message #195213] Wed, 27 September 2006 07:35 Go to next message
manishakya
Messages: 1
Registered: September 2006
Junior Member
I faced strange problem while selecting data from join of tables (in oracle 10g). While selecting, when the order of fields in "group by" clause is changed, the resulting summary of values is changed too. For eg,

query1 = SELECT F1, F2, F3, SUM(F4)
FROM TBL1
GROUP BY F1, F2, F3, F4

query2 = SELECT F1, F2, F3, SUM(F4)
FROM TBL1
GROUP BY F2, F4, F1, F3

The results SUM(F4) given by query1 and query 2 do not match. While testing these querries in differnt environments, we found such problem in ORACLE 10g RELEASE 2 only and not in ORACLE 10g RELEASE 1. I need to execute this query in oracle 10g. Can anybody help me finding the exact problem?
Re: problem in oracle 10g release 2 [message #195222 is a reply to message #195213] Wed, 27 September 2006 08:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can't reproduce this. Can you post some data and table definitions that produce this.
Re: problem in oracle 10g release 2 [message #195307 is a reply to message #195222] Wed, 27 September 2006 19:57 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle 10.2 released a new HASH method for resolving GROUP BY that was not available in previous versions.

Get an EXPLAIN PLAN and confirm that it is using HASH GROUP BY.

It is possible you have found a bug in that new code. If so, it is also possible that someone else has discovered it and reported it to Oracle. Get your DBA to search metalinks and see if there is a patch for your OS. If not, your DBA will need to raise a new SR to have it fixed.

If I'm right, you may be able to force the old-fashioned SORT style GROUP BY by getting the DBA to change the initialization parameter:
OPTIMIZER_FEATURES_ENABLE = 10.1.0.4
HASH GROUP BY is not one of the features listed, but it may work. Alternatively, you could try setting the COMPATIBLE initialization parameter to the same value.

Ross Leishman
Previous Topic: Export SELECT output to text file
Next Topic: please help ASAP
Goto Forum:
  


Current Time: Thu Dec 08 20:13:53 CST 2016

Total time taken to generate the page: 0.12955 seconds