Re: Help with query performance

1. use to_date and don't rely on a date format. so to_date('1-Jan-2000','d-mmm-yyyy') instead of '1-Jan-2000'
2. Do you have 1 index or 3 indexes. Try 1 eg -> create index my on buckets(accountno,element,dated) compress;
3. Did you analyze the index? Jim

"Stefanos" <tses_at_hotmail.com> 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 - 04:55:48 CEST