Re: Adding Indexes drastically slows down App

"E Elliott" <emelliott_99_at_yahoo.com> wrote in message news:9c4485cf.0106210704.2d9bd801_at_posting.google.com...
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
 news:<3b31c3ef_at_news.iprimus.com.au>...
> > Well, it's a hint, not a command. And the optimiser is a law unto
 itself,
> > even on sunny Wednesdays, with a following wind, fingers crossed and
 copious
> > quantities of Irish stout and shamrock juice.
> >
> > I'd love to see the explain plans! You must have some wildly skewed
 data in
> > there for two pathetic indexes to make such a wild difference... and for
 an
> > estimate statistics to be hopeless, too.
> >

<snip>

> This query is producing a report(crystal report!) the displays items
> that have not been reconciled. Item is the biggest table in the
> system

Unless I've misread the plans then the optimiser prefers the index on serial number to the index on transaction date. What does the distribution of serial numbers look like. The optimiser will be assuming that they are evenly spread. I'm also a little suprised to see a serial number column as a varchar2 but not hugely.

anyway my suggestions

Collect histograms on the item table.
EXECUTE DBMS_STATS.GATHER_TABLE_STATS
('<USERID>','ITEM', METHOD_OPT => 'FOR COLUMNS SIZE 10 sal');

Actually before doing that you could issue

select item.serialnumber,count(*)
from item
group by serialnumber;

just to see what the distribution is like if you don't know.

HTH and I'm not barking up the wrong tree again

--
Niall Litchfield
Oracle DBA
Audit Commission UK