Re: Why does using two indexes slow down my query?

Or, you could create a new Index on both columns. This takes up some space, but it will drastically improve your performance.

MR.

Mark Powell wrote:
>
> Re-analyze the table using a sample size. You implied that you used the
> default estimate which generates terrible results for larger tables. Try
> 'analyze table my_table estimate statistics sample 50000 rows;' and the
> re-try your query. Alternately you could just re-analyze the indexes doing
> computes on them and check for any effect on your query performance.
>
> patrickk_at_mindspring.com wrote in article
> <6ljb91$e3t$1_at_nnrp1.dejanews.com>...
> > I'm having a problem with Oracle that I have not seen in other DBMSs.
> I'm no
> >
> Oracle expert, so I am hoping there is a simple solution...
>
> I have a
> table
> > of about 5 million rows. Each record is relatively wide (300 bytes
> or
> so).
> > I am using Oracle 7.3 for NT.
>
> When I do the following query:
>
> SELECT
>
> > count(*)
> FROM
> my_table
> WHERE
> indexed_colum1 = '101'
>
> I get a quick
> > "9,000"
>
> Or when I do
> SELECT
> count(*)
> FROM
> my_table
> WHERE
>
> > indexed_colum2 = 'C'
>
> I get a quick "80,000"
>
> But God help me if I put
> BOTH
> > indexed columns in the query such as this:
> SELECT
> count(*)
> FROM
>
> > my_table
> WHERE
> indexed_colum1 = '101
> AND
> indexed_colum2 = 'C'
>
> This
> > takes upwards of 15 minutes. My past expereince with DBMSs (Informix,
> > RedBrick, SQLServer) has been that the more narrow I make my constraints,
>
> > the faster the query returns values...
>
> I have estimated statistics using
> > analyze.
> I have tried all the optimizer goals.
>
> Any help is greatly
> > appreciated.
>
> --Patrick
> >
> > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > http://www.dejanews.com/ Now offering spam-free web-based newsreading
> >
Received on Tue Jun 09 1998 - 14:59:22 CDT