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: Mark Richard <mrichard_at_transurban.com.au>
Date: Tue, 17 Dec 2002 13:50:42 -0800
Message-ID: <F001.0051BFB5.20021217135042@fatcity.com>


I tend to agree with Stephane...

It looks like that query was possibly generated by a tool. If not you should go talk to the person who wrote it. Find out what the business rules are and write it from scratch. In reality the query keeps hitting the same two tables, presumably looking for rows that have just been loaded which match existing rows in another table based on the names matching in some fashion (ie: perhaps incorrect firstname/middlename usage, etc).

Some of Stephane's suggestions will also help. We store customer names as case sensitive but we obviously need to do a case-insensitive search from our application to find customers. The easiest way out - store the name twice (we have firstname and firstnameasupper, lastname and lastnameasupper, etc). Obviously the indexes sit on the "asupper" columns. Sure there is a slight amount of duplicated data, but it is done for a reason and let's us search customers very fast whist still preserving the nuance's that customers like to see when you send a bill to them.

Perhaps you need to store some columns which have had "replace" and "upper" applied. Finally since the query seems to accept pretty much any combination of lastname, firstname, middlename you could concatenate the three columns into a "namesearch" column are just perform three instr searches on that column, as opposed to trying every possibility - that should save Oracle some heartache.

Regards,

     Mark.

                                                                                                                   
                    Stephane                                                                                       
                    Faroult              To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    <sfaroult_at_orio       cc:                                                                       
                    le.com>              Subject:     Re: slow query help                                          
                    Sent by:                                                                                       
                    root_at_fatcity.c                                                                                 
                    om                                                                                             
                                                                                                                   
                                                                                                                   
                    18/12/2002                                                                                     
                    07:58                                                                                          
                    Please respond                                                                                 
                    to ORACLE-L                                                                                    
                                                                                                                   
                                                                                                                   




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).




<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   Privileged/Confidential information may be contained in this message.
          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Richard
  INET: mrichard_at_transurban.com.au

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 Tue Dec 17 2002 - 15:50:42 CST

Original text of this message

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