General Quey question.

[Storey, Robert (DCSO)]

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

[Jose Rodriguez]

Not meaning to be rude here but you missed an important point: Oracle does
not guarantee the order of the returned rows unless an ORDER BY clause is
used in the query.
Take a look as this quite old AskTom entry
<https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6257473400346237629>
.

So, no, you probably haven't done anything wrong with the new box,
everything is working as intended and now that you added the required
clause, you should be totally fine.

Cheers.

[image: Pythian] <http://www.pythian.com/>
*Jose Rodriguez* | Oracle Database Consultant | [image: LinkedIn]
<https://www.linkedin.com/company/pythian>
*t* [phone] <+1+613+565+8696> *ext.* 1393
*m* [phone] <+34+607+55+49+91>
***@pythian.com
*www.pythian.com* <https://www.pythian.com/>
[image: Pythian] <https://www.pythian.com/email-footer-click>


On Fri, 7 Dec 2018 at 15:48, Storey, Robert (DCSO) <

Post by Storey, Robert (DCSO)
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

[Jacek Gębal]

It's not even Oracle.
It's fundamental characteristics of SQL language.

Post by Jose Rodriguez
Not meaning to be rude here but you missed an important point: Oracle does
not guarantee the order of the returned rows unless an ORDER BY clause is
used in the query.
Take a look as this quite old AskTom entry
<https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6257473400346237629>
.
So, no, you probably haven't done anything wrong with the new box,
everything is working as intended and now that you added the required
clause, you should be totally fine.
Cheers.
[image: Pythian] <http://www.pythian.com/>
*Jose Rodriguez* | Oracle Database Consultant | [image: LinkedIn]
<https://www.linkedin.com/company/pythian>
*t* [phone] <+1+613+565+8696> *ext.* 1393
*m* [phone] <+34+607+55+49+91>
*www.pythian.com* <https://www.pythian.com/>
[image: Pythian] <https://www.pythian.com/email-footer-click>
On Fri, 7 Dec 2018 at 15:48, Storey, Robert (DCSO) <

Post by Storey, Robert (DCSO)
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

--

[Dominic Brooks]

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) <***@DCSO.nashville.org<mailto:***@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

[Storey, Robert (DCSO)]

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:***@hotmail.com]
Sent: Friday, December 07, 2018 9:02 AM
To: Storey, Robert (DCSO)
Cc: oracle-***@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) <***@DCSO.nashville.org<mailto:***@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

[Jonathan Lewis]

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-***@freelists.org <oracle-l-***@freelists.org> on behalf of Storey, Robert (DCSO) <***@DCSO.nashville.org>
Sent: 07 December 2018 15:04
To: Dominic Brooks
Cc: oracle-***@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:***@hotmail.com]
Sent: Friday, December 07, 2018 9:02 AM
To: Storey, Robert (DCSO)
Cc: oracle-***@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) <***@DCSO.nashville.org<mailto:***@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-l