Re: SQL statement -- about index and group by

I use the plan table to test the statement count(*) and count(PK) with group by ,
then ORACLE7.3.2 always do a full scan table .

ex : Table yu01 have a PK -- class

(1) select count(*), class from yu01

    group by class;

Plan Table :

Operation                      Operations                     Object_name
------------------------------ ------------------------------ --------------
----------------
  SELECT STATEMENT Cost =      SELECT STATEMENT
    SORT                       SORT
      TABLE ACCESS             TABLE ACCESS                   YU01

(2) select count(class), class from yu01

    group by class;

Operation                      Operations                     Object_name
------------------------------ ------------------------------ --------------
----------------
  SELECT STATEMENT Cost =      SELECT STATEMENT
    SORT                       SORT
      TABLE ACCESS             TABLE ACCESS                   YU01

Matthias Gresz ¼¶¼g©ó¤å³¹ <6hf9lp$mo$1_at_news00.btx.dtag.de>...
>Hi,
>be aware that with COUNT(*) oracle always will do a full scan. Use the
>primary key instead COUNT(PK).
>
>
>°½Ãi¿ß schrieb:
>>
>> 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 ), Thank you !
Received on Mon Apr 27 1998 - 00:00:00 CDT