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

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Sun, 27 May 2001 13:41:15 +0100
Message-ID: <3b10f539$0$15023$cc9e4d1f@news.dial.pipex.com>

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


> 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 Sun May 27 2001 - 07:41:15 CDT