How to have a group by expression using index
Date: 1 Jun 2002 05:41:07 -0700
Message-ID: <992a9b5a.0206010441.7567b6ae_at_posting.google.com>
HI,
SELECT Col1,
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 :
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 Sat Jun 01 2002 - 14:41:07 CEST