Re: How to use an index for for grouping by subranges

Do you know whether you are using the COST or RULE based optimizer? If you are using RULE it will automatically ignore the index whenever you put a function round an indexed column....

Check in init.ora optimizer_mode

May be set to CHOOSE - if that's the case, have you got statistics on your table?

--
Anne Crowther

Using the hints may help - at th
Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:939195326.25874.0.nnrp-02.9e984b29_at_news.demon.co.uk...
>
> The usual rules about indices and functions apply,
> for default behaviour.
>
> Have you tried an /*+ index */ hint yet ? They aren't often ignored.
> An alternative (to use the index, but not as an index)
> is to enable the fast index scan function.
>
> The hint is /*+ index_ffs */, but you can enable
> the feature globally with init.ora parameter
> fast_full_scan_enabled = true
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Greg Stark wrote in message
> <87n1tx2glo.fsf_at_HSE-Montreal-ppp19508.qc.sympatico.ca>...
> >
> >
> >Say I have an index on an integer column `n' and I want to do a report
> >grouping successive clumps of n together. There's no reason the index
> couldn't
> >be used to do this but the Oracle optimizer doesn't seem to know this.
> >
> >For example none of the following use the index:
> >
> >select count(*) from foo group by trunc(n/2);
> >select count(*) from foo group by trunc(n,-1);
> >select count(*) from foo group by

decode(n,1,1,2,1,3,1,4,2,5,2,6,2,7,3...)
> >
> >actually I admit I haven't tried all of these but I did experiment a
while
> >back and couldn't get the optimizer to use the index.
> >
> >I would also really like to be able to group by trunc(datefield) for
> example.
> >
> >This is in 8.0.5, I know in 8.1.5 there's functional indices which would
> help,
> >but it would be a tremendous waste of space since I would have to index
> every
> >precision I might want to group by, even though they're all redundant.
It's
> >worse in 8.0.5, right now I'm using redundant columns which have
redundant
> >indexes on them...
> >
> >--
> >greg
>
>
Received on Wed Oct 06 1999 - 09:19:11 CDT