Re: Can somone optimize this Query?

Hi.

 Jonathan already adviced you to define an index on OWNERID.  If it doesnot help - post the EXPLAIN of queries

1. With distinct and order by
2. Without

 HTH. Michael.

In article <7tvr9m$36v$1_at_nnrp1.deja.com>,   vlee_at_jps.net wrote:
> 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.
>

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