Re: GROUP BY

Why not CREATE VIEW group by and query the view. Eliminating the group by will speed things considerably. Also consider partitioned tables and indexes. Partitioning can cause the query to skip several months in the master table to quickly get to the records that match.

Matt Arrocha marrocha_at_bellsouth.net

Martin Smith <mfsmith_at_erols.com> wrote in article <340AED88.E95F6456_at_erols.com>...
> This seems like a problem that bit-mapped indexes might help (possibly a
> lot.) I assume that where-ing mar_status does not cut down much on the
> number of records selected. If it DOES, then bit-map on that will
> greatly improve performance.
>
> Apart from that, it seems that if you're just doing counts, there should
> be a way to take advantage of the fact that the bit-mapped indexes
> themselves contain the count info.
>
> I'm sorry I don't know enough about Oracle's query processor to tell you
> how, though . . .
>
> Martin Smith
>
>
> Mario wrote:
>
> > 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 Wed Sep 10 1997 - 00:00:00 CDT