Re: GROUP BY

Tansel Ozkan <tansel_at_openix.com> wrote:

>Hello Oracle gurus,
>
>My question is as follows:
>
>I have a huge table with 10,000,000 records. And we are frequently
>running a query with a GROUP BY statement. Would an index on the grouped
>columns improve the performance? It seems to me that there is not really
>any need for an index since the best algorithm for such a query would
>have to do a full-table scan and increment the appropriate counters.
>
>the SQL statement is :
>
>select mar_status, apr_status , count(*)
>from master_table
>group by mar_status, apr_status;
>
>If only subset of the records are needed by specifying a where clause as
>shown in the SQL statement below and one of the filters is the grouped
>column, would that make a difference?
>
>select mar_status, apr_status , count(*)
>from master_table
>where mar_status in ('1','2')
>group by mar_status, apr_status;
>
>
>Thanks and have a crash free day...
>
>Tansel

I would index the group by columns and also one to be used in the count statement, i.e. count(indexed_col) - this should be more effecient.

John Greco Received on Wed Aug 20 1997 - 00:00:00 CDT