order by in views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: order by in views

  1. #1
    Join Date
    Mar 2001
    Posts
    78

    Exclamation

    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.

  2. #2
    Join Date
    Aug 2001
    Posts
    267
    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 ..
    Raghu

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Mar 2001
    Posts
    9
    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.
    uma

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Mar 2001
    Posts
    78
    Thanks a lot for the info

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width