SQL & PL/SQL

Hi;

I want to test performance tests on functions. I read that, if function returns the same output for the same inputs(in another words is deterministic), making the function deterministic will be faster. When I test I could not find this "fast".

Do you have any ideas?

Kindly Regards...

CREATE OR REPLACE FUNCTION with_det_test(i IN VARCHAR2) RETURN VARCHAR2
  DETERMINISTIC IS
BEGIN
  IF i = 'A' THEN
    RETURN 'Active';
  ELSIF i = 'D' THEN
    RETURN 'DeActive';
  ELSE
    RETURN 'Unknown';
  END IF;
END;


CREATE OR REPLACE FUNCTION without_det_test(i IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  IF i = 'A' THEN
    RETURN 'Active';
  ELSIF i = 'D' THEN
    RETURN 'DeActive';
  ELSE
    RETURN 'Unknown';
  END IF;
END;

DECLARE
  a               VARCHAR2(16);
  start_date      DATE;
  end_date        DATE;
  time_difference NUMBER;
  i               NUMBER := 1;
BEGIN
  start_date := SYSDATE;
  WHILE i < 100000000 LOOP
    a := with_det_test('D');
    i := i + 1;
  END LOOP;
  end_date        := SYSDATE;
  time_difference := end_date - start_date;
  time_difference := time_difference * 86400;
  time_difference := trunc(time_difference, 5);
  dbms_output.put_line('With deterministic ' || time_difference ||
                       ' seconds...');
--With deterministic 71.99999 seconds...
END;


DECLARE
  a               VARCHAR2(16);
  start_date      DATE;
  end_date        DATE;
  time_difference NUMBER;
  i               NUMBER := 1;
BEGIN
  start_date := SYSDATE;
  WHILE i < 100000000 LOOP
    a := without_det_test('A');
    i := i + 1;
  END LOOP;
  end_date        := SYSDATE;
  time_difference := end_date - start_date;
  time_difference := time_difference * 86400;
  time_difference := trunc(time_difference, 5);
  dbms_output.put_line('Without deterministic ' || time_difference ||
                       ' seconds...');
  --Without deterministic 68.99999 seconds...
END;