Re: Can somone optimize this Query?

In article <3804AAD9.C2A556FA_at_pacbell.net>,   Tom Scott <tom-s_at_pacbell.net> wrote:
> > 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
>
> I assume you have indexes on p.ref_personid, p2.personid, and
p.ownerid.
> Even if you do, the use of NVL is forcing a full table scan, no matter
> what indexes you have on p1 and p2.
>
> Functions like TRUNC, SUBSTR, TO_DATE, INSTR, etc., will all disable
> indexes and force full table scans. If you are using Oracle 8i, you
can
> now create function based indexes to get around this problem.
>

Hi,
 Not true in this case. He does not use functions in his where  predicate, therefore the indexes could be used.

 You might want to post the execution plan for that query - this might  help in fixing the problem.
 I cannot see obvious problems with that query.

 I suppose, it does not return many rows, so sorting the result should  be cheap.

 Consider using bind variables for the literals, this might save cpu-  time for parsing.

Karsten

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 14 1999 - 09:31:20 CDT