General information
Title:
How to have a group by expression using index
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only