Re: Doing large sort in RAM - sort workarea manipulation
Date: Sat, 12 Nov 2011 17:46:02 -0000
- 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,
>>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 ?
> Sprawdz za darmo zdolnosc kredytowa!
> 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