| 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: Why doesn't this work?
A copy of this was sent to "KeyStroke (Jack L. Swayze Sr.)" <KeyStrk_at_Feist.Com>
On Sun, 13 Dec 1998 12:11:53 -0600, you wrote:
>You are obviously quite emotional about this.
you too.
> I frequently get this reaction
>from people when I point out a flaw in their 'sacred cow' software.
no, I am pointing out that
1- views do not support order bys, never have 2- group by is NOT equal to order by and cannot be made so in general 3- group by doesn't even have to sort by the columns you specify (see below)
> The flaw is
>that Oracle doesn't allow ORDER BY in views.
SQL doesn't allow for it. It doesn't make sense to order by views. You cannot create an ORDERED table -- views are tables, they are *not* simply stored queries.
In many cases, views are built on views and people query from more then one view. What does it mean to have an order by on one of those then? This is like people who ask to insert "sorted" data into a table so it comes back out sorted -- you can't and it won't.
> There is no reason for that other
>than philosophical arrogance. The purpose for using database software in
>business is to make money, not to 'preach' "Relational Purity" to the masses.
>So, 'Mr. From Oracle, but your views don't reflect Oracle' - FIX THE SOFTWARE.
>
if it ain't broke. (and such disclaimers are pretty typical -- I'm just here as a person, not as "Oracle Corporation")
>As to my having an error in the syntax, I would admit that. As I don't have a
>working version of Oracle on my home PC (Oracle Lite is such a CROCK! it is
>still way, way to hard to install and make work - things on a PC should be
>SIMPLE), and as I didn't take the time to dial into work to check my syntax, I
>will admit I made a coding error.
>
>As to GROUP BY not equaling ORDER BY - that is obvious. What YOU (Mr. ARROGANT)
>fail to realize is that I CAN make GROUP BY act like an ORDER BY as long as
>there is a primary key (of reasonable number of columns) on the table.
>
No you cannot make group by act like order by (what does a primary key have to do with anything anyway? If I don't put the primary key into the view?). You gave a general purpose solution that purported that group by orders, so all you need to do is group by. Your example used derived columns and one column from the table (primary key not included).
Try this in Oracle:
SQL> create table emp as select * from scott.emp; Table created.
SQL> create index emp_idx on emp (job,ename); Index created.
SQL> select ename, job
2 from emp
3 where ename > chr(0) and job > chr(0)
4 group by ename, job;
ENAME JOB
---------- ---------
FORD ANALYST SCOTT ANALYST ADAMS CLERK JAMES CLERK MILLER CLERK SMITH CLERK BLAKE MANAGER CLARK MANAGER JONES MANAGER KING PRESIDENT ALLEN SALESMAN MARTIN SALESMAN TURNER SALESMAN WARD SALESMAN
14 rows selected.
SQL> select ename, job
2 from emp
3 group by ename, job;
ENAME JOB
---------- ---------
ADAMS CLERK ALLEN SALESMAN BLAKE MANAGER CLARK MANAGER FORD ANALYST JAMES CLERK JONES MANAGER KING PRESIDENT MARTIN SALESMAN MILLER CLERK SCOTT ANALYST SMITH CLERK TURNER SALESMAN WARD SALESMAN
14 rows selected.
SQL> HMMM. A where clause lets us use an index (as would different optimizer modes). All of a sudden, GROUP BY ENAME, JOB does not ORDER BY ENAME, JOB (since the optimizer correctly recognizes that a group by ENAME, JOB is the SAME as group by JOB, ENAME and decided it did not NEED to sort after all -- the index did all of the work for us in the first query but returned the data sorted by JOB, ENAME -- NOT ename, job.
So, you give advice to a novice (i make that judgement based on the question about views), telling them basically "use group by in a view and you'll be all right". Not only does it
Its 100% bad advice to give as the performance implications are terrible as well.
>BACK OFF - ARROGANT! - and think about helping people solve their problems -
I did in this case and I do in general. He asked "why doesn't this work" and I told him. I told him why it doesn't work. there are no good 100% workarounds. I told him how to fix it -- use order by in the select. That is the only answer that works.
I believe that by following up your inaccurate post with more information, I did help him... He gave a simple example to demonstrate his question -- your group by trick may (or may not) have worked but its not the right answer IN GENERAL. If he started using it without understanding the implications -- he could get into real trouble.
>instead of being so insecure to wrap your intelligence up with childish
>arrogance. REMEMBER! we, your CUSTOMERS are doing YOU a favor by buying and
>using your software. YOU are not doing us a favor by providing it - so STOP
>ACTING AS IF THE WORLD NEEDS TO BOW DOWN TO THE GOD OR ORACLE.
>
maybe you could goto http://www.dejanews.com/ and do a 'power search'. put my email in the author field. You might find that I actually spend quite a bit of time here trying to find solutions, explanations, workarounds, fixes, etc. Its generally well recieved but this time I seem to have struck a nerve.
When I see something wrong -- I follow up. You gave an answer (as a followup to my answer) that was plain BAD ADVICE. Sorry you disagree but this is what the groups are about.
I stand by all of the statements below.
>Thomas Kyte wrote:
>>
>> A copy of this was sent to "KeyStroke (Jack L. Swayze Sr.)" <KeyStrk_at_Feist.Com>
>>
>> On Sun, 13 Dec 1998 09:47:46 -0600, you wrote:
>>
>> >Not true on two counts.
>>
>> what do you mean Not true. It is true that count(*) must be aliased and I did
>> not comment on that, however it is 100% true that:
>>
>> -- VIEWS cannot have ORDER BYs.
>> -- GROUP BY != ORDER BY
>> -- Your view doesn't work, the way you did it won't work since
>>
>> 1 create view v as
>> 2 select ename, cnt
>> 3 from
>> 4 (select ename, count(*) cnt
>> 5 from emp
>> 6 group by ename
>> 7 )
>> 8* group by cnt
>> SQL> /
>> select ename, cnt
>> *
>> ERROR at line 2:
>> ORA-00979: not a GROUP BY expression
>>
>> so you would have to add in ename as well (group by cnt, ename). The
>> performance of such as thing is pretty bad (you have to sort by the ENTIRE
>> record in the view not just the cnt field), and since you have to sort by the
>> entire field, you may quickly hit:
>>
>> ERROR:
>> ORA-01467: sort key too long
>>
>> since you have to sort lots more fields then the order by clause would have had.
>>
>> Also, in general GROUP BY is NOT even close to ORDER BY.
>>
>> Lets say the example did not have an aggregate in the view definition. lets say
>> instead the view they were trying to create was:
>>
>> create view v
>> as select job, sal from emp
>> order by sal;
>>
>> Consider then:
>>
>> SQL> select job, sal from emp
>> 2 order by sal;
>>
>> JOB SAL
>> --------- ----------
>> CLERK 800
>> CLERK 950
>> PRESIDENT 1000
>> CLERK 1100
>> SALESMAN 1250
>> SALESMAN 1250
>> CLERK 1400
>> SALESMAN 1500
>> SALESMAN 1600
>> MANAGER 2850
>> MANAGER 2975
>> MANAGER 3000
>> ANALYST 3000
>> ANALYST 3000
>>
>> 14 rows selected.
>>
>> SQL> select job, sal from emp group by sal, job;
>>
>> JOB SAL
>> --------- ----------
>> CLERK 800
>> CLERK 950
>> PRESIDENT 1000
>> CLERK 1100
>> SALESMAN 1250
>> CLERK 1400
>> SALESMAN 1500
>> SALESMAN 1600
>> MANAGER 2850
>> MANAGER 2975
>> ANALYST 3000
>> MANAGER 3000
>>
>> 12 rows selected.
>>
>> GROUP BY has a tendency to GROUP things -- remove duplicates, the above 2
>> queries are NOT the same, return different rows. Granted, in this one
>> particular example, name was already uniqued in the first group by so no rows
>> would be removed, however, in general -- rows can and will be silently removed,
>> changing the result set.
>>
>> Lastly, order by X desc is really hard to do with group by (especially on
>> character fields). You can do it with numbers by "group by -x" but then you
>> have to "select -x" as well and the person using the view would have to know
>> they need to "select -x" from the view to undo your negation.
>>
>> Looking at your reasoning below, "what if you bought a canned application and
>> the application doesn't present the data the way you want....." If you take a
>> canned application and rewrite its views as you describe above -- you will most
>> likely BREAK the application. 1 -- adding the group by makes the view
>> non-updateable and it might need to be and 2 -- you change the results of the
>> view by grouping.
>>
>> GROUP BY != ORDER BY for sorting either.
>>
>> Group by MIGHT NOT ALWAYS sort. There is nothing anyway saying that it must and
>> .... with partitions and parallel query (consider if the data is partitioned on
>> the values you group by -- parallel query will kick in and tend to parallelize
>> by partition. Each parition will sort itself but since they are non-overlapping
>> -- they can be merged in any order. Partitions and parallel query can give you
>> non-ordered group bys).
>>
>> >1) one reason the CREATE VIEW doesn't work is that the
>> >COUNT(NAME) has to be given an alias. 2) you can accomplish the same thing as
>> >an ORDER BY by using GROUP BY. This means that you don't have to make the
>> >application apply the ORDER BY clause. Please refer to the discussion in this
>> >newsgroup labeled 'make ORDER BY in a view'. There may be reasons technical,
>> >political, or economic which restrict changing the original SELECT statement and
>> >putting an ORDER BY clause on it. What if you bought a 'canned' application and
>> >the application doesn't present the data to you in the fashion you wish? You
>> >don't have the source code, so you cant change the original SELECT statement.
>> >Therefore it would be much more useful if the DBMS allowed and ORDER BY claus on
>> >the view, but since it doesn't, you can accomplish the same thing this way.
>> >
>> >PMG try this:
>> >
>> >create view v as
>> >select name, name_cnt from
>> >(select name_cnt, name, count(*) no_info from
>> > (select name, count(name) name_cnt
>> > from emp
>> > group by name
>> > )
>> > group by name_cnt
>> >);
>> >
>> >Thomas Kyte wrote:
>> >>
>> >> A copy of this was sent to PMG <pete_g_at_2xtreme.net>
>> >> (if that email address didn't require changing)
>> >> On Sun, 13 Dec 1998 06:35:33 GMT, you wrote:
>> >>
>> >> >This query works in SQLPlus:
>> >> >
>> >> >select name, count(name)
>> >> >from emp
>> >> >group by name
>> >> >order by count(name) desc;
>> >> >
>> >> >But why doesn't this work:
>> >> >
>> >> >create view v as
>> >> >select name, count(name)
>> >> >from emp
>> >> >group by name
>> >> >order by count(name) desc;
>> >> >
>> >> >I thought views were just stored queries. So if the first one works why
>> >> >doesn't the second work?
>> >>
>> >> views are not just stored queries. A view is much more like a database table
>> >> then a stored query. Since tables and relational sets in general, do not have
>> >> ORDER, order by not only doesn't make sense on them -- it is not allowed in the
>> >> language.
>> >>
>> >> In order for a client applicatoin using a SELECT to be gauranteed to get the
>> >> data in sorted order -- that application must apply the order by clause.
>> >>
[sigs snipped]
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 - 13:26:06 CST
 |
 |