SQL optimization--custom functions in SQL

Long Run SQL:

              MERGE into Intitmrtnparam D USING (SELECT a.inrfilenm,a.inrstat,a.in                           Rdepcd,a.inritmcd,a.inrsubcd,a.inrsupcd,a.inrstocd from Intitmrtnparam A,intitmrtnparam B WHERE 1=1 and A.rowid <> B.rowid and A.I                             Nrfilenm = b.inrfilenm and A.inrstat = 0 and B.inrstat = 0                             and A.inrfilenm = p_filenm and B.inrfilenm = P_filenm                             and A.INRDEPCD = b.inrdepcd and A.INRITMCD = B.INRITMCD and Pkcrs. Checkincludecusttype (NVL (A.INRSUBCD, ' all '), NVL (B.INRSUBCD, ' all ')) = 1 and Pkcrs. Checkincludecusttype (NVL (A.INRSUPCD, ' all '), NVL (B.INRSUPCD, ' all ')) = 1 and Pkcrs. Checkincludecusttype (NVL (A.INRSTOCD, 'All '), NVL (B.INRSTOCD, "all") = 1 GROUP by A.INRFILENM,A.INRSTAT,A.INRDEPCD,A.INRITMCD,A.INRSUBCD , A.INRSUPCD,A.INRSTOCD) E on (D.inrfilenm = E.inrfilenm and d.in                    Rstat = E.inrstat and d.inrdepcd = e.inrdepcd and D.INRITMCD = E.INRITMCD and DECODE (Trim (NVL (D.INRSUBCD, ')), ', ' null ', Trim (D.INRSUBCD)) = DECODE (Trim (NVL (E.INRSUBCD, ')), ', ', ' null ', T RIM (E.INRSUBCD)) and DECODE (Trim (NVL (D.INRSUPCD, ')), ', ' NULL ', trim (d.inrsupcd)) = DECODE (Trim (NVL (e.in  RSUPCD, '), ', ', ' null ', Trim (E.INRSUPCD)) and DECODE (Trim (NVL (D.INRSTOCD, ')), "', ' null ', Trim (D.INRSTOCD)) = DECODE (Trim (NVL (E.INRSTOCD, "))," "," NULL ", TRIM (E.INRSTOCD))) when matched then UPDATE SET D . Inrerrno = 2, D.inrerrmes = D.inrerrmes | |         ', dupplicate item ', D.INRUPD = P_date,                 D.INRUSR = P_user; 

function (Checkincludecusttype) called in Long sql:

   FUNCTION Checkincludecusttype (P_custtype1 pritsphdr. Tsphcustype%type,p_custtype2 Pritsphdr.    Tsphcustype%type) RETURN number is V_check number (1);        BEGIN IF p_custtype2 is null OR p_custtype1 are null then RETURN 1;        END IF;                         BEGIN Select 1 into V_check from (SELECT level, SUBSTR ( String_to_tokenize, DECODE (level, 1, 1, INSTR (String_to_tokenize, DELIMITER, 1, LEVEL-1) +1), INSTR (String_to_tokenize, DELIMITER, 1, level)-DECODE (level, 1, 1, INSTR (string_to_ Tokenize, DELIMITER, 1, LEVEL-1) +1) as Token from (SELECT p_custtype1| | ', ' A S string_to_tokenize, ', ' as DELIMITER from DUAL] CONNECT by INSTR (String_to_tokenize, DELIMITER, 1, level                      >0 ORDER by Level ASC) Custtype1, (SELECT level,   SUBSTR (String_to_tokenize, DECODE (level, 1, 1, INSTR (String_to_tokenize, DELIMITER, 1, LEVEL-1) +1), INSTR (String_to_tokenize, DELIMITER, 1, level)-DECODE (level, 1, 1, INST R (String_to_tokenize, DELIMITER, 1, LEVEL-1) +1) as Token from (SELECT p_custtype2| | ', ' as String_to_tokenize, ', ' as DELIMITER from DUAL] CONNECT by INSTR (String_to_tokenize, DE Limiter, 1, level) >0 ORDER by Level ASC) custtype2 WHERE Custtype1.token =        Custtype2.token and rownum = 1;        EXCEPTION when No_data_found then v_check: = 0;        END;    RETURN V_check; END Checkincludecusttype;

After optimization:

          UPDATE Intitmrtncon D SET d.inrerrno = 2, D.inrerrmes = D.inrerrmes | |            ', dupplicate item ', D.INRUPD = p_date, d.inrusr = p_user WHERE inrfilenm = p_filenm and Inrstat = 0                 and (Inrfilenm, Inrstat, INRDPCD, INRITMCD, NVL (INRSUBCD, ' all '), NVL (INRSUPCD, "all")) in                    (SELECT inrfilenm, Inrstat, INRDPCD, INRITMCD, NVL (INRSUBCD, ' all '), NVL (INRSUPCD, ' all ') From (SELECT t.inrfilenm, T.inrstat, T.INRDPCD, T.INRITMCD, T.INRSUBCD, t.inrsupcd from INTITMR Tncon T WHERE INSTR (NVL (Trim (INRSUBCD), ' All '), ', ') = 0 and INSTR (n                          VL (Trim (INRSUPCD), ' All '), ', ') = 0 and Inrfilenm = p_filenm and Inrstat = 0                             UNION All Select A.inrfilenm, A.inrstat, A.INRDPCD, A.INRITMCD, A.INRSUBCD, B.INRSUPCD From (SELECT SUBSTR (strinG_to_tokenize, DECODE (level, 1, 1, INSTR (String_to_tokenize, DELIMITER,                                                1, LEVEL-1) + 1), INSTR (String_to_tokenize, DELIMITER, 1, level)-DECODE (level, 1, 1, INST                      R (String_to_tokenize, DELIMITER, 1, LEVEL-1) + 1))                                    As INRSUBCD, V_rowid, Inrfilenm, Inrstat, INRDPCD, INRITMCD From (SELECT T.INRSUBCD | |                                                 ', ' as String_to_tokenize, ', ' as DELIMITER, T.rowid V_rowid, Inrfilenm,  Inrstat, INRDPCD, INRITMCD from Intitmrtncon T WHERE inrfilenm = P_filenm and INRSTAT = 0 and (INSTR (INRSUBCD, ', ') >= 1 or INSTR (INRSUPCD, ', ') >= 1))                                 CONNECT by INSTR (String_to_tokenize, DELIMITER, 1, Level) > 0) A, (SELECT SUBSTR (string_to_tokenize, DECODE (level, 1, 1, INSTR (S Tring_to_tokenize, DELIMITER, 1, LEVEL-1) + 1), INSTR (string_to_tokenize , DELIMITER, 1, level)-DECODE (level, 1, 1, INSTR (String_to_tokenize, DELI MITER, 1, LEVEL-1) + 1)) as INRSUPCD, V_rowid, Inrfilenm, Inrstat, INRDPCD, INRI Tmcd from (SELECT t.inrsupcd | | ', ' as String_to_tokenize, ', ' as DELIMITER, T.rowid V_rowid, Inrfilenm, I Nrstat, INRDPCD, inritmcd from IntitMrtncon T WHERE inrfilenm = p_filenm and Inrstat = 0 and (INSTR (INRSUBCD, ', ')                                  ) >= 1 or INSTR (INRSUPCD, ', ') >= 1)) CONNECT by INSTR (String_to_tokenize, DELIMITER, 1, Level) > 0) b where A.v_rowid = B.v_rowid GROUP by Inrfilenm, Inrstat, INRDPCD, INRITMCD, INRSUBCD, INRSUPCD have COUNT (*) &G T 1);

SQL optimization--custom functions in SQL