Explain plan would be helpful.
Ben wrote:
> 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 Sat Sep 30 2006 - 09:28:27 CDT
