Re: Can somone optimize this Query?

> 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. Received on Wed Oct 13 1999 - 10:52:57 CDT