Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Group by nosort

Re: Group by nosort

From: John P. Higgins <jh33378_at_deere.com>
Date: Sun, 27 Sep 1998 17:00:00 -0500
Message-ID: <360EB560.85CCCAA1@deere.com>


I thought the hint must immediately follow the SELECT word.

Kevin Gray wrote:

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@h2engage.co.uk
WWW: http://www.h2engage.co.uk/

  Received on Sun Sep 27 1998 - 17:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US