| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Make ORDER BY in a view?
A copy of this was sent to Mungo Henning <mungoh_at_itacs.strath.ac.uk>
(if that email address didn't require changing)
On Fri, 11 Dec 1998 12:23:44 +0000, you wrote:
>KeyStroke (Jack L. Swayze Sr.) wrote:
>
>> the trick to all this is that GROUP BY will order the result, just as
>> well as ORDER BY will. and you _CAN_ have a GROUP BY in a view!
>
>Whilst not contradicting this nice work-around, how stable is the
>postulated solution?
Not very stable at all actually. Influences such as INDEXES, Partitions, Parallel Query, NULL/NOT NULL constraints, OPTIMIZER mode and others will affect it.
>As I (badly?) understand it, "group by" clusters records according to
>some condition. The clustering may be implemented by sorting, but it
>needn't always be so. Hence the separate "group by" and "order by"
>clauses.
EXACTLY. Well said. That is 100% correct. You understand well actually.
>What would happen if a new "group by" mechanism were to be invented
>a few weeks after you've rolled out your application and you had
>assumed that "group by" performed an "order by"...
You don't even need to wait a few weeks -- don't even need to wait 1 day. its ALWAYS been that way.
There are 2 GROUP functions for GROUP BY:
They have already disappeared. Here is an example from 7.1 (taking you back to 1994)
SQL> create table emp as select * from scott.emp where rownum <= 5; Table created.
SQL> create index emp_idx on emp(job,ename); Index created.
SQL> select ename, job from emp where ename >chr(0) and job >chr(0) 2 group by ename, job ;
ENAME JOB
---------- ---------
smith CLERK jones MANAGER allen SALESMAN martin SALESMAN ward SALESMAN
group by ename,job != order by ename, job. Here the optimizer used an index to get the answer and realizing the Group by ename, job was the same as group by job, ename -- did it that way. think about what the effect on your application would be if the emp_idx was added AFTER your application went production.
In short -- anyone who relies on the order of rows in a SQL statement for their processing and does not use ORDER BY is doing the wrong thing.
>As another poster said, one ultimate ORDER BY may be all that is
>necessary.
>
not only "all that is neccessary" -- its the only thing that works reliably and all of the time...
Here is another small example that doesn't even need the where clause on a table. All that happened was someone analyzed the table. All of a sudden, the group by that sorted -- stops sorting!
SQL> create table the_table ( pk1 int, 2 pk2 int, 3 nk1 int, 4 nk2 int, 5 primary key(pk1,pk2) )6 /
SQL> create index the_table_idx on the_table(pk1,nk1,pk2,nk2); Index created.
SQL> insert into the_table values (1, 1, 2, 2 ); 1 row created.
SQL> insert into the_table values (2, 1, 1, 1 ); 1 row created.
SQL> select nk1, nk2, pk1, pk2, count(*) a_count_1
2 from the_table
3 group by nk1, nk2, pk1, pk2;
NK1 NK2 PK1 PK2 A_COUNT_1 ---------- ---------- ---------- ---------- ----------
1 1 2 1 1
2 2 1 1 1
SQL> analyze table the_table compute statistics; Table analyzed.
SQL> select nk1, nk2, pk1, pk2, count(*) a_count_2
2 from the_table
3 group by nk1, nk2, pk1, pk2;
NK1 NK2 PK1 PK2 A_COUNT_2 ---------- ---------- ---------- ---------- ----------
2 2 1 1 1
1 1 2 1 1
>Mungo Henning
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
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 Sun Dec 13 1998 - 19:59:52 CST
 |
 |