How to avoid full tablescan with group function?

[rgopani]

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?

[slimdave]

The difference between the two queries here is that Oracle knows that it cannot return any rows of the GROUP BY query until it has identified all of the required rows -- normally the GROUP BY would be accompanied by an aggregate function such as SUM(), but just beacuse it is not there doesn't mean that Oracle won't perform the required sorting anyway. Oracle reads the table with a ful table scan because it believes that to be the fastest way of identifying all of the required rows.

In the first query Oracle can return rows as soon as they are identified as part of the result set, and it might find the first row quicker using an index.

So this all comes about because the optimizer mode is FIRST_ROWS.

I also note that in 9.2.0.6 you ought to investigate the use of the DBMS_STATS.GATHER_SYSTEM_STATS procedure to collect statistics on multiblock and single block read times, to aid the optimizer in deciding whether index or table access would be faster.