Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Group by nosort
I thought the hint must immediately follow the SELECT word.
Kevin Gray wrote:
HiReceived on Sun Sep 27 1998 - 17:00:00 CDTI am using Oracle 7.3.3 and need to look at using the NOSORT option on the
GROUP BY clause of the SELECT statement.The V733_PLANS_ENABLED parameter is set to TRUE in my init.ora
initialization file.I am considering using the NOSORT option to get better performance as there
is a composite index on col1, col2, col3 and col4. The index is called
index1.The select statement I am doing is as follows.
explain plan for
select col1, col2, col3, col4, sum(col5), count(*)
from table
group by nosort --+ INDEX(table1 index1)
col1, col2, col3, col4;The explain plan for this (without the nosort keyword) suggests that the
group by is being sorted and the table access is full. (This last bit is
understandable).I am getting a 'ORA-00933: SQL command not properly ended' error at the
start of the col1, col2, ... on the group by statement.Any ideas what I am doing wrong here would be much appreciated. Also what
is the correct way to use GROUP BY NOSORT. I found the little
documentation on the NOSORT clause in the Oracle 7 Server Tuning guide, not
very helpful at all.Thanks in advance.
Kevin Gray
Senior Consultant
The Customer Engagement Company, UK
Email: kevin.gray@h2engage.co.uk
WWW: http://www.h2engage.co.uk/