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.
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 ..
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.
CREATE VIEW CRL_V1 AS SELECT * FROM ORG ORDER BY ORG_DBA_NM
SELECT * FROM CRL_V1 ORDER BY ORG_PK
SELECT STATEMENT Optimizer=CHOOSE (Cost=14331 Card=22734 Bytes=87321294)
SORT (ORDER BY) (Cost=14331 Card=22734 Bytes=87321294)
VIEW OF CRL_V1 (Cost=1295 Card=22734 Bytes=87321294)
SORT (ORDER BY) (Cost=1295 Card=22734 Bytes=11185128)
TABLE ACCESS (FULL) OF ORG (Cost=33 Card=22734 Bytes=11185128)
I would say that these are sufficient reasons to make one think twice before adding an ORDER BY to a view definition.
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.
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.
Thanks a lot for the info
Click Here to Expand Forum to Full Width