IMHO the second example spends extra time to perform sorting to detect max
value.
AFAIK INDEX FULL SCAN returns rows ordered by the INDEX key and following
sorting less time than sorting after INDEX FFS, when records are returned
without ordering.
"Geraint Williams" <geraint.williams_at_eudoramail.com> wrote in message
news:1fd41978.0111301149.74ffef75_at_posting.google.com...
> Can anyone explain the difference I'm experiencing when running the
> following two logically similar sql statements under Oracle 7.3.4.
>
> 1) select max(a) from b;
>
> 2) select 1,max(a) from b;
>
> KEY001B is unique index on b.a;
>
>
> SQL> r
> 1 SELECT max(a)
> 2* FROM b
> Elapsed: 00:00:05.97
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10581 Card=21640290
> Bytes=281323770)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FULL SCAN) OF 'KEY001B' (UNIQUE) (Cost=10581 Card=21640290
> Bytes=281323770)
>
> SQL> r
> 1 SELECT 1,max(a)
> 2* FROM b
> Elapsed: 00:03:239.15
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10581 Card=21640290
> Bytes=281323770)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'KEY001B' (UNIQUE) (Cost=10581
> Card=21640290 Bytes=281323770)
>
> I can't understand why they would produce different execution plans.
>
> Server version is 7.3.4.0.0 on AIX.
>
> (Please no upgrade to Oracle 8/9 "advice")..
>
> Many thanks,
>
> Geraint.
Received on Fri Nov 30 2001 - 14:32:01 CST
