Re: How to have a group by expression using index

Analyze your table and indexes.
Also, consider using parallel query by specifying a parallel hint use alter table <table name> parallel <degree> to change the degree of parallelism for the table
partition your table to go across multiple disks to facilitate the full table scan with
the parallel query so that you dont have disk contention.

you can also increase/decrease db_file_multiblock_read_count at session level

The cost based optimizer makes execution plan based on the statistics for the table and index(es). If it is "cheaper" to do a full table scan, then it *will*
do it since it makes sense to take the most cost effective route.

"Christian" <ccote_msl_at_yahoo.com> 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
>
Received on Wed Jun 05 2002 - 04:22:09 CEST