Re: help: optimizer can't use index on Y to speed up select .. sum(X) ... group by Y

In article <809371785.22893_at_jimsmith.demon.co.uk>, jim_at_jimsmith.demon.co.uk says...
>
>rek_at_cas-ps.com (Reinhard Kuhn) wrote:
>
>>Using Oracle 7.1.3.3.6 (with rule based optimizing) on WindowsNT 3.5
>>I run in the following problem (simplified):
 

>>I have a table created by
>> CREATE TABLE T1 ( KEY CHAR(1) NOT NULL, VALUE NUMBER NOT NULL );
>>indexed on the key-column with
>> CREATE INDEX T1_01 ON T1 ( KEY )
 
>>Analyzing the query
>> SELECT KEY,SUM(VALUE) FROM T1 GROUP BY KEY;
>>i get the execution plan
>> SORT GROUP BY
>> TABLE ACCESS FULL T1
 
>>Since T1 is indexed on KEY, I'd expect the optimizer to avoid the
>> 'SORT GROUP BY', but obviously it can't take advantage of the index.
>>

 ...
>
>I also suspect that Oracle can't count while it is fetching. That is
>it can't "fetch a value - add it to the current total - get the next
>key - check of its different - reset the total ...." while it is
>retrieving the initial data . It needs to fetch all the data, sort it
>and then run an old-fashioned procedural break-report program on it.
>Jim Smith jim_at_jimsmith.demon.co.uk
>Oracle DBA
>

This is exactly the problem I'm dealing with.

Fetching all the data (using full table scan), sorting it (using LOTS of temp-space), and then break-report it is really a very clumsy behaviour. After all each row has to be accessed at least three times not to speak of the disk bottleneck caused by writing and reading back the temporary segments, the overhead of rollback and redolog (since the temporary segments are written to rollback and redo too ) and the computational overhead of (unnecessary) sorting.

Since the task ( counting/aggregating GROUP BY a key ) seems to be quite common and since AFAIK at least one RDBMS ( Watcom ) is capable of using an index on KEY to speed up this task, I'd like to know if Oracle has implemented this feature too (or has any plans to do so in the near future...) - after all it can't be too hard to implement and it would be VERY nice to have. (especially in Data Warehousing)

Thanks

-- 
    _/_/_/   _/_/_/ _/    _/  // Reinhard Kuhn             /  It can be      
   _/    _/ _/     _/  _/    //         (kuhn_at_cas-ps.com) /  done quickly,   
  _/_/_/   _/_/_/ _/_/      // CAS GmbH                  /  cheaply or well  
 _/  _/   _/     _/  _/    // Lemberger Strasse 14      /   - pick any two!  
_/   _/  _/_/_/ _/    _/  // 66955 Pirmasens, Germany  /