Group by nosort

Hi

I 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_at_h2engage.co.uk
WWW: http://www.h2engage.co.uk/ Received on Sun Sep 27 1998 - 06:46:19 CDT