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