Re: Doing large sort in RAM - sort workarea manipulation

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


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

  • Original Message ----- From: "Grzegorz Goryszewski" <grzegorzof_at_interia.pl> To: <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> 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,
>>the
>>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
>>getting
>>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!
> http://linkint.pl/f2a77
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1869 / Virus Database: 2092/4611 - Release Date: 11/11/11
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Nov 12 2011 - 11:46:02 CST

Original text of this message