DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: min and max functions from 10 million records

  1. #11
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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?

  2. #12
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #13
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    Last edited by abhaysk; 02-27-2004 at 06:51 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width