Joan,
Can't this be done as a series of ors instead of the union alls as this
would presumably reduce it to one full table scan of each table.
e.g.
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.SSN IN ( T11.SSN,T11.HRID,SISID,MEDID,AFFID )
OR T21.ID IN (T11.SSN,T11.HRID,SISID,MEDID,AFFID )
OR EXISTS (SELECT 1
FROM PR_ALT_IDS
WHERE TRUNK = T11.TRUNK
AND ALT_ID IN (T21.SSN,T21.ID)))
OR (REPLACE(UPPER(T11.LASTNAME),'-',' ') =
REPLACE(UPPER(T21.LASTNAME),'-',' ')
AND UPPER(T11.FIRSTNAME) = UPPER(T21.FIRSTNAME) )
OR (REPLACE(UPPER(T11.LASTNAME),'-',' ') =
REPLACE(UPPER(T21.LASTNAME),'-',' ')
AND ((INSTR(UPPER(T11.FIRSTNAME),UPPER(T21.MIDDLENAME),1) = 1 AND
INSTR(UPPER(T11.MIDDLENAME),UPPER(T21.FIRSTNAME),1) = 1 )
OR (INSTR(UPPER(T21.FIRSTNAME),UPPER(T11.MIDDLENAME),1) = 1 AND
INSTR(UPPER(T21.MIDDLENAME),UPPER(T11.FIRSTNAME),1) = 1 )))
OR (UPPER(T11.LASTNAME) = UPPER(T21.FIRSTNAME)
AND UPPER(T11.FIRSTNAME) = UPPER(T21.LASTNAME))
OR ((INSTR(' '||REPLACE(UPPER(T11.LASTNAME),'-',' ')||' ','
'||UPPER(T21.LASTNAME)|| ' ' ) > 0
OR INSTR(' '||REPLACE(UPPER(T21.LASTNAME),'-',' ')||' ','
'||UPPER(T11.LASTNAME)||' ') > 0 )
AND UPPER(T11.FIRSTNAME) = UPPER(T21.FIRSTNAME)
AND (T11.MIDDLENAME IS NULL OR T21.MIDDLENAME IS NULL OR
UPPER(SUBSTR(T11.MIDDLENAME,1,1)) = UPPER(SUBSTR(T21.MIDDLENAME,1,1)) ))
OR (UPPER(T21.LASTNAME) = UPPER(T11.LASTNAME) AND
(INSTR(UPPER(T21.FIRSTNAME),UPPER(T11.FIRSTNAME),1) > 0 OR
INSTR(UPPER(T11.FIRSTNAME),UPPER(T21.FIRSTNAME),1) > 0 ) AND
(INSTR(UPPER(T21.MIDDLENAME),UPPER(T11.MIDDLENAME),1) > 0 OR
INSTR(UPPER(T11.MIDDLENAME),UPPER(T21.MIDDLENAME),1) > 0 ))
-----Original Message-----
Sent: 17 December 2002 19:35
To: Multiple recipients of list ORACLE-L
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 ))
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joan Hsieh
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
