Re: What slows down that query?

Thanks. Adding that zero did the trick, although I'm puzzled.

In article <8srdr1$jp1$1_at_nnrp1.deja.com>,   Michael Bialik <michael_bialik_at_my-deja.com> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 23 2000 - 08:21:10 CDT