Re: Sort/Merge problem

Lothar Armbrüster wrote:
> "Ben" <balvey_at_comcast.net> writes:
>
> > Oracle Ent Ed. 9.2.0.5, AIX5L
> >
> > came in this morning to 3 ora-1652 errors.
> >
> > I'm checking to see what is using all my temp space and there are 5
> > processes running the same select statement that each have almost 1 Gig
> > of TEMP space.
> >
> > When I look at the Long Operation of those processes it is a
> > Sort/Merge, and the explain plan shows a select that has an indexed
> > column in the predicate and is ordering by four other columns that make
> > up the primary key.
> >
> > Forgive my ignorance but why does this cause a Sort/Merge? Is it the
> > fact that it is binding by one index and sorting by a different index?
> >
> [...]
>
> Hello Ben,
>
> ordering by varchar2 columns does not use any index unless nls_sort
> is set to binary.
> Normally, Oracle has locale specific sort orders that are different
> from the binary order of the index.
>
> Hope that helps,
> Lothar
>
> --
> Lothar Armbrüster | lothar.armbruester_at_t-online.de
> Hauptstr. 26 |
> 65346 Eltville |

Hello Lothar,

Thanks for the info. Our nls_sort parameter is not set to binary. Although the primary key index that is being used in the order by clause has four fields that are Number, Char, Char, Number, in that order. So I don't think this would be the reason on this case.

Any other ideas?

Thanks Received on Fri Sep 29 2006 - 07:09:34 CDT