Re: SQL question ¿why using clause 'ALL'?

From: Matthias Wirtz <Matthias.Wirtz_at_epost.de>
Date: Mon, 2 Feb 2004 22:13:29 -0500
Message-ID: <bvo6i0$u9dkf$1@ID-151394.news.uni-berlin.de>

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in news:1075764482.779925_at_yasure...
> schumacker wrote:

> > Look at this two SQL sentences:
> >
> > SQL> SELECT ename, job, sal
> > 2 FROM emp
> > 3 WHERE sal > ALL
> > 4 (SELECT sal
> > 5 FROM emp
> > 6 WHERE job = 'CLERK')
> > 7 ORDER BY sal DESC;
> >
> >
> > SQL> SELECT ename, job, sal
> > 2 FROM emp
> > 3 WHERE sal >
> > 4 (SELECT MAX(sal)
> > 5 FROM emp
> > 6 WHERE job = 'CLERK')
> > 7 ORDER BY sal DESC;

> ... but questions such as "Which is better" almost always lead to
> someone taking the answer as gospel and applying it to cases where it
> isn't applicable.

My post should not indicate that I do not agree with Daniel, but the autotrace results was suprising for me so I post them and maybe they get you started on your further invesigations:

The execution plan is different and this results in a different amount of consistent gets which gives you information about the logical I/O that was neccessary for your request. But as Daniel mentioned to generalize this finding is the hard part. Does it hold true for tables with many rows...

scott_at_DEV> set sutotrace traceonly
scott_at_DEV> SELECT ename, job, sal
  2 FROM emp
  3 WHERE sal > ALL
  4 (SELECT sal
  5 FROM emp
  6 WHERE job = 'CLERK')
  7 ORDER BY sal DESC;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'EMP'
   4    2       TABLE ACCESS (FULL) OF 'EMP'

Statistics

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

scott_at_DEV> SELECT ename, job, sal
  2 FROM emp
  3 WHERE sal >
  4 (SELECT MAX(sal)

  5     FROM emp
  6     WHERE job = 'CLERK')

7 ORDER BY sal DESC;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'EMP'
   4    2       SORT (AGGREGATE)
   5    4         TABLE ACCESS (FULL) OF 'EMP'

Statistics

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

--
Matthias Wirtz  -  Norfolk, USA

Received on Mon Feb 02 2004 - 21:13:29 CST