Re: high temp space usage for same analytic function on latest version

I was a little puzzled that this was a reply to a reference I'd made to a very old blog note, but then I realised it was address to Yudhi S.

_at_yudhi s <learnerdatabase99_at_gmail.com> I'd be interested to hear if the issue has been identified and eliminated and, if so, how.

Regards
Jonathan Lewis

On Mon, 29 Apr 2024 at 11:02, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Hello,
>
> Did you find the root cause of this issue after all?
>
> Thanks.
>
> În vin., 18 nov. 2022 la 11:41, Jonathan Lewis <jlewisoracle_at_gmail.com> a
> scris:
>
>>
>> Here's the link to the article in case you do want to read it:
>> https://jonathanlewis.wordpress.com/2009/09/07/analytic-agony/
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Fri, 18 Nov 2022 at 09:38, Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>> By the way, rewriting the code is the correct option; you don't really
>>> want to sort 10 billion rows down to 100 million if you can get rid of most
>>> of those rows first; and if you can't reduce the number of rows perhaps you
>>> can rearrange the joins to reduce the length of the rows and pick up the
>>> rest of the required columns later.
>>>
>>> Here's a quote from a very old blog note I wrote about a bug (fixed by
>>> 11.2.0.4):
>>>
>>> " Since then I’ve always warned people to be a little careful about how
>>> they use analytic functions because of the amount of sorting they can
>>> introduce. My suggestion has always been to crunch *“large”* volumes of
>>> data down to *“small”* volumes of data before applying any analytic
>>> functions to add *“intelligence”* to the intermediate result."
>>>
>>> The article might still be worth reading because it talks about how I
>>> investigated what the analytic sort was doing
>>>
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l