Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 10 vs Oracle 12: order from SELECT statement (Oracle12)
Oracle 10 vs Oracle 12: order from SELECT statement [message #668779] Wed, 14 March 2018 05:59 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 #668782 is a reply to message #668779] Wed, 14 March 2018 06:44 Go to previous messageGo to next message
EdStevens
Messages: 1233
Registered: September 2013
Senior Member
And to follow on from CookieMonster ..

Oracle has NEVER, EVER guaranteed an implied order of rows in a result set without use of ORDER BY. The fact that you seemed to get the rows in the order you wanted without using ORDER BY was sheer dumb luck.

What possible objection could you have to using ORDER BY?
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668784 is a reply to message #668782] Wed, 14 March 2018 07:19 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi,


Unless and until you add "order by" to a query, you cannot say ANYTHING about the order of the rows returned. Well, short of 'you cannot rely on the order of the rows being returned'.
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668785 is a reply to message #668782] Wed, 14 March 2018 07:38 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 #668810 is a reply to message #668782] Thu, 15 March 2018 05:21 Go to previous messageGo to next message
basileus
Messages: 3
Registered: March 2018
Junior Member
EdStevens wrote on Wed, 14 March 2018 06:44
And to follow on from CookieMonster ..

Oracle has NEVER, EVER guaranteed an implied order of rows in a result set without use of ORDER BY. The fact that you seemed to get the rows in the order you wanted without using ORDER BY was sheer dumb luck.

What possible objection could you have to using ORDER BY?
Because I extract file .dml from tables and i can't insert a different ORDER BY of each table

What I should do ? How can I change this behaviour ?

Thanks
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668815 is a reply to message #668810] Thu, 15 March 2018 06:14 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 #668817 is a reply to message #668785] Thu, 15 March 2018 06:38 Go to previous messageGo to next message
EdStevens
Messages: 1233
Registered: September 2013
Senior Member
cookiemonster wrote on Wed, 14 March 2018 07:38
I'm not sure I'd use the phrase dumb luck.
<snip>
Point is, because the behaviour appears so reliable most people won't believe you when you describe it as luck.
Just because they don't believe me doesn't mean it isn't true. People can be 100% convinced of something and still be wrong. If nothing else, the "dumb luck" factor comes in when Oracle changes the algorithm.

It reminds me of when COBOL-85 was introduced. We (the org I was at at the time) asked our IBM field rep if we would need to re-compile all of our code with the new compiler. The response was "all existing load modules will continue to work as always -- until such time as they don't". Or to put it to the current discussion, "all existing (unorderd) queries will continue to return data in the same order -- until such time as they don't."

Smile
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668818 is a reply to message #668815] Thu, 15 March 2018 06:53 Go to previous messageGo to next message
basileus
Messages: 3
Registered: March 2018
Junior Member
cookiemonster wrote on Thu, 15 March 2018 06:14
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?
because some applications reading files need to have data ordered
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668819 is a reply to message #668818] Thu, 15 March 2018 07:21 Go to previous messageGo to next message
Alien
Messages: 279
Registered: June 1999
Senior Member
Hi,

you'll have to add a column to your table to store an ordering value. Then order by that column.

Regards,

Alien
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668835 is a reply to message #668810] Fri, 16 March 2018 08:52 Go to previous messageGo to next message
EdStevens
Messages: 1233
Registered: September 2013
Senior Member
basileus wrote on Thu, 15 March 2018 05:21
[
Because I extract file .dml from tables and i can't insert a different ORDER BY of each table

Why can't you specify a different ORDER BY for each table?
You need to explain more about the whole process here. Starting with exactly what do you mean by "extract file dml from tables". And how this fits in with your later statement "because some applications reading files need to have data ordered ". What files? And how do these files relate to what you are reading from the database that you claim you can't use an ORDER BY clause.

Bottom line is this: if you want a result set to be ordered by some criteria, you must use an ORDER BY clause. Always have. Always will. The ORDER BY clause is the mechanism for guaranteeing a sort order. There is no other. Now you have two choices. Either (1) re-write your code to include the necessary ORDER BY clause, or (2) re-think your supposed requirement to produce an ordered result set.

Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668836 is a reply to message #668818] Fri, 16 March 2018 09:49 Go to previous messageGo to next message
John Watson
Messages: 8323
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 #668837 is a reply to message #668836] Fri, 16 March 2018 09:53 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
*Shudder*

That could have all sorts of negative performance impacts on code that doesn't care about the order.
Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668918 is a reply to message #668837] Thu, 22 March 2018 12:22 Go to previous messageGo to next message
Bill B
Messages: 1968
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.

Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668921 is a reply to message #668918] Fri, 23 March 2018 07:09 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi,

Please consider, If this one required sequence number and having one time data load process.Then no need to create sequence,you can use Pseudocolumn for generating sequence like:


  SELECT <column,list>,ROWNUM
  FROM   x;

Re: Oracle 10 vs Oracle 12: order from SELECT statement [message #668922 is a reply to message #668921] Fri, 23 March 2018 07:53 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
But unless the select has an order by you've just given the data a random order.
Previous Topic: Transaction 2 happen within 60 days of Transaction 1
Next Topic: Display Comma Separated Values
Goto Forum:
  


Current Time: Fri Aug 14 06:52:02 CDT 2020