Re: sql plus max function query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 01 Feb 1999 15:28:12 GMT
Message-ID: <36b6c71c.8223885@192.86.155.100>

A copy of this was sent to Mike Burden <michael.burden_at_capgemini.co.uk> (if that email address didn't require changing) On Mon, 01 Feb 1999 14:11:06 +0000, you wrote:

>Hmmmm... never thought of that but is it efficient? Will it peel all the records
>sort and take the highest value not realizing that there can only ever by one set.
>Technically aggregate functions on the entire table (i.e. no group by clause) don't
>need a sort step (I think).
>

No, an aggregate on a table needs a sort step (sort(aggregate))

Consider:

SQL> set autotrace on explain
SQL> 
SQL> select max(created) from dates where created = '01-JAN-01'

2 /

MAX(CREAT

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DATES' SQL> select max(created) from dates where created = '01-JAN-01' group by 1 2 /
no rows selected

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (GROUP BY)
   2 1 TABLE ACCESS (FULL) OF 'DATES' SQL> select *
  2 from ( select max(created) created from dates where created = '01-JAN-01' )   3 where created is not null
  4 /

no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 VIEW

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

so, the first 2 plans are very similar and perform comparably (a sort aggregate is usually less expensive then a sort group by but since we have but 1 grouping item, the overhead is minimal). The last plan is the 'most complex' but the filter and view steps are done against 1 row so they are fast as well....

even with an index, it would be pretty much the same (add an index and rerun the above, the plans are all very similar again).

>Thomas Kyte wrote:
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Feb 01 1999 - 09:28:12 CST