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

Thanks,

I did have that index as well (in addition to the indivdual indexes).

Tore.

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3b10f539$0$15023$cc9e4d1f_at_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 - 23:44:55 CDT