Re: From 1second to 2 minutes in a single column...

A copy of this was sent to "tore" <TBostrup_at_teamia.com> (if that email address didn't require changing) On Sat, 26 May 2001 03:07:32 -0400, you wrote:

>I have a very simple query:
>
>SELECT P1.InstrumentNumber,
> P1.FixedLastName
>FROM
> Instruments I,
> PartyNames P1
>WHERE I.InstrumentNumber = P1.InstrumentNumber
> AND P1.PartyIndex = 2
> AND P1.LastName >= 'A'
> AND P1.LastName <= 'ACzzzzzzzzzzz'
>
>InstrumentNumber is the primary key of Instruments. Instruments has just
>over 600,000 entries, while PartyNames has over 1.8 million entries. I have
>indexes for all the columns referenced in this query.
>
>This query executes in 1 second and returns less than 30 rows.
>
>If I change the select list to
>
>SELECT Count(*)
>
>the time explodes to 2 minutes and 17 seconds!
>
>If I add a column from Instrument to the select list (also indexed), I get a
>similar jump in execution time.
>
>How on earth is Oracle spending 2 minutes in order to obtain information
>about 30 rows???????
>
>Help !!
>Tore.
>

Check out the query plans to see whats up. Use autotrace in sqlplus (see http://osi.oracle.com/~tkyte/article1/autotrace.html if you don't know of that). Post the results and perhaps we can diagnose whats going on.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp