Printable View
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:)
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
Check out the planQuote:
Originally posted by engiri
If I use min and max functions it takes 5 minutes..at least.
That column is indexed.(B-tree)..
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
Jus put a hint.. Index_FFS(Table_Name)Quote:
Originally posted by engiri
or if we want them combined use hrishy's advice
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
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?
David :Quote:
Originally posted by DaPi
If it is not unique, the hint is ignored and an FTS is done
Jus see below..I have not even given any hint and Oracle uses index..
RgdsCode: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
Abhay
Hrishy :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
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.