title: How to make a deterministic function?
body: <p>I'm trying to create an index based on the result of a function applied a column where I have to extract a number.</p>
  
  <pre><code>Example String: ...someText...&amp;idDocunet=799493...someText...
                                   [799493] &lt;- Note the number
  The function: replace(regexp_substr(parametros, '&amp;idDocunet=\d+'), 'idDocunet=', NULL)
  The index: create index example on MY_TABLE (replace(regexp_substr(parametros, '&amp;idDocunet=\d+'), 'idDocunet=', NULL));
  </code></pre>
  
  <p>But when I run this query:</p>
  
  <pre><code>SELECT *
  FROM my_table
  WHERE replace(regexp_substr(parametros, '&amp;idDocunet=\d+'), 'idDocunet=', NULL) IS NOT NULL.
  </code></pre>
  
  <p>Or this one</p>
  
  <pre><code>SELECT *
  FROM my_table
  WHERE replace(regexp_substr(parametros, '&amp;idDocunet=\d+'), 'idDocunet=', NULL) = 799493
  </code></pre>
  
  <p>Or make a join...</p>
  
  <p>The index is not used and it performs a full table scan every time. I believe the index is deterministic because it will always return the number, or null but I don't know if the expression is just too complex for it to use. I also tried to move the code to a function but it is the same. I believe the key is in this deterministic thing (I'm doing it wrong?) and/or the table having null values on the original column. How can I assure that the index is used? this is the function:</p>
  
  <pre><code>create or replace function extraer_doc_id(viParam VARCHAR2) return varchar2 is
  begin
    RETURN replace(regexp_substr(viParam, '&amp;idDocunet=\d+'), 'idDocunet=', NULL);
  end extraer_doc_id;
  </code></pre>
  
  <p>I also executed this</p>
  
  <pre><code>ANALYZE TABLE my_table COMPUTE STATISTICS;
  </code></pre>
  
  <p>But it doesn't seem to matter.</p>