Oracle 10 vs Oracle 12: order from SELECT statement [message #668779] |
Wed, 14 March 2018 05:59 |
|
basileus
Messages: 3 Registered: March 2018
|
Junior Member |
|
|
Hi,
I'm italian e I have a problem
"select" with Oracle 10g returns the same order than inserted row, Oracle 12 not
example:
insert into x values (1) ;
insert into x values (2) ;
insert into x values (3) ;
Oeacle 10
1
2
3
Oracle 12
2
3
1
I don't want to use ORDER BY clausole
Solution ? Setting any system parameter ?
Thanks
[Updated on: Wed, 14 March 2018 06:06] Report message to a moderator
|
|
|
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668780 is a reply to message #668779] |
Wed, 14 March 2018 06:16 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Oracle never, ever returned data in order of insertion.
What it did most of the time, when doing a full table scan, was return the data in the order it's stored on disk.
Now for a small table, which hasn't had much in the way of updates/deletes the order on disk will equal the order of insertion 99% of the time.
But the moment oracle inserts a new row into space occupied by a previously inserted row order on disk stops being the same as order of insertion.
The moment oracle does any row migration order on disk stops being the same as order of insertion.
I say all this to make it clear that the behaviour you thought you had in 10g was unreliable. The fact that 12c has come up with a new approach to storing/reading the data has just highlighted this.
You may not want to use ORDER BY but that is the only correct solution.
|
|
|
|
|
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668785 is a reply to message #668782] |
Wed, 14 March 2018 07:38 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I'm not sure I'd use the phrase dumb luck.
I get the point you're making but thing is:
You can create a table and insert 100 rows, then issue repeated select * against it. You'll get the data in order of insertion (prior to 12c anyway) every single time.
As far as most people are concerned nothing that is that repeatable could possibly be sheer dumb luck.
They see it working and assume it's always true.
This is like the when the GROUP BY algorithm changed.
Prior to 10g (or was it 11g?) group by ordered the data as though the group by was also an order by.
It did it reliably because of the the way the algorithm was implemented.
Oracle never ever said it would do that, but you could test it for yourself and see that it did.
And then oracle changed the algorithm and ordering went away. And lots of people complained because they were relying on it.
Point is, because the behaviour appears so reliable most people won't believe you when you describe it as luck.
|
|
|
|
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668815 is a reply to message #668810] |
Thu, 15 March 2018 06:14 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You can't change it.
You never could.
Oracle can change the order data is returned (assuming you don't have an order by) at any time for any reason and you can't stop it.
If order matters you need order by, and if that's going to be a lot of work for you then you better get busy.
Though I'm not sure why you care about order in data extracts - does it really matter?
|
|
|
|
|
|
|
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668836 is a reply to message #668818] |
Fri, 16 March 2018 09:49 |
John Watson
Messages: 8922 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
How is this for a solution:
Add a hidden identity column to each table.
Rename the tables to something else.
Create a view over each table named with the original table name, and have the view order by the hidden identity column.
That way your software will not know that anything has changed: it will still be dumb and lucky.
|
|
|
|
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668918 is a reply to message #668837] |
Thu, 22 March 2018 12:22 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I assume that you are using sqlldr to load the table. You can easly add a sequence number to a separate column using the control file. say you want a sequence number to save the loading order you would have a column in your import file called (for example) seqnum. In your control file you would have the line
seqnum recnum,
The recnum command says to use an incrementing number to fill the seqnum column in my workfile.
|
|
|
|
|