Re: Why Oracle View can not use Order by clause in the View definition query ?...

A copy of this was sent to Paul Brewer <paulb_at_pbrewer.demon.co.uk> (if that email address didn't require changing) On Wed, 13 May 1998 11:20:04 +0100, you wrote:

>>create view dept_v as select * from dept order by deptno;
>>
>>And then you ran the query:
>>
>>select * from emp_v, dept_v where emp_v.empno = dept_v.deptno;
>>
>>What would happen to the order by's since they conflict with eachother?
>>
>All that you write makes perfect sense. But what would happen if you
>'cheated' in the creation of the views and grouped by the primary keys?

group by != order by

group by doesn't = SORT. It just so that it apparently happens all of the time (but does it)...

What about partitioned tables and parallel query? I have three partitions, partitioned on the column X such that X='a' is in partition 1, X='b' in partition 2, X='c' in parition 3. I issue a query:

   select x, count(*)
     from T
    group by x;

Now, all of a sudden I have 3 scanners, 3 sorters/aggregators -- the first one finishes and it is 'C', then 'B' finishes and then 'A'. You could get the data backwards.

In short, there is nothing in the books anywhere that say "group by will always sort the data".

So, if you create a view with a group by, you can be assured it will be group by'ed but you cannot be so sure it will be sorted (also, NLS factors can make sorted data be in a different order then group by'ed data)...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed May 13 1998 - 22:13:20 CEST