Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Sort/Merge problem

Re: Sort/Merge problem

From: Lothar Armbrüster <lothar.armbruester_at_t-online.de>
Date: Thu, 28 Sep 2006 18:50:33 +0200
Message-ID: <87zmck3pgm.fsf@prometeus.none.local>


"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     |
Received on Thu Sep 28 2006 - 11:50:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US