Probably lucky, but possibly aided by using freelist management for a long time
and only caught out when you first loaded the data into a tablespace using ASSM.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Storey, Robert (DCSO) <RStorey@xxxxxxxxxxxxxxxxxx>
Sent: 07 December 2018 15:04
To: Dominic Brooks
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: General Quey question.
Thanks for the responses. You learn something every day. Ashamed to say that
I never know or caught on to the fact that a query would not return the rows in
the same order. I had never run across this in the 18 years of working with my
existing data and moving it from machine to machine. Our data is rarely
deleted, just not accessed after time.
Maybe I was just extremely lucky!.
Thanks all.
From: Dominic Brooks [mailto:dombrooks@xxxxxxxxxxx]
Sent: Friday, December 07, 2018 9:02 AM
To: Storey, Robert (DCSO)
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: General Quey question.
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
--
//www.freelists.org/webpage/oracle-l
