Re: sort order on VIEWs ?
Date: Wed, 20 Apr 2011 23:06:05 +0300
Message-ID: <BANLkTimvgD=irx-G_7ty4No41sV3VStRSw_at_mail.gmail.com>
You might want to read this ask tom page: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3229251700346962181
"You can create a view with an order by - any select against the view would return the data sorted..
HOWEVER, a query that joins that view or references another table in fact, might not be 'sorted' anymore.
So, you can create a view, applications would use the view not the table - but you would have to bear in mind that if they use more than just the view - the results might not be sorted, they might be sorted - it depends on the plan we decide to use."
Harel Safra
On Wed, Apr 20, 2011 at 9:45 PM, Michael Moore <michaeljmoore_at_gmail.com>wrote:
> I am really trying to make a case to somebody else that he should* not* do
> it. However, there is the matter of convenience.
>
> Select * from my_view order by
> cust_num,billing_cycle_period,invoice_id,line_id,unit_price;
>
> Now if I have to type that out a dozen times a day, and that is always the
> sort order (i.e. nothing else really makes any sense) then it might
> be convenient to have the sort order on the view.
>
> That's the only counter argument I could come up with.
>
> Regards,
>
> Mike
>
>
> On Wed, Apr 20, 2011 at 11:39 AM, Lange, Kevin G <kevin.lange_at_ppoone.com>wrote:
>
>> Yea, I was gonna ask you the question ........ why would you include an
>> order by in a view ?
>>
>> ------------------------------
>> *From:* Michael Moore [mailto:michaeljmoore_at_gmail.com]
>> *Sent:* Wednesday, April 20, 2011 1:38 PM
>> *To:* Lange, Kevin G
>> *Cc:* oracle-l_at_freelists.org
>> *Subject:* Re: sort order on VIEWs ?
>>
>> Thanks kevin.
>> Since asking the question I did some digging.
>>
>> http://en.wikipedia.org/wiki/View_(database)
>> 4th paragraph
>>
>> The take-away is that you can do it in Oracle, but it's not standard and
>> probably not a good idea.
>>
>> Regards,
>> Mike
>>
>>
>> On Wed, Apr 20, 2011 at 11:34 AM, Lange, Kevin G <kevin.lange_at_ppoone.com>wrote:
>>
>>> Not sure about a Guarantee, but, in my tests, as long as I did not
>>> include an order by on the query contrary to the order by on the view, I got
>>> my data in the order of the views order by.
>>>
>>> Of course, it was a small sampling of data.
>>>
>>> ------------------------------
>>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Michael Moore
>>> *Sent:* Wednesday, April 20, 2011 1:20 PM
>>> *To:* oracle-l_at_freelists.org
>>> *Subject:* sort order on VIEWs ?
>>>
>>> If a VIEW has an ORDER BY clause, am I guaranteed to get that sort
>>> order when I use the VIEW (provided of course that I don't specify a
>>> different sort order)?
>>>
>>> Regards,
>>> Mike
>>>
>>> This e-mail, including attachments, may include confidential and/or
>>> proprietary information, and may be used only by the person or entity
>>> to which it is addressed. If the reader of this e-mail is not the
>>> intended
>>> recipient or his or her authorized agent, the reader is hereby notified
>>> that any dissemination, distribution or copying of this e-mail is
>>> prohibited. If you have received this e-mail in error, please notify the
>>> sender by replying to this message and delete this e-mail immediately.
>>>
>>
>>
>> This e-mail, including attachments, may include confidential and/or
>> proprietary information, and may be used only by the person or entity
>> to which it is addressed. If the reader of this e-mail is not the intended
>> recipient or his or her authorized agent, the reader is hereby notified
>> that any dissemination, distribution or copying of this e-mail is
>> prohibited. If you have received this e-mail in error, please notify the
>> sender by replying to this message and delete this e-mail immediately.
>>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 20 2011 - 15:06:05 CDT