-
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
-
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
-
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
-
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"
-
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
-
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
-
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
-
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
-
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 04: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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|