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 Go to next message
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 #581955 is a reply to message #581954] Fri, 12 April 2013 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Tune function [message #581957 is a reply to message #581955] Fri, 12 April 2013 03:12 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Fri, 12 April 2013 09:12
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.


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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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
The elsif will never execute as it has the same condition as the initial if.
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 does the same as the if.


It would help if you explained why flaga and flagb exist at all.
Re: Tune function [message #582258 is a reply to message #582254] Tue, 16 April 2013 07:52 Go to previous messageGo to next message
rahul1982
Messages: 53
Registered: November 2011
Location: Pune
Member
1) instead of first translate i have added upper in - IN_STRING_M := REPLACE(UPPER(IN_STRING1), ',', ' ');

2) Revised code -
IF IN_STRING != V_OUTPUT1 THEN

    FLAGA := 'A';
 
  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;


3) Revised code -
IF FLAGA = 'A' THEN
    V_OUTPUT := V_OUTPUT1;
  ELSIF FLAGB = 'B' THEN
    V_OUTPUT := V_OUTPUT2;
  ELSIF FLAGA IS NULL AND FLAGB IS NULL THEN
    V_OUTPUT := IN_STRING;
    END IF;


I am using FLAGA & FLAGB to determine specific words are removed from which REGEXP_REPLACE; so that it's output can be returned.

thank you!!
Re: Tune function [message #582262 is a reply to message #582258] Tue, 16 April 2013 08:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 Go to previous message
cookiemonster
Messages: 13917
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;

Previous Topic: Application services
Next Topic: Performance degrade after 10.2.0.5 patch upgrade and downgrade
Goto Forum:
  


Current Time: Fri Mar 29 10:02:30 CDT 2024