It’s a basic “rule” - there’s no deterministic order without a deterministic
“order by”.
You’re at the mercy of execution plans, internal algorithms, and insert order
(which might be affected by an upgrade).
The introduction of HASH GROUP BY as an option instead of SORT GROUP BY was the
big one for this sort of thing years ago.
Sent from my iPhone
On 7 Dec 2018, at 14:48, Storey, Robert (DCSO)
<RStorey@xxxxxxxxxxxxxxxxxx<mailto:RStorey@xxxxxxxxxxxxxxxxxx>> wrote:
Here is the scenario.
Old 9i database. Records were created and inserted into table years ago. It’s
basically a look-up/status table with those rows never being deleted, just
certain columns updated.
Have a query in a stored procedure cursor that provides a list of values from
that table based on one of the column values. No order by clause needed.
Returns the records back to stored procedure in the order that they were
entered in the table. All is good and as expected.
Migrate the database to 11g. Have a development box that, using an export from
the 9i, I did an import to the 11g. cursor still returns the records in the
right order.
Now I migrate to my new production box. Same import used for the dev box. All
goes good.
Almost 30 days to the hour after the import and go live, this query starts
returning the records back in a completely different order. The order returned
makes absolutely no sense. Causes some annoyances to the users. Applying an
“order by value” to the cursor query and all is back to goodness.
There have been a couple other screens in my application that prior to going to
production box would return data back in a “entered order” that are now
returning the data out of that order. The application allows the user to sort
the columns of data returned so it’s a simple click to reorder.
I’ve looked at the rowids, and the rowids for the rows for the “out of order”
return set are also out of order.
Thoughts? Did I miss something setting up my new box?
Robert Storey
Database Administrator
Nashville Sheriff’s Office
615-880-1967
