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

From: tore <TBostrup_at_teamia.com>
Date: Sat, 26 May 2001 03:07:32 -0400
Message-ID: <0DIP6.39$yH.2438@client>

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. Received on Sat May 26 2001 - 02:07:32 CDT