Oracle-L: RE: Interesting PL/SQL Puzzle

From: Chelur, Jayadas {PBSG} <Jayadas.Chelur_at_pepsi.com>
Date: Mon, 10 Nov 2003 08:59:28 -0800
Message-ID: <F001.005D63A4.20031110085928@fatcity.com>

If the logic depends on multiple IF statements based on which patterns the input strings match, it might be better to store the patterns in a PL/SQL table and find out the index of the matching pattern and then base the decision on the index value and not by repeatedly using LIKE ...

something like this ... i am not sure about the performance implications, but i feel its worth a try ...

DECLARE
TYPE TAB_PATTERN IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER; pattern_tab TAB_PATTERN;

    cVariable1 VARCHAR2(32) := '&v1';
    cVariable2 VARCHAR2(32) := '&v2';
    nPatIndex1 NUMBER;
    nPatIndex2 NUMBER;

FUNCTION GetPatternIndex( cString IN VARCHAR2, pat_tab TAB_PATTERN ) RETURN NUMBER
IS

nPatIndex NUMBER := 0;
BEGIN

        FOR i IN 1..pat_tab.COUNT LOOP
            IF cString LIKE pat_tab(i) THEN
               nPatIndex := i;
               EXIT;
            END IF;
        END LOOP;
        
        RETURN (nPatIndex);
    END;

BEGIN

    pattern_tab(1)  := '%ABC%';
    pattern_tab(2)  := '%XYZ%';
    pattern_tab(3)  := '%123%';
    pattern_tab(4)  := '%789%';

nPatIndex1 := GetPatternIndex( cVariable1, pattern_tab ); nPatIndex2 := GetPatternIndex( cVariable2, pattern_tab );

dbms_output.put_line('Value of nPatIndex1 = '||nPatIndex1); dbms_output.put_line('Value of nPatIndex2 = '||nPatIndex2);

ADD THE DESCISION STRUCTURES HERE ...
IF nPatIndex1 = 1 AND nPatIndex2 = 3 THEN ...

END;
/

-----Original Message-----
Sent: Monday, November 10, 2003 11:10 AM To: Multiple recipients of list ORACLE-L

Below are two dummy procs that are good enough to explain the issue (Jared forgive me for posting this big code).
All the code in proc test_plsql1 is inside an IF clause that will not run.

Testing the proc call is done using this:

declare

a  varchar2(1000);
b  varchar2(1000);
c  varchar2(1000);
d  varchar2(1000);

e date := sysdate;
begin
for i in 1..1000000 loop
test_plsql5(a,b,c,d);
end loop;
dbms_output.put_line( (sysdate - e) * 24 * 60 * 60); end;
/

(runs in 4 sec)

declare

a  varchar2(1000);
b  varchar2(1000);
c  varchar2(1000);
d  varchar2(1000);

e date := sysdate;
begin
for i in 1..1000000 loop
test_plsql1(a,b,c,d);
end loop;
dbms_output.put_line( (sysdate - e) * 24 * 60 * 60); end;
/

(runs in 38 sec)

Here are two procs:

CREATE OR REPLACE PROCEDURE test_plsql5 (var1 in out varchar2, var2 in out varchar2, out1 in out varchar2, out2 in out varchar2) as

pat1 varchar2(1000) := '%tttttttttttttttttt%';
pat2 varchar2(1000) := 'lllllllllllllllllllllll';
pat3 varchar2(1000) := '%dfddddddddddddddddddiii%';
pat4 varchar2(1000) := 'yyyyyyyyyyyyyyyyyyyyyyyyy';

begin
if false then
null;
end if;
end;
/

CREATE OR REPLACE PROCEDURE test_plsql1 (var1 in out varchar2, var2 in out varchar2, out1 in out varchar2, out2 in out varchar2) as

pat1 varchar2(1000) := '%tttttttttttttttttt%';
pat2 varchar2(1000) := 'lllllllllllllllllllllll';
pat3 varchar2(1000) := '%dfddddddddddddddddddiii%';
pat4 varchar2(1000) := 'yyyyyyyyyyyyyyyyyyyyyyyyy';

begin
if false then

--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
--
if var1 like pat3
then
   if var2 like pat1
   then
      out1 := pat2;
   else
      out1 := pat4;
   end if;
end if;
--
end if;
end;


/

---------------------------------------------


-----Original Message-----
Sent: Saturday, November 08, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L


I have a weird problem. It seems that execution speed of pl/sql proc can
slow down dramatically as the size of the proc goes up even if nothing gets
executed.

Let me explain:

I have a proc that looks like:

Proc test_1 (p1 in out varchar2, p2 in out varchar2) as
<some declared variables>
begin
 if condition1 then
  <big block  for string manipulation, two pages of code (substr, instr,
etc)>
 end if;
 if condition2 then
  <another big block for string manipulation,  two pages of code (substr,
instr, etc)>
 end if;
end;


If I change the proc to do nothing by altering it this way:

Proc test_2 (p1 in out varchar2, p2 in out varchar2) as
<some declared variables>
begin
 if false then
  <big block  for string manipulation>
 end if;
 if false then
  <another big block for string manipulation>
 end if;
end;

The execution speed goes up a little bit but is still at least 50 percent
slower than if I change the proc by removing the code in the "if" clause,
look below:

Proc test_3 (p1 in out varchar2, p2 in out varchar2) as
<some declared variables>
begin
 if false then
   null;
 end if;
 if false then
   null;
 end if;
end;


proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20
minutes.
Also test_2 required more CPU resources while running.

Also I tried native compilation, which did not do a lot (only 10 % faster).
When I looked at the C code generated by the native compilation, I was not
very pleased the way native compilation works.


Does anybody have a clue why?

I tried to include the proc in a package and pin it but there was no
difference.


Thanks

Waleed

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Chelur, Jayadas {PBSG}
  INET: Jayadas.Chelur_at_pepsi.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).

Received on Mon Nov 10 2003 - 10:59:28 CST