How to avoid full tablescan with group function?
Hi,
When I access my table normaly,without group function it does index range scan like this. as there is index on pharmacyid column
SELECT drugauctionid, amount
FROM drugbid Y
WHERE pharmacyid = 142
but when i use group function it does a full tablescan like this..
SELECT drugauctionid, amount
FROM drugbid Y
WHERE pharmacyid = 142
GROUP BY drugauctionid,amount
the execution plan is as follow when it does a full tablescan
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=8 Card=75 Bytes=
750)
1 0 SORT (GROUP BY) (Cost=8 Card=75 Bytes=750)
2 1 TABLE ACCESS (FULL) OF 'DRUGBID' (Cost=4 Card=75 Bytes=7
50)
the value of optimizer_* parameters as as follow.
ALTER session SET OPTIMIZER_INDEX_CACHING=100
ALTER session SET OPTIMIZER_INDEX_COST_ADJ=30
Optimizer mode is first_rows
version is 9.2.0.6
How can I use index range scan even with group function?