Re: GROUP BY

From: Martin Smith <mfsmith_at_erols.com>
Date: 1997/09/01
Message-ID: <340AED88.E95F6456@erols.com>#1/1

This is a multi-part message in MIME format.

--------------09339FD27AF262DFB5D28AC1
Content-Type: text/plain; charset=us-ascii
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Transfer-Encoding: 7bit

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

--------------09339FD27AF262DFB5D28AC1
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Martin Smith
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Martin Smith
n:              Smith;Martin
org:            US International Trade Commission
adr:            500 E Street SW;;;Washington;DC;20436;USA
email;internet: mfsmith_at_erols.com
title:          Director, Information Services
tel;work:       (202) 205-3258
tel;fax:        (202) 205-2024
tel;home:       (703) 734-1039

x-mozilla-cpt: ;0
x-mozilla-html: TRUE
end: vcard

--------------09339FD27AF262DFB5D28AC1-- Received on Mon Sep 01 1997 - 00:00:00 CDT