How to use an index for for grouping by subranges

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 - 00:39:03 CDT