Re: slow query help

Joan Hsieh wrote:
>
> Hi,
>
> This is the query bothered us very much recently. It run at least 15
> min. and sometimes crashed the temp tablespace. Do you have any idea how
> to make it run better. Our developer tried used two cursors to compare
> the result, but the result is not optimized. We tried used last name and
> first name function based index on sm_new_load table. If someone
> interested in this query, I can sent you the execution plan.
> Thanks in advanced,
>
> Joan
>
> SELECT T11.TRUNK TRUNK_FOUND,T21.ID TARGET_ID,T21.SSN
> TARGET_SSN,T21.FULLNAME
> TARGET_FULLNAME,T21.LASTNAME TARGET_LASTNAME,T21.MIDDLENAME
> TARGET_MIDDLENAME,T21.FIRSTNAME TARGET_FIRSTNAME,T21.DOB TARGET_DOB,
> T21.GENDER TARGET_GENDER
> FROM
> SM_NEW_LOAD T21,PR_IDENTITY T11 WHERE ( T21.ROWID,T11.ROWID ) IN
> (SELECT
> T22.ROWID,T12.ROWID FROM
> PR_IDENTITY T12,SM_NEW_LOAD T22 WHERE T22.SSN
> IN ( T12.SSN,T12.HRID,SISID,MEDID,AFFID ) OR T22.ID IN (
> T12.SSN,T12.HRID,
> SISID,MEDID,AFFID ) OR EXISTS (SELECT 1 FROM
> PR_ALT_IDS WHERE TRUNK = T12.TRUNK AND ALT_ID IN ( T22.SSN,T22.ID
> )) UNION ALL
> SELECT T22.ROWID,
> T12.ROWID FROM
> SM_NEW_LOAD T22,PR_IDENTITY T12 WHERE
> REPLACE(UPPER(T12.LASTNAME),'-',' ') =
> REPLACE(UPPER(T22.LASTNAME),'-',' ')
> AND UPPER(T12.FIRSTNAME) = UPPER(T22.FIRSTNAME) UNION ALL
> SELECT
> T23.ROWID,T13.ROWID FROM
> SM_NEW_LOAD T23,PR_IDENTITY T13 WHERE
> REPLACE(UPPER(T13.LASTNAME),'-',' ') =
> REPLACE(UPPER(T23.LASTNAME),'-',' ')
> AND ((INSTR(UPPER(T13.FIRSTNAME),UPPER(T23.MIDDLENAME),1) = 1 AND
> INSTR(UPPER(T13.MIDDLENAME),UPPER(T23.FIRSTNAME),1) = 1 ) OR
> (INSTR(UPPER(T23.FIRSTNAME),UPPER(T13.MIDDLENAME),1) = 1 AND
> INSTR(UPPER(T23.MIDDLENAME),UPPER(T13.FIRSTNAME),1) = 1 )) UNION ALL
> SELECT
> T24.ROWID,T14.ROWID FROM
> SM_NEW_LOAD T24,PR_IDENTITY T14 WHERE
> UPPER(T14.LASTNAME) = UPPER(T24.FIRSTNAME) AND UPPER(T14.FIRSTNAME) =
> UPPER(T24.LASTNAME) UNION ALL SELECT T25.ROWID,T15.ROWID FROM
> SM_NEW_LOAD T25,PR_IDENTITY T15 WHERE (INSTR(' ' ||
> REPLACE(UPPER(T15.LASTNAME),'-',' ') || ' ' ,' ' ||
> UPPER(T25.LASTNAME)
> || ' ' ) > 0 OR INSTR(' ' || REPLACE(UPPER(T25.LASTNAME),'-',' ') ||
> ' ' ,
> ' ' || UPPER(T15.LASTNAME) || ' ' ) > 0 ) AND UPPER(T15.FIRSTNAME) =
> UPPER(T25.FIRSTNAME) AND (T15.MIDDLENAME IS NULL OR T25.MIDDLENAME
> IS
> NULL OR UPPER(SUBSTR(T15.MIDDLENAME,1,1)) =
> UPPER(SUBSTR(T25.MIDDLENAME,1,
> 1)) ) UNION ALL
> SELECT T27.ROWID,T17.ROWID FROM
> PR_IDENTITY T17,
> SM_NEW_LOAD T27 WHERE UPPER(T27.LASTNAME) = UPPER(T17.LASTNAME) AND
> (INSTR(UPPER(T27.FIRSTNAME),UPPER(T17.FIRSTNAME),1) > 0 OR
> INSTR(UPPER(T17.FIRSTNAME),UPPER(T27.FIRSTNAME),1) > 0 ) AND
> (INSTR(UPPER(T27.MIDDLENAME),UPPER(T17.MIDDLENAME),1) > 0 OR
> INSTR(UPPER(T17.MIDDLENAME),UPPER(T27.MIDDLENAME),1) > 0 ))
> --

Joan,

   I think that you have a design problem here. First it is obvious that if all your data was entered in uppercase, it would help. If you are on 8.1.7 or above, function-based index should help. Otherwise the code is obviously awkward. What use is (a.rowid, b.rowid) in (select c.rowid, d.rowid ...) ? Quite obviously you are scanning the same data twice. All your 'replace', 'instr', 'upper' etc. are performance killers. The easiest thing to do is possibly to denormalise PR_IDENTITY, add a column (trigger-entered) which contains data you can search without applying functions of death to it - and index it.

-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).