Re: What slows down that query?

Generally speaking, generating an explain plan for both queries will allow you to see how Oracle is accessing the data. If using SQLPlus type set autotrace on or refer to the documentation for the syntax of how to generate and read an explain plan without executing the statement.

I don't think I can explain what is happening with the query without that information as well as knowing what indexes you do/don't have, what optimizer you are using etc. Obviously the last addition is changing the plan or order of execution.

--
Dave A


"Gad Krosner" <krosner_at_excite.com> wrote in message
news:8sqphc$5vr$1_at_nnrp1.deja.com...


> 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.