Re: Strage behavior with "select max(), constant" in 7.3.4

My eyes must be getting poor in my old age. I am missing the difference in the 2 execution plans.

If you accidentally included the execution plan 2 x and forgot to change it in the posting and if the select 1, max(.. is showing a full table scan then maybe the difference is because the optimizer in 7.3.4 thinks that the column 1 is not in the index so it doesn't use it. Even though there really is no need to not use the index for the case select 1, max(b)...

Of course, if you upgraded to ... :-)

Jim
"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:11:47 CST