Re: How to have a group by expression using index

From: Monkey <walkingmonkey_at_hotmail.com>
Date: 2 Jun 2002 01:33:12 -0700
Message-ID: <6ac4ac63.0206020033.74a681f2_at_posting.google.com>


ccote_msl_at_yahoo.com (Christian) wrote in message news:<992a9b5a.0206010441.7567b6ae_at_posting.google.com>...
> HI,
> I have an Oracle table of approx 15 millions rows. I am using Oracle
> 8.1.7 on a Unix 2 processor server. I hve an index created on the 9
> group by (and select, except of the one that makes a Sum()) columns.
> The table contains 18 columns. For Oracle, it is not a big table. Here
> is my SQL :
>
> SELECT Col1,
> Col2,
> Col3,
> ...
> Sum(MySumColummn),
> Sum(MyOtherColumn)
> FROM MyTable
> GROUP BY Col1,
> Col2,
> Col3,
> ...
> Col9.
> As I said, I created a concatenated index on all Col1 to Col9 column.
> The index took about 30 mins to create. My probl;em is that when I
> execute an Explain plan on this query, It tells me that it does a full
> table scan and the query take 20 mins to return results. Is there a
> way to improve this query? Here are some of Oracle parameters values
> that I have set:
> SORT_AREA_SIZE = 100 megs
> SORT_MULTIBLOCKS_READ_COUNT = 8
>
> Thank you for your help,
> Christian

hi

Make sure that your optimizer mode is set to "COST" and you have analyzed your table and index.

walkingmonkey Received on Sun Jun 02 2002 - 10:33:12 CEST

Original text of this message