Re: What slows down that query?

Hi.

 Your statement probably started using another index ( on COL2 column )  and the index in not good.
 Try:
   Select <col list >
    From a, b, c

    WHERE a.pk = b.fk1
      and c.pk = b.fk2
      and a.col1 = 'Y'
      and substr (a.col2, 1, 3) = 'abc'
      and a.col3 + 0 = 1 /* It will disable another index usage */

 Try another idea ( not connected to your problem):   Use a.col2 LIKE 'abc%' instead of substr (a.col2, 1, 3) = 'abc'.

 HTH. Michael.

In article <8sqphc$5vr$1_at_nnrp1.deja.com>,   Gad Krosner <krosner_at_excite.com> wrote:
> Hi There!
>
> I'm new to Oracle, so please bear with me...
>
> I wrote a query like this:
> Select <col list >
> From a, b, c
> WHERE a.pk = b.fk1
> and c.pk = b.fk2
> and a.col1 = 'Y'
> and substr (a.col2, 1, 3) = 'abc'
>
> The query runs fine - brings results back in a few seconds. Then, I
> added another condition,
> and a.col3 = 1
> This condition brings the server to its knees - I waited more than 3
> minutes for the rows to come back. And it wasn't a fluke - I kept
> commenting this newly added condition, to speed up the query and vice
> versa. col3 is numeric, with three possible values, 1, 2 or 3.
>
> Any explanation for this?
> Any way to debug or analyze the server's interpretation?
>
> Thanks.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 21 2000 - 01:41:37 CDT