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

Actually, I can't seem to get Oracle to respect any hints about using indexes on this table, it just does a full scan. I have analyzed the table and indices and I am using the cost based optimizer (actually I think it's "choose"). The only way I can get it to use the index is by restricting the query with a where clause on the indexed colum.

Even:
  explain plan for select /*+ index(idx_tbl_datefld) */ * from tbl order by datefld; shows a full scan!

Basically I'm looking for a hint to force a GROUP BY NOSORT when I know the index is in the right order but the optimizer doesn't:

> GROUP BY NOSORT Sorting can be avoided when performing a GROUP BY operation
> when you know that the input data is already ordered so that all rows in each
> group are clumped together. This may be the case, for example, if the rows are
> being retrieved from an index that matches the grouped columns, or if a
> sort-merge join produces the rows in the right order. ORDER BY sorts can be
> avoided in the same circumstances. When no sort takes place, the EXPLAIN PLAN
> output indicates GROUP BY NOSORT.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:

> 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
>
>

--
greg Received on Wed Oct 06 1999 - 14:13:25 CDT