order by in views

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.

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 ..

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...

Code:

---

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)

---

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

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.

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.

- Chris

Thanks a lot for the info