Geerlings Mark wrote:
> I agree with the good comments from Mr. Goodson and Mr. Koster, Oracle
> queries always need an "order by" clause to guarantee a particular
> order.
>
> But there is another option that may help besides rewriting all of the
> code. That is to use views. You would have to create a view for every
> table that has the problem, and this is a multi-step process, and this
> assumes that you always want the results from a particular table in the
> same order. Here is an example of the steps for a table named "table1":
> 1. get a list of the grants on table1
> 2. rename table1 to table1_base
> 3. create view table1 as select * from table1_base order by [whatever
> order you want];
> 4. create grants for the new table1 (view) matching the grants for the
> original table1
>
> I have used views somewhat like this in the past with "canned"
> application to solve similar problems. Most applications will just
> happily use the views and not notice that someone renamed the tables.

Yes, I considered suggesting views ... my concerns, just thinking off
the top of my head (there's probably more, but I've not had much sleep
:) ), would be:

1) There'd be some impact on performance. Not much, perhaps, but it
would be there. The CBO might take a little more time to come up with an
execution plan, you're doing more dictionary hits to verify privileges,
parse statements, etc. A lot - if not most - of this might be alleviated
by cache, of course. And if your work load is largely batch instead of
OLTP it's probably not even a problem, but if you're doing a lot of OLTP
you might want to check it out carefully.

2) Support. If your application is supplied by a third party, this sort
of 'under the covers' tweaking might be enough to invalidate a support
agreement unless agreed upon up front.

3) There's always the possibility, if any code in the application is
dynamically generated, it might not like moving to views. For example,
if some code is looking at the dictionary to get the extent size for
table X, it might not react well when table X is suddenly X_BASE :)

4) And, of course ... the ORDER BY in your view, whilst natural for the
table, might not be the one you actually want to come out at the end
after joining to fourteen other tables ...

But it's certainly a viable technique, and can be used very
successfully. Personally, unless there were absolutely no other way, I'd
rather play it safe and fix the ORDER BY issues in the code, but this
could work, sure.

--
Martin

'Things need not have happened to be true. Tales and dreams are the
shadow-truths that will endure when mere facts are dust and ashes,
and forgot' - Dream Country, Neil Gaiman

Oracle LazyDBA home page