RE: slow query help
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.IDTARGET_ID
,T21.SSN TARGET_SSN
,T21.FULLNAME TARGET_FULLNAME
,T21.LASTNAME TARGET_LASTNAME
,T21.MIDDLENAMETARGET_MIDDLENAME
,T21.FIRSTNAME TARGET_FIRSTNAME
,T21.DOB TARGET_DOB
,T21.GENDERTARGET_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
RE: slow query help
Joan - Just a random thought inspired by your explanation. Is this query a part of cleaning data for a data warehouse? Or worse yet, is the developer attempting to use this query in an attempt to avoid creating a data warehouse? Normally the best solution for the situation you describe is to create a data warehouse. If those words scare you, just say that it is a couple of tables to provide cleaned data. Then you can pull data from one system at a time, clean it, and store it. Based on your description, be aware that even if the query is made to work, as my grandpappy would say with bailing wire and chewing gum, it may be very fragile and need constant attention. Developers have this nasty habit of moving along to the next development project and you, the DBA, are stuck with a problem. The developer gets all the glory for making it work. You get all the blame because it keeps breaking. Not pretty. Anyway, this is what I got out of your explanation, and if I'm off base, just ignore this message. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 17, 2002 8:49 PM To: Multiple recipients of list ORACLE-L Mark, This is a new in-house developed the school directory system. The source data come from all over the different systems. The data quite dirty in some case. I am not sure how the business rule was defined yet. The developer knew her query was nasty, so she turned on me for help. This query is part of the whole process. As you said, it compares all the possible same person feed from all the systems (med, sis,law...)and insert it to another table. I think you and Stephane's suggestions are very valuable and I will definitely ask her to review her design of this processing. (sigh, we are not that experienced and good on this) After I work more with developer and get more knowledge of this application. I'd like to ask some detail of your experience in such searching logic. many thanks, Joan Mark Richard wrote: 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 [EMAIL PROTECTED] sfaroult@orio cc: le.com Subject: Re: slow query help Sent by: [EMAIL PROTECTED] 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
RE: slow query help
Title: RE: slow query help Joan, Here is a suggestion ... if this is going to be your most used part, I'd look into Intermedia ... you'll have a lot more options to work with and they will work good. Until then, I'd recommend replacing instr() with appropriate LIKE clause because at-least it will help you use an index. Through a DB trigger ensure that all names (first/middle/last) are always in UPPER so you don't have to pur UPPER or deal with FBI. Initially I thought of merging all clauses into one, but then I realized, Oracle will end up doing what I un-did (it will replace all OR conditions to UNION ALL anyways). Happy Holidays Everyone !! Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: slow query help
Raj, I thought it would only do the union all if it was able to use an index and all the instr's look as though they'd stop that. Even then I thought it was generally just rule that would do that unless you used the use_concat hint. I can't see why a full table scan of each wouldn't be fairly efficient unless the result set is only a small fraction of the total possible rows. I may have missed some e-mails which make the above statements irrevelant, if so my apologies. Iain -Original Message- Sent: 18 December 2002 13:55 To: Multiple recipients of list ORACLE-L Joan, Here is a suggestion ... if this is going to be your most used part, I'd look into Intermedia ... you'll have a lot more options to work with and they will work good. Until then, I'd recommend replacing instr() with appropriate LIKE clause because at-least it will help you use an index. Through a DB trigger ensure that all names (first/middle/last) are always in UPPER so you don't have to pur UPPER or deal with FBI. Initially I thought of merging all clauses into one, but then I realized, Oracle will end up doing what I un-did (it will replace all OR conditions to UNION ALL anyways). Happy Holidays Everyone !! Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- 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).
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: [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).
Re: slow query help
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 [EMAIL PROTECTED] sfaroult@orio cc: le.com Subject: Re: slow query help Sent by: [EMAIL PROTECTED] 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
RE: slow query help
Title: RE: slow query help It need not be generatd by a tool, I have worked for a blood bank in one of my previous projects. When it comes to matching a donor in th records, you have to take a lot of precautions to see if you have a duplicate donor etc. To me this seems to be logic to find duplicate customers ... our algorithm was worse though . Raj -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 4:51 PM To: Multiple recipients of list ORACLE-L Subject: Re: slow query help 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). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: slow query help
Mark,
This is a new in-house developed the school directory system. The
source data come from all over the different systems. The data quite
dirty in some case. I am not sure how the business rule was defined yet.
The developer knew her query was nasty, so she turned on me for help.
This query is part of the whole process. As you said, it compares all
the possible same person feed from all the systems (med, sis,law...)and
insert it to another table. I think you and Stephane's suggestions are
very valuable and I will definitely ask her to review her design of this
processing. (sigh, we are not that experienced and good on this) After I
work more with developer and get more knowledge of this application. I'd
like to ask some detail of your experience in such searching logic.
many thanks,
Joan
Mark Richard wrote:
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 [EMAIL PROTECTED]
sfaroult@orio cc:
le.com Subject: Re: slow query help
Sent by:
[EMAIL PROTECTED]
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
Re: slow query help
Hey Raj, I think it generated by a tool. The original code had all kinds of hints, I asked her where comes from the hints, she said from the tool. Anyway, no suggestion? That's not like you. :) joan Jamadagni, Rajendra wrote: It need not be generatd by a tool, I have worked for a blood bank in one of my previous projects. When it comes to matching a donor in th records, you have to take a lot of precautions to see if you have a duplicate donor etc. To me this seems to be logic to find duplicate customers ... our algorithm was worse though . Raj -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 4:51 PM To: Multiple recipients of list ORACLE-L Subject: Re: slow query help 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). Name: ESPN_Disclaimer.txt ESPN_Disclaimer.txtType: Plain Text (text/plain) Encoding: 7BIT -- 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).
