Top 60 Oracle Blogs

Recent comments


Regular Expression Functions are Considered To Be Non-Deterministic from Oracle 12.2

You cannot put non-deterministic functions into virtual columns or function-based index.  Regular expressions have always been sensitive to National Language Support (NLS) settings because what is a character is different in different languages.  However, from Oracle 12.2. regular expressions are considered to be non-deterministic because Oracle has fixed bug 20804063.


A developer came to me with the following query, complaining it was slow.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT …
AND LOWER(REGEXP_REPLACE(ASCIISTR(my_column), '[^a-zA-Z0-9]')) ='2732018'

When deterministic function is not

When you declare a function-based index, the function is deterministic, which means that calling it with same arguments will always return the same result. This is required because the indexed values, which are the result of the function, are stored in the index. But what happens if you declare a function deterministic when it is not?

I wanted to do this test after reading the following documents about Postgres HOT and WARM.

Keyword DETERMINISTIC is anything but…

According, the word “deterministic” means…

de·termin·istic adj. an inevitable consequence of antecedent sufficient causes

According to Wikipedia, the explanation of deterministic algorithm is…