Re: How to have a group by expression using index

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

There isn't anything you can do. The most efficient way to do that query is to do a full table scan. Analyzing your table won't help.

It isn't a matter of getting the best execution plan. What do you expect Oracle to do? In order to do the group by, it MUST read every row.

Okay, so I lied a little. There are things you can do, but it doesn't have to do with "fixing" the optimizer.

One thing you can try is to create an index that includes all of the columns in question. That's the group by columns AND the aggegrate columns. If you have an index like that, Oracle will do a full scan on the index instead of the table. If the group by plus aggegrate columns is a small part of the table, you may end up doing fewer I/O's. I don't really recommend doing this, but it is an idea.

Another thing you can do is to make sure you don't have a bunch of wasted space in your table. That is, if you've deleted a bunch of rows, you may have a lot of mostly empty blocks. Oracle will still read these blocks even though they are empty because Oracle doesn't know they are empty until it reads them. There are a few ways to clean up this space, but the easiest is with exp/imp.

Another thing is RAID. You've got to do a lot of I/O. If you have 5 or 10 disks working for you all at the same time, it will run 5 or 10 times faster. Spreading the table out on your own doesn't cut it. It has to be done by RAID hardware (or software). This is by far the best solution. Unfortunately, it involves spending money.

Another idea is parallel query. There are several things you have to do to make this work right, but it is an option. I've never been a big fan of PQ. Received on Thu Jun 06 2002 - 05:33:37 CEST