Hello all,
I was able to create views using order by clause(8i).But why it is said that we should avoid order by while creating views and sh be used only, while querying it.
Please clarify.
regards.

09-10-2001, 03:20 PM

raghud

I don't think it makes a big difference .. In older versions we can't use order by to create view's that's why they may be advising not to use ...In both cases you need properly tuned parameters such as sort_area_size or sort_area_retained_size or bigger TEMP tablespaces ..

09-10-2001, 03:40 PM

chrisrlong

Interesting.

I've not read where they say not to use them, so I'm not positive about what their reservations might be. However...

one must consider that there are very few uses for an ORDER BY in a view. Any other time, it is overhead...

As you can see, when I add my own ORDER BY to the view, the data is sorted twice - bad move. This same situation will happen if the view is joined to other views. The data will always be sorted before joining to other tables. This is definite overhead. It's also possible that certain execution paths may be restricted based on the ORDER BY, meaning that the optimizer may not be able to properly optimize your statement.

I would say that these are sufficient reasons to make one think twice before adding an ORDER BY to a view definition.

- Chris

09-11-2001, 10:07 AM

uma

Hai!

Sometimes using order by will rearrange the order of row numbers when used in combination with other functions like decode and mod.In that case group by can be used to perform the ordering,so that the goal of using decode,rownum can be accomplished.

Correct me if I am wrong.
regards.

09-11-2001, 10:52 PM

chrisrlong

Yes. That would fall under the'very few uses' I was referring to. Of course, in the larger sense, I was speaking about an ORDER BY in an in-line view, because that would be a more proper way to re-order your rows for purposes of establishing ordered ROWNUMs. By that, I mean that if I needed to re-order the rows for ROWNUM usage, such as in a 'windowing' situation, I would use and in-line view and *still* not hard-code the ORDER BY in a database view.