title: Oracle DETERMINISTIC HINT Overhead
body: <p>DETERMINISTIC HIT (as Oracle says) is used to cache the result of a function if it could be deterministic, but what is the overhead of that benefit?</p>
  
  <p>I'll try to explain this better:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION betwnstr (
     string_in   IN   VARCHAR2
   , start_in    IN   INTEGER
   , end_in      IN   INTEGER
  )
     RETURN VARCHAR2 DETERMINISTIC
  IS
  BEGIN
     RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
  END;
  /
  </code></pre>
  
  <p>This simple function extract the characters from BEGIN and END index from a given string.</p>
  
  <p>Now i'll start to use this Function in different tables as SELECT result(other functions, procedure, package etc) and Oracle will start caching all the result from the same input.</p>
  
  <p>Sure this is a wonderful result just adding a simple world on function declaration, <strong>but what is the side effect of an intensive use of this? For example, if this function is called million of times with different input ?</strong></p>
  
  <p>I could have many other functions as DETERMINISTICT for example:</p>
  
  <ul>
  <li>AN DETERMINISTIC function to calculate the difference (in DAYS) from two given date</li>
  </ul>
  
  <p>ecc</p>