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

It seems to have been the Bug 34381258

În mie., 1 mai 2024 la 12:14, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

>
> 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