0

Disclaimer: I know that it is bad to not use an 'ORDER BY' in SQL when sorted data is required.

I am currently supporting a Pro*C program which is having a wierd-problem. One of the possible causes of the wierd-problem may be that the original developers (from a long time ago) have not used ORDER BY in their SQL even though the program logic depends on it! The program has been working fine all these years and started showing problems only recently.

We are trying to pin the wierd-problem to the ORDER BY mistake (there are other cause candidates like a recent port from Solaris to Linux which took place).

What shadowy things on the database end should we look at that may have changed the old sort order? Things like data files etc? Anybody have any experience with Pro*C on Solaris magically sorting the result-set?

Thanks!

dvlpr
  • 139
  • 8

2 Answers2

2

Since you know that the program cares about the order in which results are returned and you know that the query that is submitted is missing an ORDER BY clause, is there a reason that you don't just fix the problem rather than looking to try to figure out whether the actual order of results may have changed? If you fix the known ORDER BY problem and the "weird problem" you have disappears, that would provide some pretty good evidence that the "weird problem" is, in fact, caused by the missing ORDER BY.

Unfortunately, there are lots of things that might have caused the order of results to change many of which may be impossible to track down. The most obvious cause would be a change in the execution plan. That, in turn, may have been caused either because statistics changed or because statistics didn't change enough or because of a patch or because of an initialization parameter change or because of a client configuration change among other things. If you are licensed to use the AWR (Automatic Workload Repository), you might be able to find evidence that the plan has changed by looking to see if there are multiple PLAN_HASH_VALUE values for the SQL_ID in DBA_HIST_SQLSTAT over different days. If there are, you'd still have to try to figure out whether the different plans actually caused the results to be returned in a different order. Beyond a query plan change, though, there are dozens of other possible causes. The physical order of data on disk may have changed because someone reorganized the table or because someone moved data files around on the disk or because the SAN automatically rebalanced something by moving data around. Some data may have been cached (or may not have been cached) in general in the past that is now cached. An Oracle patch may have been applied.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thanks for your answer! I will research some more about AWR and Plans. I have changed the code, but it will take a week to know if it fixed the problem. And it is tough telling users that the problem is still not solved a week later :( – dvlpr Mar 13 '13 at 10:08
0

I suggest that change your physical table with view and make your required order in that view.

example

TABLE_NOT_SORTED --> rename to --> PHYS_TABLE_NOT_SORTED

CREATE VIEW TABLE_NOT_SORTED 
AS
SELECT * FROM PHYS_TABLE_NOT_SORTED
ORDER BY DESIRED_COLUMNS

For response to comment:

According to this question and Ask Tom's Answer, it seems that since Oracle does not guarantee a default sorting if you do not use "ORDER BY", they are free to change it. They are absolutely right of course. If you need sorting, use Order By.

Other than that we can not say anything about your code or default ordering.

Community
  • 1
  • 1
Atilla Ozgur
  • 14,339
  • 3
  • 49
  • 69
  • Yup, but the problem I am having is not in fixing it. I can add the ORDER BY to the program. The problem is determining if I am fixing the right problem. To do that I need to check what has changed on the DB end which has caused the order to change. – dvlpr Mar 13 '13 at 08:14
  • Only you can tell what's happened on the DB @dvlpr, people here can only guess. For instance; have you recently upgraded it? Applied a patch? Changed some of the initialisation parameters? – Ben Mar 13 '13 at 08:16
  • 1
    There was no user intervention on the DB, no upgrades, no patches etc. What I am looking for is, 'probable' causes like data files, partitions, etc from your past experience. (I say probable because I think this is not documented). – dvlpr Mar 13 '13 at 08:24
  • It seems so, there is almost no results with search. – Atilla Ozgur Mar 13 '13 at 08:26