Re: Help with query performance

3 minutes? I would be happy with it if I get that kind of number on my home PC. What's your hardware? There is only one table in this query, should be easy to tune.
1. Accessing buckets table. Is the table analyzed? How many rows are returned? If a large percentage of the rows in the table are returned, a FTS may be more appropriate than index scan. 2. The sorting. Check your sort_area_size and temp tablespace configuration. Search this news group on this FAQ.

• K.M.

tses_at_hotmail.com (Stefanos) wrote in message news:<5883c7c4.0208051140.462e2ede_at_posting.google.com>...
> 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 Tue Aug 06 2002 - 07:29:37 CEST