Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: slow query help

RE: slow query help

From: Nicoll, Iain <IAIN.D.NICOLL_at_saic.com>
Date: Wed, 18 Dec 2002 03:08:42 -0800
Message-ID: <F001.0051C75E.20021218030842@fatcity.com> 





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: joan.hsieh_at_tufts.edu

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nicoll, Iain
  INET: IAIN.D.NICOLL_at_saic.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).


Received on Wed Dec 18 2002 - 05:08:42 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US