Oracle Database Discussions

Hi,

I have two databases, one with 2 million rows and another with 171 million rows. On both boxes when I do a max() it takes milliseconds for the result to return. There is an index on this column.

But when I do a min, on the DB with 2 million its takes about 2-3 minutes and min on the other DB with 171 M it takes only a few milliseconds.

The execution plan seems to be fine, the cost in 1 in both. The only difference is that the one taking most time has large number of physical reads. Could anyone let me know what's happening. Both the DB boxes are exactly the same in terms of configuration.
Elapsed: 00:03:25.38

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'ST_AI' (NON-UN
IQUE) (Cost=1 Card=2126583 Bytes=17012664)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
341787 consistent gets
341749 physical reads
0 redo size
382 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Elapsed: 00:00:00.23
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'ST_AI' (NON-UN
IQUE) (Cost=1 Card=171363076 Bytes=1370904608)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
383 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed