min and max functions from 10 million records
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: min and max functions from 10 million records

  1. #1
    Join Date
    Apr 2003
    Posts
    353

    min and max functions from 10 million records

    In a Table with 10 million records,
    I want to find min and max of a column,
    that column will hardly be 20 distinct values,

    What is the best efficient way to find
    min and max values of that column other than
    using min and max functions.

    If I use min and max functions it takes 5 minutes..at least.
    That column is indexed.(B-tree)..

    Thanks

  2. #2
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Bitmap index could help you if the distinct values are very few
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I have a one million row table.Here is what the results are

    SQL> select max(x) from t1;

    MAX(X)
    ----------
    10000000

    Elapsed: 00:00:09.08

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1463 Card=1 Bytes=9)
    1 0 SORT (AGGREGATE)
    2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'T1_IDX' (NON-UNIQUE) (Co
    st=1463 Card=10000000 Bytes=90000000)





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

    I have a modest server with 2Gb Ram and 2 CPU 440Mhz machine.

    regards
    Hrishy

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: min and max functions from 10 million records

    Originally posted by engiri
    If I use min and max functions it takes 5 minutes..at least.
    That column is indexed.(B-tree)..
    Check out the plan
    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"

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I tried this surprsingly index is not being used.I have analyzed my tables..
    1* select min(X),max(x) from t1
    SQL> /

    MIN(X) MAX(X)
    ---------- ----------
    1 10000000

    Elapsed: 00:00:26.06

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1463 Card=1 Bytes=9)
    1 0 SORT (AGGREGATE)
    2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=1463 Card=10000000 Byt
    es=90000000)





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

    Just wundering if somebody can explain the weird behvaiour.

    regards
    Hrishy

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    And if rewrite the quey using inline views the indexes are being used

    1 select X.x,Y.y
    2 from (
    3 select min(X) x from t1
    4 )X,
    5 (select max(x) Y from t1
    6* )Y
    7
    QL> /

    X Y
    --------- ----------
    1 10000000


    xecution Plan
    ---------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2926 Card=1 Bytes=26
    )

    1 0 MERGE JOIN (CARTESIAN) (Cost=2926 Card=1 Bytes=26)
    2 1 VIEW (Cost=1463 Card=1 Bytes=13)
    3 2 SORT (AGGREGATE)
    4 3 INDEX (FULL SCAN (MIN/MAX)) OF 'T1_IDX' (NON-UNIQUE)
    (Cost=1463 Card=10000000 Bytes=90000000)

    5 1 FIRST ROW
    6 5 VIEW
    7 6 SORT (AGGREGATE)
    8 7 INDEX (FULL SCAN (MIN/MAX)) OF 'T1_IDX' (NON-UNIQU
    E) (Cost=1463 Card=10000000 Bytes=90000000)





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

    regards
    Hrishy

  7. #7
    Join Date
    Apr 2003
    Posts
    353
    eXCELLENT .. IT FINISHED IN 16 MILLI SECONDS
    select X.x,Y.y
    from (
    select min(YEAR_MON) x from STOKCUMM
    )X,
    (select max(YEAR_MON) Y from STOKCUMM
    * )Y
    > /

    X Y
    ------ ---------
    200001 200403

    real: 16

    THANKS

  8. #8
    Join Date
    Apr 2003
    Posts
    353
    This is very useful advice for me from Hrishy

    And check my previous query
    SQL> SELECT MAX(YEAR_MON),MIN(YEAR_MON) FROM STOKCUMM;

    MAX(YEAR_MON) MIN(YEAR_MON)
    ------------- -------------
    200403 200001
    real: 87265
    The above is not using the index.

    do this separately ,, it uses the index.

    SQL> SELECT MIN(YEAR_MON) FROM STOKCUMM;

    MIN(YEAR_MON)
    -------------
    200001
    real: 47
    SQL> SELECT MAX(YEAR_MON ) FROM STOKCUMM;

    MAX(YEAR_MON)
    -------------
    200403
    real: 31

    or if we want them combined use hrishy's advice

    select X.x,Y.y
    from (
    select min(YEAR_MON) x from STOKCUMM
    )X,
    (select max(YEAR_MON) Y from STOKCUMM
    * )Y
    > /

    X Y
    ------ ---------
    200001 200403

    real: 16

    Thanks

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by engiri
    or if we want them combined use hrishy's advice
    Jus put a hint.. Index_FFS(Table_Name)
    Last edited by abhaysk; 02-27-2004 at 03:22 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"

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    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

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