Re: Help with query performance
Date: 6 Aug 2002 09:23:33 -0700
Message-ID: <5883c7c4.0208060823.57225d01_at_posting.google.com>
Jim,
Thanks for the help.
I have 3 indexes one for each field.
I created the index you told me but the query still is not using any index, even if I force it with hints.
Yes I analyzed the table and run the statistics.
Stefanos
"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<UUG39.3749$7n5.2145_at_sccrnsc01>...
> 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 - 18:23:33 CEST