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

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.
>
 

>Investigating further I found that
> SELECT MIN(KEY) FROM T1
> is planned
> SORT AGGREGATE
> INDEX RANGE SCAN T1_01
>whereas
> SELECT MIN(KEY),MIN(KEY) FROM T1
> is planned
> SORT AGGREGATE
> TABLE ACCESS FULL T1
>and even
> SELECT KEY,COUNT(*) FROM T1 GROUP BY KEY
> is planned
> SORT GROUP BY
> TABLE ACCESS FULL T1

>The execution of the (more complex) original statement on a big table
>requires enormous amounts of temp-space for sorting, writes lots of
>redo-log and is very slow.
>Of course I could do the task in PL/SQL with a cursor 'select key,value from
>t1 order by key'
>computing the sum of values per key 'by hand', but since this kind of problem
>doesn't sound
>uncommon I wonder, if there are other solutions.
 

>So I have a few questions:
 

> - am I missing something
> - has anybody encountered/solved a similar problem
> - is this behaviour a (known) bug/missing feature of the ruleOptimizer
> - would it help to use the costOptimizer instead (with/without hints)
> - will the behaviour change in Oracle >= 7.2
 

>Any experiences/help/hints/suggestions are appreciated
 

>Thanks in advance
 

>--
> _/_/_/ _/_/_/ _/ _/ // 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 /
>

The optimiser probably isn't using the index because you don't have a where clause and it has determined that you will be fetching all the data. If it used the index this would mean a full index scan, plus a rowid fetch from the table for every row, This is usually undesirable.

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