Re: Memory Sort Puzzle

artmt_at_hotmail.com wrote:
> Consider this simple query:
>
> select max(c1) from
> (select 1 c1 from dual
> union all
> select 2 c1 from dual);
>
> When I auto trace it the execution plan shows a sort (as expected).
>

...
>
> The statistics however show "0 sorts".

Oracle decided the sort-like thing in the explain plan didn't count as a real sort.

>
> When I re-write the statement using an analytical function instead I am
> getting "1 sorts (memory)"

And here it decided that a different sort-like thing did count as a real sort (although it may or may not be as computationally intensive as a plain-Jane sort would be).

Personally, I wouldn't read too much into this. I'd create real test cases, and then analyze them by performance and concrete numbers, not by whether this particular version of Oracle feels like counting some sort-like-thingy (done on only two rows) as really being a sort.

> The reason I am looking into this is because I am considering
> re-writing queries that look like this:
>
> SELECT *
> FROM vlsup A
> WHERE id_Case = '000000275'
> AND seq_order = 1
> AND seq_obligation = 1
> AND seq_event_global = ( SELECT MAX(B.seq_event_global)
> FROM vlsup B
> WHERE B.id_case = A.id_case
> AND B.seq_order = A.seq_order
> AND B.seq_obligation = A.seq_obligation);
>
> and replacing self-join with analytical function.
> Auto trace stats show significant reduction in I/O, but additional
> memory sort.
>
> Thoughts?

How much time is being spent on those memory sorts? Is overall performance going up or down?

I think your examples on dual are pretty much useless in making decisions for the real thing. (Although they were useful for making a simple explain plan analogous to your real query, so if that is all you meant them to do, great.)

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB