Home » RDBMS Server » Performance Tuning » Tune function (Windows 2003, Oracle Database 10g Enterprise Edition Release 10.2.0.4.0)
| Tune function [message #581954] |
Fri, 12 April 2013 02:42  |
 |
rahul1982
Messages: 53
Registered: November 2011
Location: Pune
|
Member |
|
|
Hi Experts,
I have created below function to remove specific words/special characters from string. This function is producing expected result. Using this function i need to insert around 900000 records in name_compress table. Insert is taking around 7 mins, how we can tune this function so that insert will be executed within 1-2 mins.
Function -
CREATE OR REPLACE FUNCTION NAME_FN(IN_STRING1 VARCHAR2)
RETURN VARCHAR2 IS
V_OUTPUT VARCHAR2(300);
V_OUTPUT1 VARCHAR2(300);
V_OUTPUT2 VARCHAR2(300);
V_OUTPUT3 VARCHAR2(300);
V_OUTPUT4 VARCHAR2(300);
V_OUTPUT5 VARCHAR2(300);
V_OUTPUT6 VARCHAR2(300);
--V_OUTPUT4 VARCHAR2(300);
IN_STRING VARCHAR2(300);
IN_STRING_M VARCHAR2(300);
-- ISEARCHNAME_V VARCHAR2(300);
FLAGA VARCHAR2(30);
FLAGB VARCHAR2(30);
FLAGC VARCHAR2(30);
BEGIN
/* IN_STRING_M := REPLACE(IN_STRING1,',',' ');
IN_STRING_M := RTRIM(LTRIM(upper(IN_STRING_M)));
IN_STRING_M := RTRIM(RTRIM(IN_STRING_M,'"'),'.');
IN_STRING_M := RTRIM(RTRIM(IN_STRING_M,'.'),'"');
IN_STRING_M := RTRIM(IN_STRING_M,' ');
IN_STRING_M := RTRIM(IN_STRING_M,'.');
IN_STRING_M := REPLACE(IN_STRING_M,'U.S.A','USA');
IN_STRING_M := REPLACE(IN_STRING_M,'.',' ');
IN_STRING_M := LTRIM(IN_STRING_M,'/');
IN_STRING_M := LTRIM(IN_STRING_M,'_');*/
IN_STRING_M := REPLACE(IN_STRING1,',',' ');
IN_STRING_M := TRIM(IN_STRING_M);
IN_STRING_M := REPLACE(IN_STRING_M,'U.S.A','USA');
IN_STRING_M := REPLACE(IN_STRING_M,'.',' ');
with data as (select upper(IN_STRING_M) val from dual)
select
translate(val,
'A'||translate(val,'$*+%ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@()+/\#$& ','$'),
'A') new_val
INTO IN_STRING
from data;
--$*+%ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@+/\#&
FLAGA := NULL;
WITH X AS
(SELECT IN_STRING AS STR FROM DUAL)
SELECT REGEXP_REPLACE(STR,
' (PROFESSIONAL LIMITED LIABILITY COMPANY|PROFESSIONAL LIMITED LIABILITY CO|REAL ESTATE INVESTMENT TRUST|MEDICAL DOCTORS PROFESSIONAL ASSOCIATION|MEDICAL DOCTORS PROFESSIONAL CORPORATION|REGISTERED LIMITED LIABILITY PARTNERSHIP|LIMITED LIABILITY COMPANY|LIMITED LIABILITY CO|LIMITED LIABILITY PARTNERSHIP|FEDERAL SAVINGS BANK)$')
INTO V_OUTPUT1
FROM X;
IF IN_STRING != V_OUTPUT1 THEN
-- V_OUTPUT1 := IN_STRING;
FLAGA := 'A';
END IF;
IF IN_STRING != V_OUTPUT1 THEN
FLAGB := NULL;
ELSE
with x as
(SELECT IN_STRING as str FROM dual)
select regexp_replace(str,' (LC|LLP|LP|LLC|ATTORNEYS AT LAW|NATIONAL BANK|BUSINESS TRUST|GENERAL PARTNERSHIP|NATIONAL ASSOCIATION|PROFESSIONAL ASSOCIATION|PROFESSIONAL CORPORATION|PROF ASSN|PROF CORP|SAVINGS ASSOCIATION|SERVICE CORPORATION|SOLE PROPRIETORSHIP|GEN PART|CREDIT UNION|LIMITED PARTNERSHIP|AS TRUSTEE|AGENCY|ASSOCIATION|ASSN|ASSOCIATES|ASSC|ASSOC|BANK|CHARTER|CHARTERED|COMPANY|CO|CORPORATION|CORP|CU|FSB|GP|INCORPORATED|INC|LIMITED|LTD|LTEE)$')
INTO V_OUTPUT2
from x;
IF IN_STRING != V_OUTPUT2 THEN
-- V_OUTPUT1 := IN_STRING;
FLAGB := 'B';
END IF;
END IF;
/* FLAGC := NULL;
with x as
(SELECT IN_STRING as str FROM dual)
select regexp_replace(str,' (MDPA|MDPC|NA|PARTNERS|PARTNERSHIP|PA|PC|PLLC|RAILROAD|RR|REIT|RLLP|SA|SC|SP|SPA|TRUST|TRUSTEE|C O|C U|C.U|F S B|F.S.B|G P|G.P|I N C|P L L C|P.L.L.C|L L C|L.L.C|L.LC|L L P|L.L.P|M D P A|M.D.P.A|M D P C|M.D.P.C|N A|N.A|P C|P.C|R R|R.R|R L L P|R.L.L.P|S A|S.A|S C|S P|S.P|S P A|S.P.A|L C|L.C|P A|P.A|L P|L.P|GEN PTR)$')
INTO V_OUTPUT3
from x;*/
IF IN_STRING != V_OUTPUT2 OR IN_STRING != V_OUTPUT THEN
FLAGC := NULL;
ELSE
with x as
(SELECT IN_STRING as str FROM dual)
select regexp_replace(str,' (MDPA|MDPC|NA|PARTNERS|PARTNERSHIP|PA|PC|PLLC|RAILROAD|RR|REIT|RLLP|SA|SC|SP|SPA|TRUST|TRUSTEE|C O|C U|F S B|G P|I N C|P L L C|L L C|L.LC|L L P|M D P A|M D P C|N A|P C|R R|R L L P|S A|S C|S P|S P A|L C|P A|L P|GEN PTR)$')
INTO V_OUTPUT3
from x;
IF IN_STRING != V_OUTPUT3 THEN
--V_OUTPUT1 := IN_STRING;
FLAGC := 'C';
END IF;
END IF;
IF FLAGA = 'A' THEN V_OUTPUT := V_OUTPUT1 ;
ELSIF FLAGA = 'A' AND (FLAGB = 'B' OR FLAGC = 'C' ) THEN V_OUTPUT := V_OUTPUT1 ;
ELSIF FLAGA = 'A' AND (FLAGB IS NULL OR FLAGC IS NULL ) THEN V_OUTPUT := V_OUTPUT1 ;
ELSIF FLAGB = 'B' AND FLAGC = 'C' THEN V_OUTPUT := V_OUTPUT2;
ELSIF FLAGB = 'B' AND (FLAGA IS NULL OR FLAGC IS NULL ) THEN V_OUTPUT := V_OUTPUT2 ;
ELSIF FLAGC = 'C' AND (FLAGA IS NULL AND FLAGB IS NULL) THEN V_OUTPUT := V_OUTPUT3 ;
ELSIF FLAGA IS NULL AND FLAGB IS NULL AND FLAGC IS NULL THEN V_OUTPUT := IN_STRING;
END IF;
IF V_OUTPUT LIKE 'THE %' OR V_OUTPUT LIKE 'DBA %' OR
V_OUTPUT LIKE 'DBA/%' OR V_OUTPUT LIKE 'DBA\%' THEN
V_OUTPUT := substr(V_OUTPUT,5);
ELSIF V_OUTPUT LIKE 'D B A %' OR V_OUTPUT LIKE 'D/B/A %'
OR V_OUTPUT LIKE 'D/B/A/%' OR V_OUTPUT LIKE '*D/B/A%' THEN
V_OUTPUT := substr(V_OUTPUT,7);
ELSIF V_OUTPUT LIKE '/DBA %' OR V_OUTPUT LIKE '/DBA/%' THEN
V_OUTPUT := substr(V_OUTPUT,6);
ELSIF V_OUTPUT='THE' OR V_OUTPUT ='DBA' THEN
V_OUTPUT :=' ';
END IF;
/* with x as
(SELECT V_OUTPUT as str FROM dual)
select regexp_replace(str,'^THE |^DBA |^DBA/|^DBA\|^D B A |^D/B/A |^D/B/A/|^*D/B/A|^DBA |^/DBA/')
INTO V_OUTPUT5
from x;
with x as
(SELECT V_OUTPUT5 as str FROM dual)
select regexp_replace(str,' AND | AND/|/AND/|/AND | AND| AND*','&')
INTO V_OUTPUT6
from x;*/
-- select regexp_replace(str,' AND | AND/|/AND/|/AND | AND| AND*','&')
V_OUTPUT := REPLACE(V_OUTPUT,' AND ','&');
V_OUTPUT := REPLACE(V_OUTPUT,' AND/','&');
V_OUTPUT := REPLACE(V_OUTPUT,'/AND/','&');
V_OUTPUT := REPLACE(V_OUTPUT,'/AND ','&');
V_OUTPUT := REPLACE(V_OUTPUT,' AND*','&');
IF V_OUTPUT LIKE '% AND' THEN
V_OUTPUT := SUBSTR(V_OUTPUT,1,LENGTH(V_OUTPUT)-4);
V_OUTPUT := V_OUTPUT||'&';
-- outSearchName := RTRIM(outSearchName,' AND') || '&';
END IF;
select
translate(V_OUTPUT,
'A'||translate(V_OUTPUT5,'$*+%ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@+/\#&$','$'),
'A') new_val
INTO V_OUTPUT4
from DUAL;
RETURN REPLACE(V_OUTPUT4,' ');
END;
Thank you!!!
|
|
|
|
|
|
| Re: Tune function [message #581957 is a reply to message #581955] |
Fri, 12 April 2013 03:12  |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I don't the usual information is going to be much help in this particular case, especially since I'm about to tell the OP to get rid of the only sql currently there.
@rahul1982
This:
with data as (select upper(IN_STRING_M) val from dual)
select
translate(val,
'A'||translate(val,'$*+%ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@()+/\#$& ','$'),
'A') new_val
INTO IN_STRING
from data;
Can and should be written more simply as this:
IN_STRING := translate(IN_STRING_M,
'A'||translate(IN_STRING_M,'$*+%ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@()+/\#$& ','$'),
'A');
Same for all the other selects. Never select a function from dual if you don't have to.
So get rid of the all the selects and the commented code, post the new version here and we'll take a look.
|
|
|
|
| Re: Tune function [message #581960 is a reply to message #581957] |
Fri, 12 April 2013 03:30 |
|
rahul1982
Messages: 53
Registered: November 2011
Location: Pune
|
Member |
|
|
Total records in AL000UCC02 table -
SQL> SELECT COUNT(*) FROM AL000UCC02;
COUNT(*)
----------
924604
insert script -
INSERT INTO RAHUL_COMPRESS_TEST1(XNAME,NMETYP,NMETYP2,RA9CNAME)
SELECT DNAME,DCODE,DTYPE,NAME_FN(DNAME) FROM AL000UCC02;
Index present on name_data table -
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED
ALUCC AL000UCC02_IX_DNAME NORMAL ALUCC AL000UCC02 TABLE NONUNIQUE DISABLED INDEXSPACE02 2 255 26214400 2097152 1 2147483645 0 2 YES 2 3621 393060 1 1 627237 VALID 932845 932845 12/19/2010 1:06:33 AM 1 1 NO N N N DEFAULT NO YES NO NO NO
@cookiemonster - thank you for your comments.
I will revise the function as you suggested.
[Updated on: Fri, 12 April 2013 03:31] Report message to a moderator |
|
|
|
| Re: Tune function [message #581961 is a reply to message #581960] |
Fri, 12 April 2013 03:45 |
|
rahul1982
Messages: 53
Registered: November 2011
Location: Pune
|
Member |
|
|
Hi cookiemonster,
New version -
CREATE OR REPLACE FUNCTION RAHUL_NOISE_FN_NEW(IN_STRING1 VARCHAR2)
RETURN VARCHAR2 IS
V_OUTPUT VARCHAR2(300);
V_OUTPUT1 VARCHAR2(300);
V_OUTPUT2 VARCHAR2(300);
V_OUTPUT3 VARCHAR2(300);
V_OUTPUT4 VARCHAR2(300);
IN_STRING VARCHAR2(300);
IN_STRING_M VARCHAR2(300);
FLAGA VARCHAR2(30);
FLAGB VARCHAR2(30);
FLAGC VARCHAR2(30);
BEGIN
IN_STRING_M := REPLACE(IN_STRING1, ',', ' ');
IN_STRING_M := TRIM(IN_STRING_M);
IN_STRING_M := REPLACE(IN_STRING_M, 'U.S.A', 'USA');
IN_STRING_M := REPLACE(IN_STRING_M, '.', ' ');
IN_STRING := TRANSLATE(IN_STRING_M,
'A' || TRANSLATE(IN_STRING_M,
'$*+%ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@()+/\#$& ',
'$'),
'A');
FLAGA := NULL;
WITH X AS
(SELECT IN_STRING AS STR FROM DUAL)
SELECT REGEXP_REPLACE(STR,
' (PROFESSIONAL LIMITED LIABILITY COMPANY|PROFESSIONAL LIMITED LIABILITY CO|REAL ESTATE INVESTMENT TRUST|MEDICAL DOCTORS PROFESSIONAL ASSOCIATION|MEDICAL DOCTORS PROFESSIONAL CORPORATION|REGISTERED LIMITED LIABILITY PARTNERSHIP|LIMITED LIABILITY COMPANY|LIMITED LIABILITY CO|LIMITED LIABILITY PARTNERSHIP|FEDERAL SAVINGS BANK)$')
INTO V_OUTPUT1
FROM X;
IF IN_STRING != V_OUTPUT1 THEN
FLAGA := 'A';
END IF;
IF IN_STRING != V_OUTPUT1 THEN
FLAGB := NULL;
ELSE
WITH X AS
(SELECT IN_STRING AS STR FROM DUAL)
SELECT REGEXP_REPLACE(STR,
' (LC|LLP|LP|LLC|ATTORNEYS AT LAW|NATIONAL BANK|BUSINESS TRUST|GENERAL PARTNERSHIP|NATIONAL ASSOCIATION|PROFESSIONAL ASSOCIATION|PROFESSIONAL CORPORATION|PROF ASSN|PROF CORP|SAVINGS ASSOCIATION|SERVICE CORPORATION|SOLE PROPRIETORSHIP|GEN PART|CREDIT UNION|LIMITED PARTNERSHIP|AS TRUSTEE|AGENCY|ASSOCIATION|ASSN|ASSOCIATES|ASSC|ASSOC|BANK|CHARTER|CHARTERED|COMPANY|CO|CORPORATION|CORP|CU|FSB|GP|INCORPORATED|INC|LIMITED|LTD|LTEE)$')
INTO V_OUTPUT2
FROM X;
IF IN_STRING != V_OUTPUT2 THEN
FLAGB := 'B';
END IF;
END IF;
IF IN_STRING != V_OUTPUT2 OR IN_STRING != V_OUTPUT THEN
FLAGC := NULL;
ELSE
WITH X AS
(SELECT IN_STRING AS STR FROM DUAL)
SELECT REGEXP_REPLACE(STR,
' (MDPA|MDPC|NA|PARTNERS|PARTNERSHIP|PA|PC|PLLC|RAILROAD|RR|REIT|RLLP|SA|SC|SP|SPA|TRUST|TRUSTEE|C O|C U|F S B|G P|I N C|P L L C|L L C|L.LC|L L P|M D P A|M D P C|N A|P C|R R|R L L P|S A|S C|S P|S P A|L C|P A|L P|GEN PTR)$')
INTO V_OUTPUT3
FROM X;
IF IN_STRING != V_OUTPUT3 THEN
FLAGC := 'C';
END IF;
END IF;
IF FLAGA = 'A' THEN
V_OUTPUT := V_OUTPUT1;
ELSIF FLAGA = 'A' AND (FLAGB = 'B' OR FLAGC = 'C') THEN
V_OUTPUT := V_OUTPUT1;
ELSIF FLAGA = 'A' AND (FLAGB IS NULL OR FLAGC IS NULL) THEN
V_OUTPUT := V_OUTPUT1;
ELSIF FLAGB = 'B' AND FLAGC = 'C' THEN
V_OUTPUT := V_OUTPUT2;
ELSIF FLAGB = 'B' AND (FLAGA IS NULL OR FLAGC IS NULL) THEN
V_OUTPUT := V_OUTPUT2;
ELSIF FLAGC = 'C' AND (FLAGA IS NULL AND FLAGB IS NULL) THEN
V_OUTPUT := V_OUTPUT3;
ELSIF FLAGA IS NULL AND FLAGB IS NULL AND FLAGC IS NULL THEN
V_OUTPUT := IN_STRING;
END IF;
IF V_OUTPUT LIKE 'THE %' OR V_OUTPUT LIKE 'DBA %' OR
V_OUTPUT LIKE 'DBA/%' OR V_OUTPUT LIKE 'DBA\%' THEN
V_OUTPUT := SUBSTR(V_OUTPUT, 5);
ELSIF V_OUTPUT LIKE 'D B A %' OR V_OUTPUT LIKE 'D/B/A %' OR
V_OUTPUT LIKE 'D/B/A/%' OR V_OUTPUT LIKE '*D/B/A%' THEN
V_OUTPUT := SUBSTR(V_OUTPUT, 7);
ELSIF V_OUTPUT LIKE '/DBA %' OR V_OUTPUT LIKE '/DBA/%' THEN
V_OUTPUT := SUBSTR(V_OUTPUT, 6);
ELSIF V_OUTPUT = 'THE' OR V_OUTPUT = 'DBA' THEN
V_OUTPUT := ' ';
END IF;
V_OUTPUT := REPLACE(V_OUTPUT, ' AND ', '&');
V_OUTPUT := REPLACE(V_OUTPUT, ' AND/', '&');
V_OUTPUT := REPLACE(V_OUTPUT, '/AND/', '&');
V_OUTPUT := REPLACE(V_OUTPUT, '/AND ', '&');
V_OUTPUT := REPLACE(V_OUTPUT, ' AND*', '&');
IF V_OUTPUT LIKE '% AND' THEN
V_OUTPUT := SUBSTR(V_OUTPUT, 1, LENGTH(V_OUTPUT) - 4);
V_OUTPUT := V_OUTPUT || '&';
END IF;
V_OUTPUT4 := TRANSLATE(V_OUTPUT,
'A' || TRANSLATE(V_OUTPUT,
'$*+%ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@+/\#&$',
'$'),
'A');
RETURN REPLACE(V_OUTPUT4, ' ');
END;
|
|
|
|
| Re: Tune function [message #581964 is a reply to message #581957] |
Fri, 12 April 2013 04:15 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Fri, 12 April 2013 09:12Same for all the other selects. Never select a function from dual if you don't have to.
So get rid of the all the selects and the commented code, post the new version here and we'll take a look.
And I missed an UPPER from my rewritten example, I suggest you add it.
|
|
|
|
| Re: Tune function [message #582252 is a reply to message #581954] |
Tue, 16 April 2013 07:05 |
|
rahul1982
Messages: 53
Registered: November 2011
Location: Pune
|
Member |
|
|
I made below changes and now while inserting 924604 records using this function, insert script is taking 4 mins; earlier it was 7 mins.
Function -
CREATE OR REPLACE FUNCTION RAHUL_NOISE_FN_NEW(IN_STRING1 VARCHAR2)
RETURN VARCHAR2 IS
V_OUTPUT VARCHAR2(300);
V_OUTPUT1 VARCHAR2(300);
V_OUTPUT2 VARCHAR2(300);
V_OUTPUT3 VARCHAR2(300);
V_OUTPUT4 VARCHAR2(300);
IN_STRING VARCHAR2(300);
IN_STRING_M VARCHAR2(300);
FLAGA VARCHAR2(30);
FLAGB VARCHAR2(30);
BEGIN
IN_STRING_M := REPLACE(UPPER(IN_STRING1), ',', ' ');
IN_STRING_M := TRIM(IN_STRING_M);
IN_STRING_M := REPLACE(IN_STRING_M, 'U.S.A', 'USA');
IN_STRING_M := REPLACE(IN_STRING_M, '.', ' ');
IN_STRING := TRANSLATE(IN_STRING_M,
'A' || TRANSLATE(IN_STRING_M,
'$*+%ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@()+/\#$& ',
'$'),
'A');
FLAGA := NULL;
V_OUTPUT1 := REGEXP_REPLACE(IN_STRING,
' (PROFESSIONAL LIMITED LIABILITY COMPANY|PROFESSIONAL LIMITED LIABILITY CO|REAL ESTATE INVESTMENT TRUST|MEDICAL DOCTORS PROFESSIONAL ASSOCIATION|MEDICAL DOCTORS PROFESSIONAL CORPORATION|REGISTERED LIMITED LIABILITY PARTNERSHIP|LIMITED LIABILITY COMPANY|LIMITED LIABILITY CO|LIMITED LIABILITY PARTNERSHIP|FEDERAL SAVINGS BANK|MDPA|MDPC|NA|PARTNERS|PARTNERSHIP|PA|PC|PLLC|RAILROAD|RR|REIT|RLLP|SA|SC|SP|SPA|BUSINESS TRUST|TRUSTEE|C O|C U|F S B|G P|I N C|P L L C|N A|P C|R R|R L L P|S A|S C|S P|S P A)$');
IF IN_STRING != V_OUTPUT1 THEN
FLAGA := 'A';
ELSIF IN_STRING != V_OUTPUT1 THEN
FLAGB := NULL;
ELSE
V_OUTPUT2 := REGEXP_REPLACE(IN_STRING,
' (LC|LLP|LP|LLC|ATTORNEYS AT LAW|NATIONAL BANK|TRUST|GENERAL PARTNERSHIP|NATIONAL ASSOCIATION|PROFESSIONAL ASSOCIATION|PROFESSIONAL CORPORATION|PROF ASSN|PROF CORP|SAVINGS ASSOCIATION|SERVICE CORPORATION|SOLE PROPRIETORSHIP|GEN PART|CREDIT UNION|LIMITED PARTNERSHIP|AS TRUSTEE|AGENCY|ASSOCIATION|ASSN|ASSOCIATES|ASSC|ASSOC|BANK|CHARTER|CHARTERED|COMPANY|CO|CORPORATION|CORP|CU|FSB|GP|INCORPORATED|INC|LIMITED|LTD|LTEE|L L C|LLC|L L P|M D P A|M D P C|L C|P A|L P|GEN PTR)$');
IF IN_STRING != V_OUTPUT2 THEN
FLAGB := 'B';
END IF;
END IF;
IF FLAGA = 'A' THEN
V_OUTPUT := V_OUTPUT1;
ELSIF FLAGA = 'A' AND (FLAGB = 'B' OR FLAGB IS NULL) THEN
V_OUTPUT := V_OUTPUT1;
ELSIF FLAGB = 'B' AND FLAGA IS NULL THEN
V_OUTPUT := V_OUTPUT2;
ELSIF FLAGA IS NULL AND FLAGB IS NULL THEN
V_OUTPUT := IN_STRING;
END IF;
V_OUTPUT3 := REGEXP_REPLACE(V_OUTPUT,
'^THE |^DBA |^DBA/|^DBA\|^D B A |^D/B/A |^D/B/A/|^*D/B/A|^DBA |^/DBA/');
V_OUTPUT4 := REGEXP_REPLACE(V_OUTPUT3,
' AND | AND/|/AND/|/AND | AND| AND*',
'&');
RETURN REPLACE(V_OUTPUT4, ' ');
END;
Below is the insert script -
INSERT INTO RAHUL_COMPRESS_TEST1(XNAME,NMETYP,NMETYP2,RA9CNAME)
SELECT /*+ index(al IX_DNAME)*/ al.DNAME,al.DCODE,al.DTYPE, Rahul_NOISE_FN_new(DNAME) FROM AL000UCC02 al;
Please suggest how we can improve on function/insert script so that insert will be completed within 1 or 2 mins.
Thank you!!
|
|
|
|
| Re: Tune function [message #582254 is a reply to message #582252] |
Tue, 16 April 2013 07:25 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You appear to have introduced some bugs, I suggest you fix them before we try to improve the performance:
1) you never re-added the upper I accidently removed from the first translate.
2) This makes no sense:
IF IN_STRING != V_OUTPUT1 THEN
FLAGA := 'A';
ELSIF IN_STRING != V_OUTPUT1 THEN
3) Likewise the elsif here makes no sense:
IF FLAGA = 'A' THEN
V_OUTPUT := V_OUTPUT1;
ELSIF FLAGA = 'A' AND (FLAGB = 'B' OR FLAGB IS NULL) THEN
V_OUTPUT := V_OUTPUT1;
It would help if you explained why flaga and flagb exist at all.
|
|
|
|
|
|
| Re: Tune function [message #582262 is a reply to message #582258] |
Tue, 16 April 2013 08:11 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I don't see any need for flaga and flagb then. Just have both regexp_replaces set v_output and don't bother checking if the 2nd regexp_replace has actually changed anything. You've got about 10 lines of unecessary code there.
While you're at it get rid of v_output3 and v_output4, have the lines that set those variables set v_output instead.
EDIT: typo
[Updated on: Tue, 16 April 2013 08:17] Report message to a moderator |
|
|
|
| Re: Tune function [message #582275 is a reply to message #582262] |
Tue, 16 April 2013 09:10 |
|
rahul1982
Messages: 53
Registered: November 2011
Location: Pune
|
Member |
|
|
Thank you for your inputs -
revised code -
CREATE OR REPLACE FUNCTION RAHUL_NOISE_FN_NEW(IN_STRING1 VARCHAR2)
RETURN VARCHAR2 IS
V_OUTPUT VARCHAR2(300);
IN_STRING VARCHAR2(300);
IN_STRING_M VARCHAR2(300);
BEGIN
IN_STRING_M := REPLACE(UPPER(IN_STRING1), ',', ' ');
IN_STRING_M := TRIM(IN_STRING_M);
IN_STRING_M := REPLACE(IN_STRING_M, 'U.S.A', 'USA');
IN_STRING_M := REPLACE(IN_STRING_M, '.', ' ');
IN_STRING := TRANSLATE(IN_STRING_M,
'A' || TRANSLATE(IN_STRING_M,
'$*+%ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@()+/\#$& ',
'$'),
'A');
V_OUTPUT := REGEXP_REPLACE(IN_STRING,
' (PROFESSIONAL LIMITED LIABILITY COMPANY|PROFESSIONAL LIMITED LIABILITY CO|REAL ESTATE INVESTMENT TRUST|MEDICAL DOCTORS PROFESSIONAL ASSOCIATION|MEDICAL DOCTORS PROFESSIONAL CORPORATION|REGISTERED LIMITED LIABILITY PARTNERSHIP|LIMITED LIABILITY COMPANY|LIMITED LIABILITY CO|LIMITED LIABILITY PARTNERSHIP|FEDERAL SAVINGS BANK|MDPA|MDPC|NA|PARTNERS|PARTNERSHIP|PA|PC|PLLC|RAILROAD|RR|REIT|RLLP|SA|SC|SP|SPA|BUSINESS TRUST|TRUSTEE|C O|C U|F S B|G P|I N C|P L L C|N A|P C|R R|R L L P|S A|S C|S P|S P A)$');
V_OUTPUT := REGEXP_REPLACE(V_OUTPUT,
' (LC|LLP|LP|LLC|ATTORNEYS AT LAW|NATIONAL BANK|TRUST|GENERAL PARTNERSHIP|NATIONAL ASSOCIATION|PROFESSIONAL ASSOCIATION|PROFESSIONAL CORPORATION|PROF ASSN|PROF CORP|SAVINGS ASSOCIATION|SERVICE CORPORATION|SOLE PROPRIETORSHIP|GEN PART|CREDIT UNION|LIMITED PARTNERSHIP|AS TRUSTEE|AGENCY|ASSOCIATION|ASSN|ASSOCIATES|ASSC|ASSOC|BANK|CHARTER|CHARTERED|COMPANY|CO|CORPORATION|CORP|CU|FSB|GP|INCORPORATED|INC|LIMITED|LTD|LTEE|L L C|LLC|L L P|M D P A|M D P C|L C|P A|L P|GEN PTR)$');
IF IN_STRING = V_OUTPUT THEN
V_OUTPUT := IN_STRING;
END IF;
V_OUTPUT := REGEXP_REPLACE(V_OUTPUT,
'^THE |^DBA |^DBA/|^DBA\|^D B A |^D/B/A |^D/B/A/|^*D/B/A|^DBA |^/DBA/');
V_OUTPUT := REGEXP_REPLACE(V_OUTPUT,
' AND | AND/|/AND/|/AND | AND| AND*',
'&');
RETURN REPLACE(V_OUTPUT, ' ');
END;
After revising the code, i have re-exeuted insert; insert script took 4mins.
Insert script -
INSERT INTO RAHUL_COMPRESS_TEST1(XNAME,NMETYP,NMETYP2,RA9CNAME)
SELECT /*+ index(al IX_DNAME)*/ al.DNAME,al.DCODE,al.DTYPE, Rahul_NOISE_FN_new(DNAME) FROM AL000UCC02 al
Thank you!!!
|
|
|
|
| Re: Tune function [message #582278 is a reply to message #582275] |
Tue, 16 April 2013 09:13 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's not equivalent to your previous code. Originally the two regexp_substrs were mutually exclusive, now they're not.
This is pointless:
IF IN_STRING = V_OUTPUT THEN
V_OUTPUT := IN_STRING;
END IF;
|
|
|
|
Goto Forum:
Current Time: Wed Dec 18 00:40:43 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
