Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Saxena.

Asked: February 06, 2017 - 7:07 am UTC

Last updated: February 11, 2017 - 2:22 am UTC

Version: 12c and 9i

Viewed 1000+ times

You Asked

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

and Connor said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.