Re: GROUP BY

From: Martin Smith <mfsmith_at_erols.com>
Date: 1997/08/22
Message-ID: <33FE54E4.72A35671@erols.com>#1/1

This is a multi-part message in MIME format.

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

Using a bit-mapped index on the WHERE variable(s) can make a HUGE difference, assuming you're actually cutting down the selected rows significantly.

Actually, Oracle should in principle be able to do the count using JUST the bit-mapped index itself--it has the info. Wonder if that's implemented??

mfs

TurkBear wrote:

> 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

--------------69AFF0C18B7E21CBE338D6BF
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

--------------69AFF0C18B7E21CBE338D6BF-- Received on Fri Aug 22 1997 - 00:00:00 CDT