min and max functions from 10 million records

I found for min(),max() that:
If the column has a unique index, it is used without needing a hint.
If it is not unique, the hint is ignored and an FTS is done (though I don't rule out getting the hint wrong ;))

In both cases an index scan is done when only one of min/max is in the select clause.

Can anyone explain?

Quote:

---

Originally posted by DaPi
If it is not unique, the hint is ignored and an FTS is done

---

David :

Jus see below..I have not even given any hint and Oracle uses index..

Code:

---

WW04_PRO_DBA> select INDEX_NAME, UNIQUENESS from dba_indexes where index_name = 'AK1_SHIPMENT_LINE';

INDEX_NAME          UNIQUENES
-------------------- ---------
AK1_SHIPMENT_LINE    NONUNIQUE

Elapsed: 00:00:00.07
WW04_PRO_DBA> select INDEX_NAME, Column_Name from dba_ind_columns where index_name = 'AK1_SHIPMENT_LINE';

INDEX_NAME          COLUMN_NAME
-------------------- ------------------------------
AK1_SHIPMENT_LINE    SHIPPING_DATE

Elapsed: 00:00:00.08
WW04_PRO_DBA> Set Autotrace on
WW04_PRO_DBA> select min(SHIPPING_DATE), max(SHIPPING_DATE) from SHIPMENT_LINE;

MIN(SHIPP MAX(SHIPP
--------- ---------
01-NOV-03 27-FEB-04

Elapsed: 00:00:14.06

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=841 Card=1 Bytes=7)
  1    0  SORT (AGGREGATE)
  2    1    INDEX (FAST FULL SCAN) OF 'AK1_SHIPMENT_LINE' (NON-UNIQUE) (Cost=841 Card=3518186 Bytes=24627302)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13828  consistent gets
      13825  physical reads
          0  redo size
        468  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

---

Rgds
Abhay

Quote:

---

Originally posted by hrishy
hi All

I was just wundering why oracle is not using the index in this case.

select min(X),max(x) from t1

I think it should use it..

regards
Hrishy

---

Hrishy :

It will surely use index provided

* Tab is analyzed
* Index is analyzed
* Col Stats are taken

And not to forget the init params Optimizer_Index_Cost_Adj, Optimizer_Index_caching also plays a quite significant role..

Dapi :

I think this explains? :)

Rgds
Abhay.