Re: GROUP BY

TurkBear <jvgreco_at_nospamprimenet.com> wrote in article <33fd4766.167435399_at_news.primenet.com>...
> 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
>

No, it wouldn't. In both cases, with or without indexes, full table scan will
be performed.
If only subset of records is needed then you should index mar_status column and try to avoid using "in" in where clause. Depending on your data in mar_status column you should try

select mar_status, apr_status , count(*) from master_table
where mar_status < '2'
group by mar_status, apr_status;

or

select mar_status, apr_status , count(*) from master_table
where mar_status between '1' and '2'
group by mar_status, apr_status;

or

select '1', apr_status , count(*)
from master_table
where mar_status = '1'
group by mar_status, apr_status
union
select '2', apr_status , count(*)
from master_table
where mar_status = '2'
group by mar_status, apr_status;

Mario Simic
IN2 d.o.o.
Zagreb, Croatia Received on Sun Aug 31 1997 - 00:00:00 CDT