Re: basic question about analytic functions

udo456_at_yahoo.com (Udo Diekmann) wrote in message news:<5757f268.0302201242.39bdbd7_at_posting.google.com>...
> JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0302192205.5e192c08_at_posting.google.com>...
<snip>
> Thanks for the help and translation, the same analytic query provided
> improved performance in my own case (> 1.2M rows) by 25%.
>

Good to know it made a difference. But I have to say this is somewhat of a surprise. For this query the difference with analytic functions is it uses window sort as opposed to regular sort. I really don't see any reason window sorting should be faster since it's a bit more complicated. However, if the access paths are different, e.g. hash join for analytic query and sort merge join for regular query, that will explain the performance improvement.

> I wonder if anyone has any knowledge of caching and analytic queries.
> It does seem that the amount of time to re-execute the analytic query
> is quite fixed (no noticable subsequent improvement), whereas repeated
> standard queries seem to show evidence of caching (substantial
> improvement).
>

As far as I know, caching doesn't depend on what kind of query you run...

> Cheers,
>
> Udo.
Received on Fri Feb 21 2003 - 00:41:17 CST