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