JL Computer Consultancy

Deterministic Functions

(Recreated from an original written for the DizwellWiki).

Jan 2006

The idea of a deterministic function has been around for a long time
in Oracle introduced, I believe, some time in the 8i timescale to assist with minimising the costs related to function-based indexes.

A deterministic function is, in principle, one that is guaranteed to return
exactly the same output for a given set of inputs at all times. Consequently,
if you want to write a pl/sql function to use as the
basis for a function-based index (or
index with virtual columns, as I prefer to call them) then you need to ensure
that the function is a deterministic function.

The performance benefit of deterministic functions is that if you call the
function twice in a row with the same inputs, then (according to the manuals)
Oracle has the option of ‘remembering’ the result from the first
call to avoid actually executing the second call. This is clearly a nice idea;
unfortunately this clever little optimisation has
never been implemented - until 10g Release 2.

I discovered this by accident quite recently. There is much more work to be
done finding out the benefits and limits of deterministic functions, but I
don’t have to be the only one to add to this page.

This is the function I happened to be using when I discovered the
enhancement:

The purpose of this function was to allow me to run a very slow query (testing,
for example, for ORA-01555: Snapshot too old), with simple code like:

selectwait_row(colX, 1) from t1;

In most versions of Oracle, this query would run at the rate of one row
returned per second. In Oracle 10g Release 2, I happened to use a query like
this against a table where the colX always returned
the same value - and in 10gR2, my query returned all its data almost instantly,
not waiting one second per row. Repeating the experiment and taking snapshots
of v$session_event, it became clear that the wait_row function (which should have been causing one
PL/SQL lock timer wait per row) was not being called once per row.

Following a few extra tests, I decided that Oracle really had implemented
some special optimisation for deterministic functions,
probably including a cache of four recent input/output sets. And at first
sight, the limitation seem to be that the cache is cleared at the end of each
database call - so single row processing, for example, probably won’t be
able to take advantage of the optimisations.