sorting error

[mahajanakhil198]

we have recently migrated data from 9i to 10g..
platform MS server 2003 on both sides..
oracle 6i forms and reports both sides..

Earlier, data in forms and reports was displayed in sorted order..But After shifting to new server, data is not displayed in sorted order.
How could it be corrected?

[ales]

Do you mean the output is not sorted at all? Then use the ORDER BY clause in queries.
If output is kind of sorted but the order is wrong, check the NLS_ parameters and if possible compare them with those on the old box.

[PAVB]

Check the code.

Until Ora9i when you had a GROUP BY clause ORDER BY was implicit meaning Oracle would sort the output by the GROUP BY condition even if no ORDER BY was explicitily specified.

Starting on Ora10g if you want to ensure the output sorted you have to include both GROUP BY and ORDER BY clauses.

[mahajanakhil198]

thanks it helped

[mahajanakhil198]

this requires changing the coding of all forma and reports of my application dat i cant afford..Is there any way or parameter setting that allows implicit order by alongwith group by??
I need to do it urgently..

[ales]

Until Ora9i when you had a GROUP BY clause ORDER BY was implicit meaning Oracle would sort the output by the GROUP BY condition even if no ORDER BY was explicitily specified.

This never has been true. Not a big deal to show that:

http://groups.google.com/group/comp....e=source&hl=en

[mahajanakhil198]

well momentarily, i got the solution by setting optimizer_features_enable='9.2.0'....
but i m willing to use the optimizer features of 10g later on...
is there any other way??

[ales]

this requires changing the coding of all forma and reports of my application dat i cant afford..Is there any way or parameter setting that allows implicit order by alongwith group by??

No, there isn't. Implicit ORDER BY with GROUP BY is a myth.

Quote from 9i docs:
http://download.oracle.com/docs/cd/B...3a.htm#2066158

The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.

Obviously, omitting the ORDER BY was a developer mistake. If you want sorted output you have to sort it explicitly. It apply to queries with GROUP BY or without it.

Changing the optimizer_features_enable is the only thing that might help. I hope it gives you enough time to adjust the queries.
Perhaps others here have some idea ...
Good luck.

[mahajanakhil198]

myth????
here is the code..
SQL> select * from test;

A B
---------- ----------
3 200
2 300
8 100
5 400
1 300
4 500
8 200
5 300
4 600
2 300
3 500
2 100
7 300

13 rows selected.

SQL> select a,sum(b) from test
2 group by a;

A SUM(B)
---------- ----------
1 300
2 700
3 700
4 1100
5 700
7 300
8 300

7 rows selected.

[ales]

Well, sometimes it works. But it doesn't work always. Launch the code bellow and post here if the output was sorted according the group by clause in ascending order. Mine wasn't in 9i nor in 10g.

create table the_table (
   pk1 int,
   pk2 int,
   nk1 int not null,
   nk2 int not null,
   primary key(pk1,pk2)
)
/
create index the_table_idx on the_table(pk1,nk1,pk2,nk2)
/
insert into the_table values (1, 1, 2, 2 );

insert into the_table values (2, 1, 1, 1 );

analyze table the_table compute statistics;

set autotrace on explain

select nk1, nk2, pk1, pk2, count(*) a_count
from the_table
group by nk1, nk2, pk1, pk2
;