Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: From 1second to 2 minutes in a single column...
As Sybrand says the instrument table appears to be irrelevant in your query.
You might also wish to consider creating an index on (PartyIndex,Lastname) for the PartyNames table instead of the two seperate indices.
-- Niall Litchfield Oracle DBA Audit Commission UK "tore" <TBostrup_at_teamia.com> wrote in message news:0DIP6.39$yH.2438_at_client...Received on Sun May 27 2001 - 07:41:15 CDT
> 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.
>
>