Memory Sort Puzzle

From: <artmt_at_hotmail.com>
Date: 29 Jul 2005 11:06:11 -0700
Message-ID: <1122660371.366906.241970@f14g2000cwb.googlegroups.com>

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

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)

   2    1     VIEW
   3    2       UNION-ALL
   4    3         TABLE ACCESS (FULL) OF 'DUAL'
   5    3         TABLE ACCESS (FULL) OF 'DUAL'

The statistics however show "0 sorts".

Statistics

---

          0  recursive calls
          0  db block gets
          6  consistent gets
          1  physical reads
          0  redo size
        382  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

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

select * from(
select
c1,
row_number() over(order by c1 desc) rnk
from(select 1 c1 from dual

     union all
     select 2 c1 from dual)

)
where rnk=1;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 VIEW

   2    1     WINDOW (SORT PUSHED RANK)
   3    2       VIEW
   4    3         UNION-ALL
   5    4           TABLE ACCESS (FULL) OF 'DUAL'
   6    4           TABLE ACCESS (FULL) OF 'DUAL'

Statistics

---

          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        435  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

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

Thanks
-Art Received on Fri Jul 29 2005 - 13:06:11 CDT