Hi,
We are migrating data from 9i to 12c by import/export procedure, After import when we are checking product behavior the table display the data in both Oracle 9i application is different from Oracle 12c application.
For e.g.:
create table costumer (
idcostumer integer,
name varchar2(20),
note varchar2(10 CHAR),
update_date date
);
insert into costumer values (1,'Natalie','A',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (2,'CarlSagan','A',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (3,'BobBurnquist','A',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (4,'Natalie','B',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (5,'CarlSagan','B',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (6,'BobBurnquist','B',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (7,'Natalie','C',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (8,'CarlSagan','C',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (9,'BobBurnquist','C',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (10,'Natalie','A',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (11,'CarlSagan','A',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (12,'BobBurnquist','A',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (13,'Natalie','B',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (14,'CarlSagan','B',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (15,'BobBurnquist','B',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (16,'Natalie','C',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (17,'CarlSagan','C',(TO_DATE('2003/05/03','yyyy/mm/dd')));
insert into costumer values (18,'BobBurnquist','C',(TO_DATE('2003/05/03','yyyy/mm/dd')));
Execute SQL:
SELECT * FROM costumer order by name asc, update_date desc;
Oracle 12c Output:
S.No IDCOSTUMER NAME NOTE UPDATE_DATE
1 18 Bob Burnquist C 03-05-03
2 9 Bob Burnquist C 03-05-03
3 3 Bob Burnquist A 03-05-03
4 12 Bob Burnquist A 03-05-03
5 15 Bob Burnquist B 03-05-03
6 6 Bob Burnquist B 03-05-03
7 8 Carl Sagan C 03-05-03
8 5 Carl Sagan B 03-05-03
9 2 Carl Sagan A 03-05-03
10 17 Carl Sagan C 03-05-03
11 14 Carl Sagan B 03-05-03
12 11 Carl Sagan A 03-05-03
13 13 Natalie B 03-05-03
14 16 Natalie C 03-05-03
15 4 Natalie B 03-05-03
16 1 Natalie A 03-05-03
17 10 Natalie A 03-05-03
18 7 Natalie C 03-05-03
Oracle 9i Output:
S.No IDCOSTUMER NAME NOTE UPDATE_DATE
1 3 Bob Burnquist A 03-05-03
2 6 Bob Burnquist B 03-05-03
3 9 Bob Burnquist C 03-05-03
4 12 Bob Burnquist A 03-05-03
5 15 Bob Burnquist B 03-05-03
6 18 Bob Burnquist C 03-05-03
7 2 Carl Sagan A 03-05-03
8 5 Carl Sagan B 03-05-03
9 8 Carl Sagan C 03-05-03
10 14 Carl Sagan B 03-05-03
11 17 Carl Sagan C 03-05-03
12 11 Carl Sagan A 03-05-03
13 1 Natalie A 03-05-03
14 4 Natalie B 03-05-03
15 16 Natalie C 03-05-03
16 13 Natalie B 03-05-03
17 10 Natalie A 03-05-03
18 7 Natalie C 03-05-03
Please let me know why there is a difference in Sort order in different version of Oracle.
Regards,
Saxena
Sort settings can change the way data is sorted, but I dont think that is what you are hitting here.
I'm assuming from your test data that (for example) all of the "Bob" rows have the same name and same date.
In that way, the sort order of those identical rows is non-deterministic - that is, you cannot know what the order will be. You would need to add something that makes the sorting *predictable*, for example:
order by name, update_date, id
so that the 'id' becomes the "tie breaker". Things change from version to version as we do things like make algorithms faster and smarter.