Re: General Quey question.
Date: Fri, 7 Dec 2018 15:09:01 +0000
Message-ID: <CWXP265MB1493269DC7B004A54448A8FCA5AA0_at_CWXP265MB1493.GBRP265.PROD.OUTLOOK.COM>
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Storey, Robert (DCSO) <RStorey_at_DCSO.nashville.org> Sent: 07 December 2018 15:04
To: Dominic Brooks
Cc: oracle-l_at_freelists.org
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_at_hotmail.com]
Sent: Friday, December 07, 2018 9:02 AM
To: Storey, Robert (DCSO)
Cc: oracle-l_at_freelists.org
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_at_DCSO.nashville.org<mailto:RStorey_at_DCSO.nashville.org>> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 07 2018 - 16:09:01 CET