Help with query performance

Hi,

I need some help improving performance in the following query:

Select Sum(HITS), ACCOUNTNO
from buckets
where element = 'CP'
and DATED >= '1-Jan-2000'
and DATED <= '30-Jun-2002'
group by ACCOUNTNO
order by 1 Desc

The BUCKETS table has indexes on DATED, ELEMENT and ACCOUNTO and has about 5,000,000 records and growing, and the total records where element = 'CP' are about 3,500,000 records.

When execute this query it takes about 3 minutes to return data.

These are the optimizer results:

SELECT STATEMENT Optimizer=CHOOSE (Cost=3211 Card=206551 Bytes=8675142)   SORT (ORDER BY) (Cost=3211 Card=206551 Bytes=8675142)     SORT (GROUP BY) (Cost=3211 Card=206551 Bytes=8675142)       TABLE ACCESS (FULL) OF BUCKETS (Cost=1197 Card=279328 Bytes=11731776)

Is this normal, to take that long?
How can I improve it?
How can I avoid a table full scan?

Any help will be appreciated.

Stefanos Received on Mon Aug 05 2002 - 21:40:25 CEST