Re: SQL statement -- about index and group by

°½Ãi¿ß wrote:
>
> Jurij Modic ¼¶¼g©ó¤å³¹ <353b6bff.31103304_at_www.sigov.si>...
> >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.
>
> DOES ORACLE 7.3.2 SUPPORT COST BASE OR RULE BASE ?

1. check out the init parameters

optimizer_mode=
v733_plans_enabled= (once you get to 7.3.3 that is)

2) check out "analyze" command (cf: SQL Reference)

3) check out optimizer (cf: Server Concepts manual)

Have fun...

-- 
==========================================
Connor McDonald
BHP Information Technology
Perth, Western Australia
"These views mine not BHP..etc etc"

"The only difference between me and a madman is that I am not mad."