Re: Can somone optimize this Query?

Jonathan,

Thanks for your help. Sorry for the lack of information. Here it is.

In article <939714630.19550.0.nnrp-07.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Not very much information to go on -
> Which version of Oracle

      8i

> Quantify 'really slow'

      If you take out the DISTINCT and the ORDER BY the return is instantenous. When you leave it, it takes 2 minutes. There are 4200 records and duplicate values. I have indexes on the personid(unique), firstname and last_name.
> How big is the person table
> What indices are defined
> Which optimisation method are you using
What optimization methods can I use and where can I get information.
> Have you analyzed the table
>
> First obvious question -
> Have you got indices defined on:
> person.ownerid
> person.personid
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> vlee_at_jps.net wrote in message <7tu86h$u7o$1_at_nnrp1.deja.com>...
> >Can anyone optimize this query? It runs really slow.
> >SELECT DISTINCT p.personid, p.first_name, p.last_name, p.username,
> >p.address1, p.address2, p.city, p.state, p.zip, p.company_name,
> >p.phone1, p.fax, p.ref_personid, p2.viewable, nvl((p2.last_modified -
> >p.last_modified), 0) as difference FROM person p, person p2 WHERE
> >p.ref_personid = p2.personid(+) AND p.ownerid = 25971 ORDER BY 3 ,
> >p.last_name, p.first_name
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 12 1999 - 12:34:12 CDT