(Recreated from an original written for the Dizwell Wiki).
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:
create or replace function wait_row(
i_v in varchar2,
i_sec in number default 5
) return varchar2
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:
select wait_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.
To be investigated further ...