Unsorted Query

[cdias]

Suppose the fallowing scenario:
D_TIME - Time dimension table
F_FACT - Fact table
D_ENTITY - Entity dimension table

The query is something like this:
SELECT sum(fact1), d_time.month, d_time.day, d_time.year
FROM f_fact, d_time, d_entity
WHERE (((((d_time.year='2006')) and ((d_time.month='3')))) and ((((d_entity='XPTO'))))) and
f_fact.time_key=d_time.time_key and f_fact.entity_key=d_entity.entity_key
GROUP BY year, month, day

I want to get the data order by date. I know that for this I must add a ORDER BY statement. The point is that
this query works fine in Oracle 9i and in Oracle 109 release 10.1.0.3.0, but in 10.2.0.2.0 the data is displayed unsorted.
Changing the software that creates the query is, for now, a hard task. Is there a workaround?

[slimdave]

Add an ORDER BY clause. 10R2 has a hash-based grouping algorithm that will not get anywhere close to a sorted output. This change is very wel documentedl

[cdias]

Can I change the compatible parameter to 10.1.0.2.0?

[slimdave]

You could try that, but I'm not sure whether this new algorithm is controlled by that parameter. Also if you lose the hash-based group by method then you're losing a lot of performance and still have no sort order guarantee.

[davey23uk]

you have a bug, fix the bug

[DaPi]

"you have a bug, fix the bug" - exactly.

There are MANY factors that can cause a Group By NOT to deliver an ordered result set - parallel query for one. I first had this problem going from v7 to v8. Fiddling with compatibility is not going to give you a reliable solution to your problem.