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

Was this just a typo in copying your example: the index hint should read:

    index (table_alias index_name)
e.g.

    select /*+ index(tbl idx_tbl_datefld) */ * from tbl order by datefld;

The conditions for getting a plan of

    group by nosort
are very strict (more so in some versions of Oracle than others)

I think you have to do a group by a column list that is exactly the same as the leading column of an index, in exactly the right order. I know that I also had problems in one version of Oracle where the path would only be taken if there was a (notionally) redundant WHERE clause of the form

    where leading_column > {very small value}

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Greg Stark wrote in message
<87emf8z4j0.fsf_at_HSE-Montreal-ppp19508.qc.sympatico.ca>...
>
>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:
>
Received on Wed Oct 06 1999 - 15:10:26 CDT