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

From: Geraint Williams <geraint.williams_at_eudoramail.com>
Date: 30 Nov 2001 11:49:54 -0800
Message-ID: <1fd41978.0111301149.74ffef75@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 - 13:49:54 CST