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

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 - 02:22:11 CDT