Re: Doing large sort in RAM - sort workarea manipulation

From: Jonathan Lewis <>
Date: Sat, 12 Nov 2011 17:46:02 -0000
Message-ID: <1E0DD8F4249648A5B1579192E81B02B5_at_Primary>


Jonathan Lewis

  • Original Message ----- From: "Grzegorz Goryszewski" <> To: <> Cc: <> Sent: Saturday, November 12, 2011 2:03 PM Subject: Re: Doing large sort in RAM - sort workarea manipulation

> On 2011-11-12 13:05, Jonathan Lewis wrote:
>>Since you mention Informatica, you need to consider the full life-cycle of WHY
>>you are sorting. I have found cases in the past where the best strategy for
>>completing an Informatica job is to use a "NOSORT" type of option because most
>>of the time spent turned out to be from moving the results out to Informatica
>>and then back into the database. In such cases, if you sort in the database,
>>total run time increases by the time it takes to complete the sort; whereas if
>>you have a no-sort operation it's slower, but not the limiting factor in
>>the data out to Informatica.
> Thanks for sharing that.
> I just want to confirm I've got Your point about NOSORT approach.
> So by 'NOSORT" type of option' You are referring to index access which
> produces rowsource in sorted order or NOSORT means forget about order by ? :)
> If I stick with sort elimination by CBO (nosort operation) I can imagine there
> are hints needed , not sure CBO will use index with large output , so what
> kind of hints
> should be used to force NOSORT sort execution ?
> Regards
> GregG
> -------------------------------------
> Sprawdz za darmo zdolnosc kredytowa!
> -----
> No virus found in this message.
> Checked by AVG -
> Version: 2012.0.1869 / Virus Database: 2092/4611 - Release Date: 11/11/11

Received on Sat Nov 12 2011 - 11:46:02 CST

Original text of this message