Re: SQL statement -- about index and group by

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/04/20
Message-ID: <353b6bff.31103304@www.sigov.si>#1/1

On 20 Apr 1998 11:42:47 GMT, "Chris Hamilton" <toneczar_at_erols.com> wrote:

>°½Ãi¿ß <Grafield_at_grafield.com.tw> wrote ...
>
>> When I issue an Select statement with group by ,
>> is it useful to create a index on the columns in group by function ?
>>
>> example :
>>
>> There is a lot of data in Table Table_AA, I use
>>
>> SELECT COUNT(*), C_1, C_2, C_3, C_4, C_5
>> FROM TABLE_AA
>> GROUP BY C_1, C_2, C_3, C_4, C_5
>>
>> For the performance, do I need to create a index on (C_1, C_2, C_3, C_4,
>> C_5 )
>
>I don't think an index would be beneficial for THIS statement. Since
>you're not restricting the range of values returned, a full table scan is
>needed, which would not use the indexes.

Not quite true. If cost base optimizer is used, then indexes can be used even when there is no WHERE clause. For this particular query, if the avarage combined length of data in columns C_1, C_2, C_3, C_4 and C_5 is significantly smaller then the avarage length of the whole record, then creating a composite index on those columns might have significant impact on the performance of the above query, since oracle will perform INDEX FULL SCAN much quicker than TABLE FULL SCAN.

Again, you could benefit from this kind of index only if you use CBO and have your table and index analyzed. However, this can't be use as a rule of thumb. You should experiment with your data and look at explain plan output.

>Now you need to think about other parts of your application, maybe they
>could benefit from indexes on the above columns. But the statement above
>would not.
>
>Chris
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>Chris Hamilton -- christopher.h.hamilton_at_usace.army.mil
>US Army Corps of Engineers
>http://www.serve.com/cowpb/chamilton.html

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Apr 20 1998 - 00:00:00 CDT